Can one macro serve several check boxes?

[Guest]

I put several check boxes on a chart and assigned different macro to each of
them.

All each macro is doing is hide-unhide a specific column. Say I have ckeck
boxes "Weight", "Length" and "Width". Assigned macro A, B and C accordingly.
When I click "Weight" checkbox it calls macro "A" which toggles "hidden"
status for column "J". So all macro have same functionality, only for
different columns.

I would like to build a _single_ macro, capable to know which control box
was clicked.

So, all check boxes will be assigned the same macro and that macro should be
able to receive check box name as a parameter and then use a CASE statement
(or several IFs) to process appropriate columns.

Is it possible and, if yes, how?

Thank you

 

[Andy Pope]

Hi,

You can use Application.Caller to tell you the name of the control that was
clicked.
To give the controls meaningful names rename then using the Name Box, next
to the formula bar.

You code could look something like this,

Sub ChartCheckBoxClick()

Dim blnChecked As Boolean

With ActiveSheet
' get whether control is checked or not
blnChecked = Not (.Shapes(Application.Caller).ControlFormat.Value
= -4146)
Select Case Application.Caller
Case "Check Box 1"
.Columns(2).Hidden = Not blnChecked
Case "Check Box 2"
.Columns(3).Hidden = Not blnChecked
Case "Check Box 3"
.Columns(4).Hidden = Not blnChecked
Case Else
' do nothing
End Select

End With
End Sub

Cheers
Andy

 

[Guest]

Andy, thanks one more time!

Using your hint regarding Application.Caller I was able to come up with
working code.

Your version did not seem to be working, perhaps due to Excel version (I am
on 2003). I changed column references from numbers to letters, but as I click
on a check box, it was giving me the Run-time error '438': Object doesn't
support this property or method. Debug would point to

..Columns("J").Hidden = Not blnChecked

lines depending on which check box I clicked (so, Application.caller is
working!). I changes column reference to numbers, just as in your code -
same error.

Then I blended old code with new and that did work!! It now looks as
following:

Sub Check_Box_Click()

Sheets("Summary_Worksheet").Select

Select Case Application.Caller
Case "cbAdvocate"
Columns("J").Select
Case "cbBelgrade"
Columns("K").Select
Case Else
End Select

If Selection.EntireColumn.Hidden = False Then
Selection.EntireColumn.Hidden = True
Else
Selection.EntireColumn.Hidden = False
End If

Sheets("Star-plot").Select
End Sub

I assume I should be able to build second group of check boxes and put
together similar macro which will hide-unhide ROWS thus controlling spokes on
my radar chart...

Thank very much you your help!
Naum

 

[Andy Pope]

I did test the code I posted in 2003, but still I'm glad you managed to get
your code working.

try this revision, which does not require the selection of the sheets.

Sub Check_Box_Click()

with Sheets("Summary_Worksheet")

Select Case Application.Caller
Case "cbAdvocate"
.Columns("J").Hidden = Not .Columns("J").Hidden
Case "cbBelgrade"
.Columns("K").Hidden = Not .Columns("K").Hidden

Case Else
End Select
End Sub

And you should be able to use the Rows object to do similar hiding of rows.

Cheers
Andy