Why "object variable or with block variable not set" error?
 

Any idea why the buttons will not be created when opening the exce
sheet? The deletes work fine, but I am getting an object variable o
with block variable not set error on the first With block. Also if
attach the same code to a custom button and run the macro that way th
buttons do get created. They only are not created when opening th
excel sheet, which is what I am trying to accomplish. Any thoughts?

Sub Workbook_Open()

Application.CommandBars("Assembly Test").Controls(5).Delete
Application.CommandBars("Assembly Test").Controls(4).Delete
Application.CommandBars("Assembly Test").Controls(3).Delete
Application.CommandBars("Assembly Test").Controls(2).Delete
Application.CommandBars("Assembly Test").Controls(1).Delete

With CommandBars("Assembly Test").Controls.Add
.Caption = "Add Test Condition"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton1_Click"
.BeginGroup = True
.FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
.Caption = "Add Main Row for Sub-Test Conditions"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton2_Click"
.BeginGroup = True
.FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
.Caption = "Add Sub-Test Condition"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton3_Click"
.BeginGroup = True
.FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
.Caption = "Add Page Divider Row"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton4_Click"
.BeginGroup = True
.FaceId = 112
End With

With CommandBars("Assembly Test").Controls.Add
.Caption = "Delete"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton5_Click"
.BeginGroup = True
.FaceId = 67
End With

End Su

--
SSjmg247
-----------------------------------------------------------------------
SSjmg2477's Profile: http://www.excelforum.com/member.php...fo&userid=3153
View this thread: http://www.excelforum.com/showthread.php?threadid=51223

Why "object variable or with block variable not set" error?
 

In workbook event programming, references to CommandBars NEED the
"Application." in front of it.
So change With CommandBars("Assembly Test")... to With
Application.CommandBars("Assembly Test")...
Bob Umlas
Excel MVP

"SSjmg2477" wrote
in message ...

Any idea why the buttons will not be created when opening the excel
sheet? The deletes work fine, but I am getting an object variable or
with block variable not set error on the first With block. Also if I
attach the same code to a custom button and run the macro that way the
buttons do get created. They only are not created when opening the
excel sheet, which is what I am trying to accomplish. Any thoughts?

Sub Workbook_Open()

Application.CommandBars("Assembly Test").Controls(5).Delete
Application.CommandBars("Assembly Test").Controls(4).Delete
Application.CommandBars("Assembly Test").Controls(3).Delete
Application.CommandBars("Assembly Test").Controls(2).Delete
Application.CommandBars("Assembly Test").Controls(1).Delete

With CommandBars("Assembly Test").Controls.Add
Caption = "Add Test Condition"
Style = msoButtonIconAndCaption
OnAction = "Sheet1.CommandButton1_Click"
BeginGroup = True
FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
Caption = "Add Main Row for Sub-Test Conditions"
Style = msoButtonIconAndCaption
OnAction = "Sheet1.CommandButton2_Click"
BeginGroup = True
FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
Caption = "Add Sub-Test Condition"
Style = msoButtonIconAndCaption
OnAction = "Sheet1.CommandButton3_Click"
BeginGroup = True
FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
Caption = "Add Page Divider Row"
Style = msoButtonIconAndCaption
OnAction = "Sheet1.CommandButton4_Click"
BeginGroup = True
FaceId = 112
End With

With CommandBars("Assembly Test").Controls.Add
Caption = "Delete"
Style = msoButtonIconAndCaption
OnAction = "Sheet1.CommandButton5_Click"
BeginGroup = True
FaceId = 67
End With

End Sub


--
SSjmg2477
------------------------------------------------------------------------
SSjmg2477's Profile:
http://www.excelforum.com/member.php...o&userid=31532
View this thread: http://www.excelforum.com/showthread...hreadid=512238

Why "object variable or with block variable not set" error?
 

Beautiful. Thank You

--
SSjmg247
-----------------------------------------------------------------------
SSjmg2477's Profile: http://www.excelforum.com/member.php...fo&userid=3153
View this thread: http://www.excelforum.com/showthread.php?threadid=51223