Posts by jdawson422

    Re: Unhide multiple sheets that are NOT very hidden (at once)

    Hi again,

    Sorry, I forgot to fix one of the routines in the userform code module. The UpdateForm() routine should be:

    As for the message about the active workbook, that was so that if I had it loaded as a non-addin (.xlsx instead of .xlam) I would be reminded that the wrong workbook was active. It is fine to remove that part.

    Regards, Jim

    Re: Unhide multiple sheets that are NOT very hidden (at once)

    Sorry for the misinterpretation of your post.

    To make it work, I changed the Dim statement for arrSheets() from Boolean to Integer and several If statements to use xlSheetHidden, xlSheetVisible, etc. and added a few extra If statements to make sure the very hidden sheets are not included.

    Also, because I and everyone in our company uses Excel 2010, I changed the Auto_Open routine to add the custom toolbar in a different way.

    I created a new post in order to attach my new addin. However, you must resave it as an addin.

    Regards, Jim

    Addin for Multi Hide sheets


    This is for pvman's request for help in modifying an addin.

    The addin displays a userform to allow a user to hide and unhide several sheets at once and avoid having the very hidden sheets listed on the form.

    I will add a reply to his original post as soon as I finish here.

    I can't add an attachment to my reply, so I posted this. Please see his original post for credit for the original addin.

    Also, my apologies to the forum if this is not in accordance with the forum's protocol. I usually only read or make quick replies.

    Kind regards, Jim :)

    Originally posted as a separate thread, - merged.

    Re: Unhide multiple sheets that are NOT very hidden (at once)


    Test the worksheet visible property and if it is equal to "xlSheeVeryHidden" then skip that sheet.



    Re: Formula: Test for overlapping number ranges

    I posted a lot about this in the Hav-A-Chat part of this site. Here:

    Sorry for the previous ambiguity.

    Here is the final result using Excel's worksheet formulas with

    Cells A1, B1 are the first begin and end range
    Cells A2, B2 are the second begin and end range:

    True If Overlap =OR(A1=A2,B1=B2,B1=A2,A1=B2,AND(B1>A2,B1<B2),AND(B2>A1,B2<B1))

    It displays True if the first range overlaps the second and False if not.
    You can use filtering, additional helper columns, etc. to further the data manipulation.


    This is a discussion related to my post in the Formulas forum. Here:

    I will post a reply there with some additional info. and a link to this post.

    (Note: The following uses code tags formatting (i.e., preserve spaces).

    For a large exported data set, I needed to determine if the current row's number
    range overlapped with the next row's number range. For example, if you have some
    manufactured items with similar model and styles grouped together with different
    lot numbers, it can be of interest to know if the lot numbers overlap which would
    indicate a problem with the data.

    Originally, I just wanted a formula to use on a spreadsheet that would indicate
    if two number ranges overlapped. When I searched, all that I found was:
    * Statements similar to "Cant't do it with a boolean expression. You need need a computer program."
    * VBA routines.
    * Other prgramming language pseudo routines
    * Pseudo formulas similar to:
    - IF(A>C AND A<D Then True) ...
    - =if(a=c,true,if(a=d,true,(b=c,true,if(...

    My final answer is based on what follows hereafter.

    With two columns representing a range of numbers:
    Column A = the begin of a number range
    Column B = the end of a number range

    I used the following table/Visual of conditions defining if two ranges overlap to help figure out a
    worksheet formula.

    For two number ranges A1 thru B1 and A2 thru B2:

    Visual Overlap      Conditions for True                 How they overlap------------        ---------------------------         ---------------------------A1....B1            (B1 > A2) AND (B1 < A2)             A1-B1 overlaps A2-B2 at one end.......A2....B2.......A1....B1     (B2 > A1) AND (B2 < B1)             A1-B1 overlaps A2-B2 at other end                 Which gives a partial Boolean                  condition for True:                 ((B1 > A2) AND (B1 < A2))                 OR                 ((B2 > A1) AND (B2 < B1))                 Note that the overlap shown is visually more than exactly                  at the number range boundaries.                 It seemed a bit complicated to try using >= or =< due to                  the AND operators.                 Also, my boolean algebra has long ago become rusty.         The Excel worksheet formula: =OR(AND(B1>A2,B1<B2),AND(B2>A1,B2<B1))When the ranges overlap only at one or more boundaries or are an exact match, I used the following:Visual Overlap   Conditions for True                 How they overlap--------------   ---------------------------         ------------------------------------A1....B1         (A1 = A2) OR (B1 = B2)              Upper or lower boundaries matchA2....B2                                                    (or an exact match)A1....B1         (B1 = A2)                           One upper and lower boundary matches......A2....B2......A1....B1   (A1 = B2)                           Other upper and lower boudary matchesA2....B2                 Which gives another partial Boolean                  condition for True:                 (A1 = A2) OR (B1 = B2) OR (B1 = A2) OR (A1 = B2)                 Excel worksheet formula: =OR(A1=A2,B1=B2,B1=A2,A1=B2)Combining the two partials and using Excel's worksheet formulas with Cells A1, B1 are the first begin and end rangeCells A2, B2 are the second begin and end range:Worksheet formula       =OR(A1=A2,B1=B2,B1=A2,A1=B2,AND(B1>A2,B1<B2),AND(B2>A1,B2<B1))

    It displays True if the first range overlaps the second and False if not.
    You can use filtering, additional helper columns, etc. to further the data manipulation.

    I suppose a better, more structured database and data translation to my own database
    would be more exacting. However, I just wanted a worksheet since it would be much
    more of a project than is practical for me at this time.


    Re: Find first blank row after all other data in sheet

    My comment is about "UsedRange". It includes cells/rows/columns with formatting but otherwise would be blank. I have been relying on the following for a while now:

    It assumes the active sheet and column 1 (A) as the default. But, like the comment in the code says, it will not find it if it's hidden due to filtering.

    Regards, Jim

    I searched the internet and here for the answer and found very little that was convincing. Often, an entire algorithm in VBA or other language was given.

    Here is my answer; remember, formula; not a VBA subroutine.

    For two number ranges m-n and x-y (i.e., m through n and x through y)


    Where m, n, x and y could be cell addresses or whatever gives the desired values defining the number ranges.

    Regards, Jim

    This is a variation to a previous post (see where I misspelled transform as tramsform, lol).

    This shows how to read the stylesheet from a textbox on a worksheet. I did this so I could distribute the application as one file without the users having to do any zipping, unzipping, etc.

    I'm not sure how big of a stylesheet can be embedded this way but mine was a bit over 25 kB.

    Cheers, Jim

    I have seen a lot about this and I though I would share the fruits of my effort.

    I used to process XML with a XSLT style sheet by using the following command:
    Workbooks.OpenXML Filename:="Path_to_XML_file", Stylesheets:=Array(1)

    This used to work until I had Excel upgraded from Excel 2003 to Excel 2010. However, Excel 2010 by default does not allow the stylesheet to contain scripting or references to additional stylesheets. After much searching on the web I found some documentation by Microsoft (…op/ms765520(v=vs.85).aspx) and (…op/ms766487(v=vs.85).aspx)

    Then I came up with the following:

    sorry, I misspelled the word "transform" in the title but the "Edit Post" did not let me change it.

    Re: WebBrowser control only displays a blank screen


    I tried again and still all I get is a blank browser control on the form. I moved the html file to my local c:\ drive instead of the mapped drive to our network server file space and there are no spaces to be concerned with, tried both forward and back slashes. I tried one of the infamous search sites, too. I tried (again) placing the .show command before and after the .navigate command.

    Here is my code again with all of the paths I tried (all but one commented):

    Sub ShowTheForm()
        'WebBrowser1.Navigate "[URL]file:///j:/temp/WebBrowser1_image.jpg[/URL]"
        'WebBrowser1.Navigate "J:\temp\WebBrowser1.html"
        'WebBrowser1.Navigate "[URL]file:///c:/temp/WebBrowser1.html[/URL]"
        'WebBrowser1.Navigate "[URL]file:///c:\temp\WebBrowser1.html[/URL]"
        'WebBrowser1.Navigate "c:\temp\WebBrowser1.html"
        WebBrowser1.Navigate "[URL][/URL]"
    End Sub

    I'm wondering if there is some setting that I need to change or if our company's IT department has done something to prevent this from working; we can't even use the MSI installer utility.

    I have searched and found a lot of references to the web browser control for a user form. I have tried and tried but all I get is a blank webbrowser control.

    I have tried the navigate and navigate2 methods and other stuff that you can see that I have commented out. Also, I tried placing the line with the navigate method both before and after the method. But all I get is a blank web browser on the form.

    Here is what (sort of) I have tried:

    So, what's the trick. All the stuff I found searching the web implies that what I have above should work.

    Regards Jim

    Re: Refer To Named Constant In VBA

    Try hacking around with this:

    Example values for the RefersTo in the name manager:
    ="String value"