Excell 2003 - VBA - #&*!% Buttons

[Craig Brandt]

I have a button on a sheet that was initially generated with the following
code:

Public StratAFBut As Button

Sheets("Strat").Select
Set StratAFBut = ActiveSheet.Buttons.Add(2, 2, 100, 28)
StratAFBut.Caption = "Turn AutoFocus On"
StratAFBut.OnAction = "AutoFocusStrat"

When I depress the button, I want to toggle the caption on the button as
follows:

StratAFBut.Caption = "Turn AutoFocus On"
Else
StratAFBut.Caption = "Turn AutoFocus Off"

When I first fire up the worksheet, this works, but when I go off and do
something else on the worksheet, then come back, it fails.

Run-time error '91':
Object Variable or With block variable not set.

What am I missing?

Thanks,
Craig

 

[Dave Peterson]

I think you left out the important stuff from the second macro (the macro that
is invoked when you click the button).

I'd use something like:

Option Explicit
Sub somemacnamehere()

Dim StratAFBut as Button

set stratafbut = activesheet.buttons(Application.caller)

if stratafbut.caption = "Turn AutoFocus On" then
stratafbut.caption = ""Turn AutoFocus Off"
else
stratafbut.caption = "Turn AutoFocus On"
end if

End Sub

(Untested, uncompiled. Watch for typos.)

====
I guess that you were trying to depend on stratafbut being a public variable --
so that it would remember what object was assigned to the variable.

The variable will be reset to nothing if you hit the End button in the VBE. Use
End (not End Sub and not End Function) in your code. Or just close and reopen
the workbook.

 

[Craig Brandt]

Dave:
I'm not going to pretend that I understand it but when I added the line
"set stratafbut = activesheet.buttons(Application.caller)"
just before the reference to the buttons, it seems to work.
Thanks,
Craig

 

[Dave Peterson]

Application.caller is the name of the button that you assigned the macro to.

So
set stratafbut = Activesheet.buttons(application.caller)

just says that you want that stratafbut set to the button you clicked to invoke
this macro.

 

[Craig Brandt]

Dave:
Appreciate the follow-up.

Thank you.
Craig