Excel Open Event VBA Code does not execute

[dkathrens77]

Greetings All,

I want to code a custom Commandbar for a simple Excel workbook application. A few buttons to run macros. This is not a persistent object, it should be created when the workbook opens and disappear when it closes.

I've got the Commandbar and the buttons and the macros set up, and I want the code to run when the workbook opens.

It apparently does not run. My commandbar is not shown, and does not appear in the View>Toolbars list.

If I go into the Visual Basic Editor (Alt-F11) and step through the code, it executes fine with no errors. When I go back to my worksheet, the commandbar is there as desired and the buttons work as expected.

I wonder if I have the Workbook_Open() event in the correct location. Looking at the Project Explorer, should this be in a module, in the ThisWorkbook object, or somewhere else?

If the answer to my issue isn't instantly obivous from this posting, I can post the code here for your inspection.

Thanks,

Dennis

 

[Loomah]

Dennis
Your code should be in the 'ThisWorkbook' module within the workbook object. Double click 'ThisWorkbook' then choose workbook from the left hand dropdown in the code window of the VBE.

As an alternative, create an Autpen sub in an ordinary code module and see if your code runs from that
eg
Sub auto_open()
'Your Code Here!!!
End Sub

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[dkathrens77]

Thanks Loomah,

I tried moving my code to ThisWorkbook. It runs there, but halts with Runtime Error 91 "Object variable or With Block variable not set.

Here is the code :
[blue]
Option Explicit

Public wksMe As Workbook
Public tlbWalkdownToolBar As CommandBar

Public butReOrderSheet As CommandBarButton
Public butAlternateLinesShaded As CommandBarButton

Public butMoveToBePulled As CommandBarButton
Public butMoveVerifiedPulled As CommandBarButton

Sub Workbook_Open()

Application.WindowState = xlMaximized

'Stop

Set wksMe = ThisWorkbook

'This toolbar is not persistent
[/blue]
[red]

'**********************
' following line generates run-time error 91
'***********************

Set tlbWalkdownToolBar = CommandBars.Add("WalkDownToolBar", _
msoBarLeft, _
False, _
' True)
'***********************
[/red]
[blue]
tlbWalkdownToolBar.Visible = True
tlbWalkdownToolBar.Enabled = True

Set butReOrderSheet = tlbWalkdownToolBar.Controls.Add(Type:=msoControlButton)

With butReOrderSheet 'set its properties etc
.FaceId = 31 'A down arrow Z
.TooltipText = "Sort Sheet by Bldg/Elev/MDT#"
.Caption = "Re-Order Sheet"
.OnAction = "Sort_By_Bldg_Level_DefTagNumber" 'call a macro
.Visible = True
End With


Set butAlternateLinesShaded = tlbWalkdownToolBar.Controls.Add(Type:=msoControlButton)

With butAlternateLinesShaded 'set its properties etc
.FaceId = 123 'Full Justified
.TooltipText = "Shade Alternate Lines Gray"
.Caption = "Shade Alternate Lines"
.OnAction = "Alternate_Lines_Shaded" 'call a macro
.Visible = True
End With


Set butMoveToBePulled = tlbWalkdownToolBar.Controls.Add(Type:=msoControlButton)

With butMoveToBePulled
.FaceId = 133 'Green Right Arrow
.TooltipText = "Move To Be Pulled"
.Caption = "Move To Be Pulled"
.OnAction = "Move_To_Be_Pulled" 'call a macro
.Visible = True
End With


Set butMoveVerifiedPulled = tlbWalkdownToolBar.Controls.Add(Type:=msoControlButton)

With butMoveVerifiedPulled
.FaceId = 136 'Double Green Right Arrow
.TooltipText = "Move To Verified Pulled"
.Caption = "Verified Pulled"
.OnAction = "Move_Verified_Pulled" 'call a macro
.Visible = True
End With

End Sub

Sub Workbook_Close()

On Error GoTo ErrorHandler:

Stop

CommandBars("WalkDownToolBar").Delete
'CommandBars("MDT WalkDown").Delete
Set tlbWalkdownToolBar = Nothing
Set butReOrderSheet = Nothing
Set butAlternateLinesShaded = Nothing
Set butMoveToBePulled = Nothing
Set butMoveVerifiedPulled = Nothing

ErrorHandler:

MsgBox Err.Description, vbOKOnly, Err.Number

End Sub
[/blue]

 

[fumei]

Is the following absolutely the way it is?

Set tlbWalkdownToolBar = CommandBars.Add("WalkDownToolBar", _
          msoBarLeft, _
          False, _
'         True)

because if it is, and that last line is commented, there is no closing " ) ". The instruction ends with False, _ which indicates that there is more code.

Gerry

 

[combo]

Try change beginning of red line to:
Set tlbWalkdownToolBar = Application.CommandBars.Add....

and remove an apostroph from the beginning of last part of the line above.

As ThisWorkbook is class-like document module, it is not recommended to declare variables here, they should be rather transferred to standard module.

combo