Multiple Check Boxes calling same macro

[Guest]

I have a list of items each with a named check box.

All the check boxes run the same priece of code with only one difference
each time, being related to data on the line being amended.

When a check box is changed, how do I find out programmatically which check
box has been clicked ?

I want to avoid having 50 plus macros to deal with each check box separately.

 

[Bob Phillips]

Public Sub Test()
Select Case Application.Caller
Case "Check Box1"
MsgBox "1 called"
Case "Check Box 2"
MsgBox "2 called"
'etc.
End Select
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Fast and fantastic answer !!

Public Sub Test()
Select Case Application.Caller
Case "Check Box1"
MsgBox "1 called"
Case "Check Box 2"
MsgBox "2 called"
'etc.
End Select
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Incidental]

Hi Paul

You could try something like the code below which will work but will
lead to still haveing more code than you need i would recommend you
check out the following link which gives a great example of using a
class module to do the work for you.

http://j-walk.com/ss/excel/tips/tip44.htm

Option Explicit
Dim i As Integer
Dim Ctrl As MSForms.Control

Private Sub CheckBox1_Click()
i = 1
DoYourStuff
End Sub
Private Sub CheckBox2_Click()
i = 2
DoYourStuff
End Sub
Private Sub CheckBox3_Click()
i = 3
DoYourStuff
End Sub

Sub DoYourStuff()

Set Ctrl = UserForm1.Controls("CheckBox" & i)

'from here you can refer to the clicked control
'using " Ctrl "

MsgBox Ctrl.Name

End Sub

Hope this helps

S