Object Variable with Block variable not set message
 

I am trying to run this code on workbook open and get the above error message

mode = EssVGetGlobalOption(5)

For Each ctl In CommandBars("essoption").Controls
If mode = 4 And (ctl.Caption Like "Warnings*") Then
ctl.Caption = "Warnings Off"
ElseIf mode = 2 And (ctl.Caption Like "Warnings*") Then
ctl.Caption = "Warnings On"
End If
Next ctl

the error is on the line For Each ctl In CommandBars("essoption").Controls

thanks

Object Variable with Block variable not set message
 

VBA doesn't think that the Commandbar "essoption" exists.

In article ,
Nigel wrote:

I am trying to run this code on workbook open and get the above error message

mode = EssVGetGlobalOption(5)

For Each ctl In CommandBars("essoption").Controls
If mode = 4 And (ctl.Caption Like "Warnings*") Then
ctl.Caption = "Warnings Off"
ElseIf mode = 2 And (ctl.Caption Like "Warnings*") Then
ctl.Caption = "Warnings On"
End If
Next ctl

the error is on the line For Each ctl In CommandBars("essoption").Controls

thanks

Object Variable with Block variable not set message
 

As with your problem yesterday, we have to know what
mode = EssVGetGlobalOption(5)
is, the For Next code is fine, i built the custom toolbar and everything, it
is good.
If i set mode to 4 i get warnings off, if i set it to 2 i get warnings on,
so it has to be the EssVGetGlobalOption(5) that is causing the issue.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Nigel" wrote:

I am trying to run this code on workbook open and get the above error message

mode = EssVGetGlobalOption(5)

For Each ctl In CommandBars("essoption").Controls
If mode = 4 And (ctl.Caption Like "Warnings*") Then
ctl.Caption = "Warnings Off"
ElseIf mode = 2 And (ctl.Caption Like "Warnings*") Then
ctl.Caption = "Warnings On"
End If
Next ctl

the error is on the line For Each ctl In CommandBars("essoption").Controls

thanks

Object Variable with Block variable not set message
 

the code works fine when added to an addin and everything works as it should.

What I wanted to do is as a spreadsheet options check the settings and then
adjust the button caption on the toolbar, the ESSVGETGLOBALOption is setting
the mode correctly (to 4) if thats is the value

I actually just put the code into a module and it works fine there so it
must be in the sub routine it doesn't like it


"John Bundy" wrote:

As with your problem yesterday, we have to know what
mode = EssVGetGlobalOption(5)
is, the For Next code is fine, i built the custom toolbar and everything, it
is good.
If i set mode to 4 i get warnings off, if i set it to 2 i get warnings on,
so it has to be the EssVGetGlobalOption(5) that is causing the issue.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Nigel" wrote:

I am trying to run this code on workbook open and get the above error message

mode = EssVGetGlobalOption(5)

For Each ctl In CommandBars("essoption").Controls
If mode = 4 And (ctl.Caption Like "Warnings*") Then
ctl.Caption = "Warnings Off"
ElseIf mode = 2 And (ctl.Caption Like "Warnings*") Then
ctl.Caption = "Warnings On"
End If
Next ctl

the error is on the line For Each ctl In CommandBars("essoption").Controls

thanks