Running Private Sub

StuartWhi

Board Regular
Joined
Sep 1, 2011
Messages
75
Hi Experts,

After checking the forum I found what appears to be perfect for my needs but not able to get this VBA private sub to run (or it's simply not doing anything that I can see).

I have taken the code from this post (I'm using Excel 2010),

My set-up is as follows,

Open new Excel (Book1),
Paste below data (taken from above post) in "Sheet1"

Excel 2010
ABCDEF
1Not sureCurrent Node IDParent node IDNot sureNot surePersons Name
2A00USCR00O0A02JUSCR00O0A02J00Ústavní soud
3A00USCR00O0A03EUSCR00O0A02J11Justice
4A00USCR00O0A049USCR00O0A02J22Generální sekretár
5A00USCR00O0A054USCR00O0A02J33Soudní správa
6A00USCR00O0A06ZUSCR00O0A03E111. senát
7A00USCR00O0A07UUSCR00O0A03E222. senát
8A00USCR00O0A08PUSCR00O0A03E333. senát
9A00USCR00O0A09KUSCR00O0A03E444. senát
10A00USCR00O0A0AFUSCR00O0A03E55Funkcionár
11A00USCR00O0A0Q7USCR00O0A049120Generální sekretár
12A00USCR00O0A0SXUSCR00O0A049230Analytický odbor

<tbody>
</tbody>
Sheet1
Delete the top row and left column (to remove the inserted column and rows),
Right click "Sheet1" tab, select "View Code",
Paste the below code,
Code:
'Source is current open worksheet,'Source = ThisWorkbook.Sheets '(name of the current list)
Private Sub CreateDiagram(Source As Worksheet)


    Dim oSALayout As SmartArtLayout
    Dim QNode As SmartArtNode
    Dim QNodes As SmartArtNodes
    Dim Line As Integer
    Dim PID As String      'identification of parent node
    
    Set oSALayout = Application.SmartArtLayouts(92) 'reference to organization chart
    Set oShp = ActiveWorkbook.ActiveSheet.Shapes.AddSmartArt(oSALayout)
    
    Set QNodes = oShp.SmartArt.AllNodes
    For i = 1 To 5      'delete all included nodes
        oShp.SmartArt.AllNodes(1).Delete
    Next
    
    'looking for root(s)
    Line = 2
    Do While Source.Cells(Line, 1) <> ""
        If Source.Cells(Line, 2) = Source.Cells(Line, 3) Then
            Set QNode = oShp.SmartArt.AllNodes.Add
            QNode.TextFrame2.TextRange.Text = Source.Cells(Line, 6)
            PID = Source.Cells(Line, 2)
            Source.Rows(Line).Delete
            Call AddChildNodes(QNode, Source, PID)
        Else
            Line = Line + 1
        End If
    Loop


End Sub
Private Sub AddChildNodes(QNode As SmartArtNode, Source As Worksheet, PID As String)
    Dim Line As Integer
    Dim Found As Boolean
    Dim ParNode As SmartArtNode
    Dim CurPid As String 'ID of current parent node
    
    Line = 2
    Found = False    'nothing found yet
    Do While Source.Cells(Line, 1) <> ""
        If Source.Cells(Line, 3) = PID Then
            Set ParNode = QNode
            Set QNode = QNode.AddNode(msoSmartArtNodeBelow)
            QNode.TextFrame2.TextRange.Text = Cells(Line, 6)
            CurPid = Source.Cells(Line, 2)
            If Not Found Then Found = True 'something was find
            Source.Rows(Line).Delete
            Call AddChildNodes(QNode, Source, CurPid)
            Set QNode = ParNode
        ElseIf Found Then    'it's sorted,so nothing else can be found
            Exit Do
        Else
            Line = Line + 1
        End If
    Loop
    
End Sub

Nothing happens in Sheet1?
I have adjusted the data in cell C12 but still nothing happens?

Sorry for what I guess in a simple and most likely silly question but I must have missed something very basic in this case.

Thanks for your help in advance as this simple thing is driving me mad.

Regards,

Stuart.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
338
Office Version
  1. 2013
Platform
  1. Windows
Hi, Stuart.

you must apply another macro to start passing the parameter for Worksheet, i.e.

Code:
Sub StartMacro()
CreateDiagram(ActiveSheet) 'for the active sheet
End Sub
Ciao,
Holger
 

StuartWhi

Board Regular
Joined
Sep 1, 2011
Messages
75
Hi Holger,

Thanks for your help on this unfortunately I'm still stuck... I don’t expect you to fault find some else code but I’m not an expert obviously (basically able to combine/adjust existing running code only).
Do you (or anyone else) have any idea why when running this I end up with the following error,
“Object doesn’t support this property or method”
When stepping through the code “F8” it doesn’t help, when it tries to run “CreateDiagram” I get this error, when looking up excel help it doesn’t help…

Below is the code I have (the full CreateDiagram code is in the earlier post).
Code:
Sub StartMacro()
CreateDiagram (ActiveSheet) 'for the active sheet
End Sub
'Source is current open worksheet,
'Source = ThisWorkbook.Sheets '(name of the current list)
Private Sub CreateDiagram(Source As Worksheet)


    Dim oSALayout As SmartArtLayout
Look forward to any thoughts on this.

Thanks in advance for anyone’s ideas.

Stuart.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Rich (BB code):
Call CreateDiagram(ActiveSheet)
 

StuartWhi

Board Regular
Joined
Sep 1, 2011
Messages
75

ADVERTISEMENT

Hi VoG (and Holger),

Thanks for this I thought it was the same thing but adding "Call" fixed the issue.

The code isn't perfect for my needs but I'm able to work with it now.

As always, I’m amazed with how responsive and helpful this community is, great stuff in indeed.

Thanks both.

Stuart.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Code:
Sub x()
    Call y(ActiveSheet) ' works
    y ActiveSheet       ' works
    y (ActiveSheet)     ' fails
End Sub

Sub y(wks As Worksheet)
    wks.Range("A1").Value = "test"
End Sub

The third call fails because the parens around the argument attempt to pass the worksheet object by value.
 

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
338
Office Version
  1. 2013
Platform
  1. Windows
Hi, shg,

thanks for your explanation - should have tested it before posting.

Ciao,
Holger
 

Watch MrExcel Video

Forum statistics

Threads
1,129,445
Messages
5,636,318
Members
416,912
Latest member
danluk12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top