VB code in excell

[Guest]

I have a spreadsheet with a few check boxes that have macro attached to them.
When a check box is checked, a macro runs that makes the font in a
corresponding cell, bold. A cell link is also attached to each check box to
set a value for anothe cell on the page. I have about 20 of these boxes and
have listed the code I use to bold the font in the cell:

Sub CheckBox88_Click()
Range("B23").Font.Bold = Range("A23").Value
End Sub

What I would like to do, is make one sub routine that I could use for every
check box rather that having 20 of these little subs. I cannot figure out the
syntax or how to do this. Does anyone have any ideas?

 

[Bob Phillips]

Take a look at Application.Caller, you can test that to see which checkbox
has been clicked, and do a Select Case on the value

Select Case Application.Caller
Case "Check Box 1": MsgBox "Check Box 1"
Case "Check Box 2": MsgBox "Check Box 2"
Case "Check Box 3": MsgBox "Check Box 3"
End Select

--

HTH

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

 

[Guest]

Thank you. I had never dealt with this property before. In my case, if I have
20 check boxes, I will need 20 cases, right? This is what I tried to use to
make a generic routine that all the check boxes could use:

Sub Bold()
Dim CaseNum As String
CaseNum = Application.Caller
CaseNum = Right(CaseNum, 2)
Range("B" & CaseNum).Font.Bold = Range("A" & CaseNum).Value
End Sub

Can you help me understand what I have done wrong here?

 

[Guest]

Thank you very much, I have never worked that property before. I can see it
could be used for a lot of things. I would like to make a generic routine
that all of the checkboxes could run, here is what I came up with:

Sub Bold()
Dim CaseNum As String
CaseNum = Application.Caller
CaseNum = Right(CaseNum, 2)
Range("B" & CaseNum).Font.Bold = Range("A" & CaseNum).Value
End Sub

Can you help me figure out what is wrong with this? I am not familiar with
the syntax on setting the Range Property.

 

[Bob Phillips]

That looks the way to go. I think this is what you want

Sub Bold()
Dim CaseNum As String
CaseNum = Application.Caller
CaseNum = Right(CaseNum, 2)
Range("B" & CaseNum).Font.Bold = True
End Sub



--

HTH

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

 

[Guest]

Thans for the help. I couldn't get my original try to work because the check
boxes kept losing their their names. I got that fixed and found that it
worked fine. I left the bold property = the value of the link cell so that
the checked/unchecked would bold/unbold. Thank you so much for your help.