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:

Sub UpdateForm()
    
    lbNon.Clear
    lbHidden.Clear
    For i = 1 To Sheets.Count
        arrSheets(i) = Sheets(i).Visible
        If arrSheets(i) = xlSheetVisible Then
            frmSelect.lbNon.AddItem Sheets(i).Name
        ElseIf arrSheets(i) = xlSheetHidden Then
            frmSelect.lbHidden.AddItem Sheets(i).Name
        ElseIf showVeryHidden Then
            frmSelect.lbHidden.AddItem Sheets(i).Name
            'Or do something else as an improvement?
        End If
    Next i
End Sub

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)

Hi,
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

Hi,

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

[HR]*[/HR]
Originally posted as a separate thread, - merged.

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

Hi,

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

E.g.,

Dim ws as Worksheet


Set ws = ThisWorkbook.Worksheets("mysheet")
If ws.Visible <> xlSheetVeryHidden then ws.Visible = xlSheetVisible


Example loop:


[code]
For Each ws In ActiveWorkbook.Worksheets()
    If ws.Visible <> xlSheetVeryHidden Then ws.Visible = xlSheetVisible
Next ws

[/code]

Re: Increasing 'to' value as variable in For Next statement

How about some code like this:

Dim J As Integer
    Dim N As Integer
    Dim NOP As Integer
    
    N = 10
    J = 0
    Do Until J > N
        NOP = N 'Used for an extra break point
        J = J + 1
        If J = 8 Then J = 10
    Loop

Hi,

I use two vbCrLf (i.e., vbCrLf & vbCrLf) to produce a double-spaced line in message boxes. Is there a constant to do this. I suppose I could create a string variable and define it myself but it would be nice to use something that is already built in.

Regards, Jim

Re: Formula: Test for overlapping number ranges

I posted a lot about this in the Hav-A-Chat part of this site. Here: http://www.ozgrid.com/forum/showthread.php?t=199410

*******************************
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.

Jim

This is a discussion related to my post in the Formulas forum. Here: http://www.ozgrid.com/forum/showthread.php?t=199398

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).

Background:
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.

Jim

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:

Function LastRow(Optional ByVal InColNum = 1) As Integer
    'This does not find it if it's hidden due to filtering
    Dim fndRng As Range
    With Range(Cells(InColNum, InColNum), Cells(InColNum, InColNum)).EntireColumn
        Set fndRng = .Find(What:="*", _
                After:=Cells(InColNum, InColNum), _
                SearchDirection:=xlPrevious, _
                SearchOrder:=xlByColumns)
    End With
    
    If Not fndRng Is Nothing Then
        LastRow = fndRng.Row
    Else
        LastRow = 0
    End If
End Function

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)

=OR(m=x,m=y,n=x,n=y,AND(n>x,n<y),AND(y>m,y<n))

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.

Option Explicit
'Note: From the Tools -> References menu the following references need to be selected
' Microsoft XML, v6.0     (other versions might also work)
' Microsoft Scripting Runtime
Private Function CreateDOM() As DOMDocument60
    'This function is handy for creating the DOM objects.
    'It enables stylesheets to include (reference) other stylesheets.
    'It enables scripts to be used in the stylesheets.
    Dim dom As DOMDocument60
    Set dom = New DOMDocument60
    dom.async = False
    dom.validateOnParse = False
    dom.resolveExternals = True
    dom.setProperty "AllowXsltScript", True
    Set CreateDOM = dom
End Function
Private Sub TransformXML()
    'Call this routine to transform and open the XML file.
    Dim xmlDOCin As DOMDocument60, xslDoc As DOMDocument60, xmlDOCout As DOMDocument60
    Dim FSO As FileSystemObject, f1
    Dim InputXML As String 'Path to XML file to transform
    Dim OutputXML As String 'Path to XML file to output after transforming
    Dim txtBox As Shape 'Worksheet text box
    Dim strXSL As String 'XSL stylesheet instead of loading from a text file
    Dim strXML As String 'XML to write to a temp file to open with the Workbooks.OpenXML statement
    Dim myPath As String 'Path to this workbook
    myPath = ThisWorkbook.Path
    
    InputXML = myPath & "\OM_status_Alladdin.xml"
    OutputXML = myPath & "\Temp01.xml"
    
    Set txtBox = ActiveSheet.Shapes("Text Box 1")
    strXSL = txtBox.TextFrame.Characters.Text
    
    Set FSO = New FileSystemObject
    Set xmlDOCin = CreateDOM
    xmlDOCin.Load InputXML 'Load in the XML file
    Set xslDoc = CreateDOM
    xslDoc.LoadXML strXSL 'Load the stylesheet (string instead of a file)
    
    'Transform the xml and output as string instead of a DOM
    'because my stylesheet outputs HTML.
    'Note also that any stylesheets specified with processing instructions (PIs)
    'are ignored.
    
    strXML = xmlDOCin.transformNode(xslDoc)
    'If you use the transformNodeToObject method, you get an error if
    'the output is not (well-formed ?) xml.
    
    'Write the transformed xml to a temporary file.
    Set f1 = FSO.CreateTextFile(Filename:=OutputXML, overwrite:=True)
    f1.WriteLine (strXML)
    f1.Close
    
    'Open the temp file (xml)
    Workbooks.OpenXML Filename:=OutputXML
    
End Sub

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 (http://msdn.microsoft.com/en-u…op/ms765520(v=vs.85).aspx) and (http://msdn.microsoft.com/en-u…op/ms766487(v=vs.85).aspx)

Then I came up with the following:

Option Explicit
'Note: From the Tools -> References menu the following references need to be selected
' Microsoft XML, v6.0     (other versions might also work)
' Microsoft Scripting Runtime


Private Function CreateDOM()
    'This function is handy for creating the DOM objects
    'It enables stylesheets to include (reference) other stylesheet
    'It enables scripts to be used in the stylesheets
    Dim dom As DOMDocument60
    Set dom = New DOMDocument60
    dom.async = False
    dom.validateOnParse = False
    dom.resolveExternals = True
    dom.setProperty "AllowXsltScript", True
    Set CreateDOM = dom
End Function


Private Sub TransformXML()
    Dim doc As DOMDocument60, xsl As DOMDocument60, out As DOMDocument60, str
    Dim FSO As FileSystemObject, f1
    Dim InputXML As String 'Path to XML file to transform
    Dim OutputXML As String 'Path to XML file to output after transforming
    Dim StyleSheet As String 'Path to XSL stylesheet
    
    InputXML = "J:\Excel\XSL_hacks\FGI - translation status report.xml"
    OutputXML = "J:\Excel\XSL_hacks\FGI-XSL_test_output4.xml"
    StyleSheet = "J:\Excel\XSL_hacks\FGI - translation status report.xsl"
    
    Set FSO = New FileSystemObject
    Set doc = CreateDOM
    doc.Load InputXML
    Set xsl = CreateDOM
    xsl.Load StyleSheet
    str = doc.transformNode(xsl)
    'MsgBox "doc.transformNode: " & vbCrLf & str
    Set f1 = FSO.CreateTextFile(Filename:=OutputXML, overwrite:=True)
    f1.WriteLine (str)
    f1.Close
    Workbooks.OpenXML Filename:=OutputXML
    
    'The following does not work for stylesheets that output HTML.
    'Set out = CreateDOM
    'doc.transformNodeToObject xsl, out
    'MsgBox "doc.transformNodeToObject:" & vbNewLine & out.XML
    'out.Save "J:\Excel\XSL_hacks\test_output.xml"
End Sub

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

Hi again; and sorry for being so stupid. The control works and the navigate method works, if you put the code in a regular module and not in with the userform code module.
(DUH!) LOL

Re: WebBrowser control only displays a blank screen

P.S. I am using Excel 2010 on Windows XP, in case that makes any difference.

Re: WebBrowser control only displays a blank screen

Bummer.

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()
    UserForm1.Show
    '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]http://www.google.com[/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 userform.show method. But all I get is a blank web browser on the form.

Here is what (sort of) I have tried:

Sub ShowTheForm()
    'Dim myPerm As Office.Permission
    'Set myPerm = ThisWorkbook.Permission
    'myPerm.EnableTrustedBrowser = True
    'Of course this permission stuff was fruitless


    UserForm1.Show
    WebBrowser1.Navigate "J:\temp\WebBrowser1.html"
    'WebBrowser1.Navigate "[URL]file://j:\temp\WebBrowser1_image.jpg[/URL]"
    
End Sub

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:

Sub ShowValueOfNamedConstant()
    'Example how to use named formulas as named constants that can be changed without editing any VBA code
    Dim ValueOfNamedConstant As String
    Dim xNumber As Double
    
    ValueOfNamedConstant = Evaluate(ThisWorkbook.Names("myNamedConstant").RefersToR1C1)
    'But do your own data type checking and conversion if necessary
    xNumber = ValueOfNamedConstant + 1.5 'must be a number to do this
    MsgBox ValueOfNamedConstant
    MsgBox xNumber
    MsgBox ValueOfNamedConstant & xNumber
End Sub

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