Declare object by means of variables

[Sebastian K.]

Hi

Need a bit of help.

I'm using a bunch of checkboxes to show/hide graphs i a chart.

I want to set a variable as an object which is combined of a string and an integer, like this.

Set chk="CheckBox" & CId

Where chk is the object for use in the code whiche hides/shows the graph. The CId is a variable that depends on which CheckBox I toggle.

Dim rng As Range, chk As Object, CId As Integer
Private Sub CheckBox1_Click()
    CId = 1
    ToggleColumn
End Sub
Private Sub CheckBox10_Click()
    CId = 2
    ToggleColumn
End Sub
Private Sub ToggleColumn()
    chk = "CheckBox" & CId
    rng = Range(Cells(22, 5 + CId))
    
    If chk.Value = True Then
        rng.EntireColumn.Hidden = False
    Else: rng.EntireColumn.Hidden = True
    End If

End Sub

As it is right now it doesn't work, is there any way to do what I want to?
Essentially it is only a matter of avoiding having ti write the same code for each checkbox.

Best regards
Sebastian

 

[Andrew Poulsom]

If you controls are on a UserForm you can use:

Set chk = Me.Controls("CheckBox" & CId)

If your controls are on a worksheet:

Private Sub ToggleButton1_Click()
    Dim chk As OLEObject
    Dim rng As Range
    Set chk = ActiveSheet.OLEObjects("CheckBox" & CId)
    Set rng = Cells(22, 5 + CId)
    rng.EntireColumn.Hidden = Not chk.Object.Value
End Sub

 

[AlphaFrog]

Or you can pass the arguments when you call ToggleColumn

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()<br>    ToggleColumn CheckBox1.Value, 1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox10_Click()<br>    ToggleColumn CheckBox10.Value, 2<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ToggleColumn(cbState <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, CId <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>)<br><br>    Range(Cells(22, 5 + CId)).EntireColumn.Hidden = <SPAN style="color:#00007F">Not</SPAN> cbState<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Sebastian K.]

Or you can pass the arguments when you call ToggleColumn

Private Sub CheckBox1_Click()
ToggleColumn CheckBox1.Value, 1
End Sub

Private Sub CheckBox10_Click()
ToggleColumn CheckBox10.Value, 2
End Sub

Private Sub ToggleColumn(cbState As Boolean, CId As Integer)

Range(Cells(22, 5 + CId)).EntireColumn.Hidden = Not cbState

End Sub

Thanks .... worked nicely.

I had to change the Range object as it failed, so the code now looks like this.

Private Sub CheckBox8_Click()
    ToggleColumn CheckBox8.Value, 8
End Sub
Private Sub CheckBox9_Click()
    ToggleColumn CheckBox9.Value, 9
End Sub
Private Sub ToggleColumn(cbState As Boolean, CId As Integer)
    Cells(22, 5 + CId).EntireColumn.Hidden = Not cbState
End Sub

I was thinking is there a way to make it detect which checkbox I toggled so that code could be simplified dow to this:

Private Sub CheckBox8_Click()
    ToggleColumn
End Sub
Private Sub CheckBox9_Click()
    ToggleColumn
End Sub
Private Sub ToggleColumn(cbState As Boolean, CId As Integer)
    
*****
Code which identifies which Checkbox is toggled and sets cbState and CId
*****
    Cells(22, 5 + CId).EntireColumn.Hidden = Not cbState
End Sub

Just a thought.

The code works fine as it is now.

Best regards
Sebastian

 

[AlphaFrog]

I was thinking is there a way to make it detect which checkbox I toggled so that code could be simplified dow to this...

This link describes how it could be done for the ActiveX Textbox object. The same would apply for your Checkbox object.

If you change your checkboxes from the Activex type to a Form type (from the Form control toolbar) and assign each one to the same macro, you could then use Application.Caller to return the name of the Form-type object that called the macro and parse the CId from the checkbox name. Application.Caller doesn't work with the ActiveX type checkboxes that you are currently using.