using variable as object name
 

I need to have numerous toggle buttons on a spread sheet. Rather than
have to replicate most of the macro code many times, I'd like to
substitute a variable for an object name in the lines of code.

'First I define ToggleBut as a string, and then I assign a value:

Dim ToggleBut as string
ToggleBut = "ToggleButton1"

'I then test the value of the object as shown in the next line, but the
test fails because my grammer is incorrect.
If ToggleBut.Value = False Then


'The code works when I use:
If ToggleButton1.Value = False Then

Obviously there is a problem with the way I am using a string variable
as an object name. What am I doing wrong? The actual code is shown below.

- - - - - - - -

Dim SheetNamed, ToggleBut As String

Private Sub ToggleButton2_Click()
SheetNamed = "2-SR"
ToggleBut = "ToggleButton2"
Application.Run macro:="sheet7.Std_Toggle"
End Sub

Sub Std_Toggle()
If ToggleBut.Value = False Then
ToggleBut.Caption = "Hide " & SheetNamed
Sheets(SheetNamed).Visible = True
Else
ToggleBut.Caption = "Unhide " & SheetNamed
Sheets(SheetNamed).Visible = False
End If
End Sub

using variable as object name
 

Dim SheetNamed
Private Sub ToggleButton2_Click()
SheetNamed = "2-SR"
sheet7.Std_Toggle ToggleButton2
End Sub

Sub Std_Toggle(ToggleBut as MSforms.ToggleButton)
If ToggleBut.Value = False Then
ToggleBut.Caption = "Hide " & SheetNamed
Sheets(SheetNamed).Visible = True
Else
ToggleBut.Caption = "Unhide " & SheetNamed
Sheets(SheetNamed).Visible = False
End If
End Sub

--
Regards,
Tom Ogilvy

"windsurferLA" wrote in message
...
I need to have numerous toggle buttons on a spread sheet. Rather than
have to replicate most of the macro code many times, I'd like to
substitute a variable for an object name in the lines of code.

'First I define ToggleBut as a string, and then I assign a value:

Dim ToggleBut as string
ToggleBut = "ToggleButton1"

'I then test the value of the object as shown in the next line, but the
test fails because my grammer is incorrect.
If ToggleBut.Value = False Then


'The code works when I use:
If ToggleButton1.Value = False Then

Obviously there is a problem with the way I am using a string variable
as an object name. What am I doing wrong? The actual code is shown
below.

- - - - - - - -

Dim SheetNamed, ToggleBut As String

Private Sub ToggleButton2_Click()
SheetNamed = "2-SR"
ToggleBut = "ToggleButton2"
Application.Run macro:="sheet7.Std_Toggle"
End Sub

Sub Std_Toggle()
If ToggleBut.Value = False Then
ToggleBut.Caption = "Hide " & SheetNamed
Sheets(SheetNamed).Visible = True
Else
ToggleBut.Caption = "Unhide " & SheetNamed
Sheets(SheetNamed).Visible = False
End If
End Sub

using variable as object name
 

Put the Std-Toggle procedure into a standard code module, and change to

Sub Std_Toggle(ToggleBut As Object, SheetNamed As String)
If ToggleBut.Value = False Then
ToggleBut.Caption = "Hide " & SheetNamed
Sheets(SheetNamed).Visible = True
Else
ToggleBut.Caption = "Unhide " & SheetNamed
Sheets(SheetNamed).Visible = False
End If
End Sub


and then call like so

Private Sub ToggleButton2_Click()
Std_Toggle ToggleButton2, "2-SR"
End Sub
--

HTH

RP
(remove nothere from the email address if mailing direct)


"windsurferLA" wrote in message
...
I need to have numerous toggle buttons on a spread sheet. Rather than
have to replicate most of the macro code many times, I'd like to
substitute a variable for an object name in the lines of code.

'First I define ToggleBut as a string, and then I assign a value:

Dim ToggleBut as string
ToggleBut = "ToggleButton1"

'I then test the value of the object as shown in the next line, but the
test fails because my grammer is incorrect.
If ToggleBut.Value = False Then


'The code works when I use:
If ToggleButton1.Value = False Then

Obviously there is a problem with the way I am using a string variable
as an object name. What am I doing wrong? The actual code is shown
below.

- - - - - - - -

Dim SheetNamed, ToggleBut As String

Private Sub ToggleButton2_Click()
SheetNamed = "2-SR"
ToggleBut = "ToggleButton2"
Application.Run macro:="sheet7.Std_Toggle"
End Sub

Sub Std_Toggle()
If ToggleBut.Value = False Then
ToggleBut.Caption = "Hide " & SheetNamed
Sheets(SheetNamed).Visible = True
Else
ToggleBut.Caption = "Unhide " & SheetNamed
Sheets(SheetNamed).Visible = False
End If
End Sub

using variable as object name
 

windsurferLA wrote:
I need to have numerous toggle buttons on a spread sheet. Rather than
have to replicate most of the macro code many times, I'd like to
substitute a variable for an object name in the lines of code.

'First I define ToggleBut as a string, and then I assign a value:

Dim ToggleBut as string
ToggleBut = "ToggleButton1"

'I then test the value of the object as shown in the next line, but
the test fails because my grammer is incorrect.
If ToggleBut.Value = False Then


'The code works when I use:
If ToggleButton1.Value = False Then


You can use

If Me.OLEObjects(ToggleBut).Object.Value = False Then

or you can use a class module to capture all of the toggle button click
events in one place. See

http://www.dicks-blog.com/archives/2...-events-class/

for more information on the latter.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

using variable as object name
 

Bob Phillips wrote:
Put the Std-Toggle procedure into a standard code module, and change to

Sub Std_Toggle(ToggleBut As Object, SheetNamed As String)
If ToggleBut.Value = False Then
ToggleBut.Caption = "Hide " & SheetNamed
Sheets(SheetNamed).Visible = True
Else
ToggleBut.Caption = "Unhide " & SheetNamed
Sheets(SheetNamed).Visible = False
End If
End Sub


and then call like so

Private Sub ToggleButton2_Click()
Std_Toggle ToggleButton2, "2-SR"
End Sub
Thanks for hints...got it working, but on somewhat different subject

I note that when I create a toggle button, Excel creates and places
associated macro frame in an Excel Object rather than in an Excel
Module. Can you point me to something that explains why one puts Macro
code in Excel Object instead of Excel Module? Alternatively, is it
merely that by putting code with Excel Object it is restricted to work
only with that object.