Getting Macros to Run Together (with same range selection as input)

I am currently trying to get three subroutines to run harmoniously with each other and for some reason or another (my VBA noobness most likely) I can't seem to figure out the call statements correctly, nor am I sure of the best way to pass the selected range and have them run off this range. I've arrived at this point of trying to integrate the pieces of this project together (they successfully and do their job alone) via a lot of help from OnErrorGoto0 within this thread: http://www.excelforum.com/excel-prog...59#post2700959

The following is the code I'm trying to get to run together, I have them all within a single module and attempt to call the second from the first. The second purely just sends the range to the third. When developing them I would test the second and the third together and the first alone, and both ran successfully. Although the second and the third requiring a few files to be stored locally on your machine for the tag cloud to actually be generated within the web browser.

Sub MakeTable3()

    Dim CloudData As Range
    Dim Pt As PivotTable
    Dim strField As String
    Dim oDic As Object
    Dim varData
    Dim varItems
    Dim varKeys
    Dim n As Long
    Dim wksTable As Worksheet
    Dim lngTop5Count As Long

    Const cstrSHEET_NAME As String = "Incident Summary"
    On Error Resume Next

    'Asks user to specify which column of data they wish to summarize
    Set CloudData = Application.InputBox("Please select a range with the incident information you wish to summarize.", _
                                         "Specify Incident Information", Selection.Address, , , , , 8)
    On Error GoTo err_handle
    Application.ScreenUpdating = False

    If Not CloudData Is Nothing Then
        Set oDic = CreateObject("Scripting.Dictionary")
        strField = Cells(1, CloudData.Column).Value
        With CloudData
            If .Row = 1 Then
                varData = .Resize(.Rows.Count - 1).Offset(1).Value
            Else
                varData = .Value
            End If
        End With
        For n = 1 To UBound(varData, 1)
            If Len(varData(n, 1)) > 0 Then
                oDic(CStr(varData(n, 1))) = Val(oDic(CStr(varData(n, 1)))) + 1
            End If
        Next n

        If oDic.Count > 0 Then

            On Error Resume Next
            Application.DisplayAlerts = False
            Sheets(cstrSHEET_NAME).Delete
            Application.DisplayAlerts = True
            On Error GoTo err_handle

            Set wksTable = Sheets.Add
            With wksTable
                .Name = cstrSHEET_NAME
                .Range("A1:B1").Value = Array(strField, "Total")
                varItems = oDic.Items
                varKeys = oDic.Keys
                If oDic.Count > 5 Then
                   lngTop5Count = Application.Large(varItems, 5)
                Else
                   lngTop5Count = 0
                End If
                For n = LBound(varItems) To UBound(varItems)
                    If varItems(n) >= lngTop5Count Then
                        With .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                            .Value = varKeys(n)
                            .Offset(, 1).Value = varItems(n)
                        End With
                    End If
                Next n
                'Sorts frequency table descending.
                With .Range("A1").CurrentRegion
                    .Sort .Cells(1, 2), xlDescending
                End With
            End With

        End If
    End If
    
    ActiveSheet.OLEObjects.Add(ClassType:="Shell.Explorer.2", Link:=False, _
        DisplayAsIcon:=False, Left:=383.25, Top:=45, Width:=324.75, Height:= _
        225).Select
    ActiveSheet.Shapes("WebBrowser1").ScaleWidth 1.480369515, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("WebBrowser1").ScaleHeight 1.3966666667, msoFalse, _
        msoScaleFromTopLeft
leave:
    Application.ScreenUpdating = True
    Exit Sub
err_handle:
    MsgBox Err.Description
    Resume leave
    
Call test

End Sub

Public Sub test()
'this subroutine produces a tag cloud and places it within the Web Browser contained
'on "Incident Summary" (cstrSHEET_NAME) worksheet. It does this by calling WordCloud
'subroutine which creates the tag cloud using a jscript file stored locally.

 WordCloud Selection
 
End Sub


Sub WordCloud(rngInput As Range)
Dim wbString As String
Dim myFile As String
Dim rngVar As Variant
Dim fnum As Integer
Dim i As Integer

rngVar = Application.Transpose(rngInput.Value)


wbString = "<html>" & vbCr
wbString = wbString & "  <head>"

'wbString = wbString & "    <link rel=""stylesheet"" type=""text/css"" href=""http://visapi-gadgets.googlecode.com/svn/trunk/wordcloud/wc.css""></script>" & vbCr
'wbString = wbString & "    <script type=""text/javascript"" src=""http://visapi-gadgets.googlecode.com/svn/trunk/wordcloud/wc.js""></script>" & vbCr
'wbString = wbString & "    <script type=""text/javascript"" src=""http://www.google.com/jsapi""></script>" & vbCr

wbString = wbString & "    <link rel=""stylesheet"" type=""text/css"" href=""wc.css""></script>" & vbCr
wbString = wbString & "    <script type=""text/javascript"" src=""wcbackup3.js""></script>" & vbCr
wbString = wbString & "    <script type=""text/javascript"" src=""jsapi""></script>" & vbCr

wbString = wbString & "  </head>" & vbCr
wbString = wbString & "  <body>" & vbCr
wbString = wbString & "    <div id=""wcdiv""></div>" & vbCr
wbString = wbString & "    <script type=""text/javascript"">" & vbCr
wbString = wbString & "      google.load('visualization', '1');" & vbCr
wbString = wbString & "      google.setOnLoadCallback(draw);" & vbCr
wbString = wbString & "      function draw() {" & vbCr
wbString = wbString & "        var data = new google.visualization.DataTable();" & vbCr
wbString = wbString & "        data.addColumn('string', 'Text1');" & vbCr
wbString = wbString & "        data.addRows(" & UBound(rngVar) & ");" & vbCr

For i = 1 To UBound(rngVar)
    wbString = wbString & "        data.setCell(" & i - 1 & ", 0,'" & rngVar(i) & "');" & vbCr
Next i

wbString = wbString & "        var outputDiv = document.getElementById('wcdiv');" & vbCr
wbString = wbString & "        var wc = new WordCloud(outputDiv);" & vbCr
wbString = wbString & "        wc.draw(data, null);" & vbCr
wbString = wbString & "      }" & vbCr
wbString = wbString & "    </script>" & vbCr
wbString = wbString & "  </body>" & vbCr
wbString = wbString & "</html>"


myFile = ThisWorkbook.Path & "\WordCloud.htm"
fnum = FreeFile()
Open myFile For Output As fnum
Print #fnum, wbString
Close #fnum


With Sheets("Incident Summary").WebBrowser1
    .Silent = True
    .Navigate (myFile)
    Do
        DoEvents
    Loop Until .ReadyState = READYSTATE_COMPLETE
    .Document.body.Scroll = "no"
End With

MsgBox "Macro Finished."

End Sub

I can tell the last subroutine is not being run as I never see the "Macro Finished." So it's more than it just not being passed the range, I believe its not being ran at all. One thing I would like to change is for the first subroutine MakeTable3 to run purely off of whatever range is selected (similar to how the other two subroutines have been written to just run off the selected range)...I'd like to the user to be able to highlight a range and just press play and have the summary sheet appear. Thanks in advance to anyone who has any advice for how I can accomplish this! This forum rocks!

Have you tried stepping through each line to see where there is a miss?

It should be

Public Sub test(rng As Range)
'this subroutine produces a tag cloud and places it within the Web Browser contained
'on "Incident Summary" (cstrSHEET_NAME) worksheet. It does this by calling WordCloud
'subroutine which creates the tag cloud using a jscript file stored locally.

 WordCloud rng
 
End Sub

with

Call test(clouddata)

before the Exit Sub.

Set wordcloud = Selection

Can't answer the rest as I'm not sure what the question is.

I was actually addressing the second part, but got the variable name wrong. Replace

Set CloudData = Application.InputBox("Please select a range with the incident information you wish to summarize.", _
                                         "Specify Incident Information", Selection.Address, , , , , 8)

with just

Set CloudData = Selection

I would also replace

ActiveSheet.OLEObjects.Add(ClassType:="Shell.Explorer.2", Link:=False, _
        DisplayAsIcon:=False, Left:=383.25, Top:=45, Width:=324.75, Height:= _
        225).Select
    ActiveSheet.Shapes("WebBrowser1").ScaleWidth 1.480369515, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("WebBrowser1").ScaleHeight 1.3966666667, msoFalse, _
        msoScaleFromTopLeft

with

With ActiveSheet.OLEObjects.Add(ClassType:="Shell.Explorer.2", Link:=False, _
        DisplayAsIcon:=False, Left:=383.25, Top:=45, Width:=324.75, Height:= _
        225).ShapeRange
    .ScaleWidth 1.480369515, msoFalse, msoScaleFromTopLeft
    .ScaleHeight 1.3966666667, msoFalse, msoScaleFromTopLeft
End With

At the time you try to run the code, that control does not exist, so the compiler won't like it. You will need something like

With Sheets("Incident Summary").OLEObjects("WebBrowser1").Object

I'll try adding the construction earlier but the browser isn't used until the third subroutine and I have it being constructed at the end of the first. I'll report back in a little with good results hopefully. Fingers crossed.

Edit: Ah hah I replaced 'With Sheets("Incident Summary").WebBrowser1' with the line you provided me and I did not get that message. My tag cloud was blank however but in my experience fiddling with that subroutine that was usually because it was passed nothing as the range...going to start fiddling with that now but have other work with a nearer deadline so may have to wait til the AM.

Edit II: OK so I have had some time to return to this. What is currently happening is that when I run the code before my tag cloud appears I must turn design mode on then off.

For some reason after I do this my tag cloud will appear...very odd. When I try to record myself clicking the design mode button twice I get nothing recorded. Also worth mentioning is that the vertical scroll bar typically on the right hand side of a WebBrowser control appears with the tag cloud...i.e. is not visible then upon entering/exiting design mode it appears as well.

The "Macro Finished." message is also not appearing leading me to believe its still doing something with the construction of the WebBrowser...not really sure on this one any help would be awesome!

What happens if you remove the Do...Loop from the webbrowser code?

Comment out that line too. Do you get the message box?

Which line is "that" line your speaking of? I get the feeling its not finishing with this chunk of the code since it never tells me "Macro Finished." Thanks again for your help .

Let me know if you'd like me to email you the jscript files that work with this macro...perhaps it'd be easier if you had them so you saw exactly what was happening with respect to the tag cloud and WebBrowser...just let me know.

This line

.Document.body.Scroll = "no"

OK, so that implies that your loop is never finishing for some reason. Try

    Do
        DoEvents
    Loop While .Busy

(I am doing this from memory so that might not be correct syntax)

With that chunk of the macro coded as follows:

With Sheets("Incident Summary").OLEObjects("WebBrowser1").Object
'With Sheets("Incident Summary").WebBrowser1
    .Silent = True
    .Navigate (myFile)
'    Do
'        DoEvents
'    Loop Until .ReadyState = READYSTATE_COMPLETE

Do
        DoEvents
    Loop While .Busy
    
'    .Document.body.Scroll = "no"
End With



MsgBox "Macro Finished."

The loop seems to run through as I get the macro finished message but I unfortunately still must double click on the "Design Mode" button for the tag cloud to appear.

I've also noticed that at some point during this troubleshooting i've done something such that I'm no longer allowed to record macros from this workbook at all (its the whole workbook too i don't get it).

Edit: This morning when I rebooted and opened the workbook again I can record. But when I record myself clicking on the design mode button twice it does not seem to record anything at all :-(. This is getting frustrating soooo close but yet so far it seems.