Run macro when state of Check Box changes, return the status and position of the Check Box.

[USAMax]

I have been programing in Excel VBA for years but I am new to Check Boxes.

I have four problems all related to single Check Boxes. Each check box pertains to a specific row and column.

How can I launch a macro as soon as a Check Box's value changes?

My macro will be contingent on the status of a lot of other check boxes and the row the check box is positioned on. How can I get the status of each check box and how can I determine the row it is on?

Lastly, how can I align the Check Boxes for aesthetic reasons only?

 

[Snakehips]

USAMax,

You do not say whether you are using form checkboxes or Activex checkboxes?

The solution will differ a little dependent upon which.

Perhaps, see if a couple of my recent posts help at all. Otherwise come back with additional detail.

LINK1 LINK2

 

[USAMax]

Thank you Tony, can you tell me how to determine if the checkboxes the previous developer used is a form checkbox or an Activex checkbox?

I will be reviewing the links you sent me while I wait for your reply.

 

[mikerickson]

What is the name of the check box? When you right click on the checkbox, does the pop-up menu include the item PROPERTIES?

 

[USAMax]

The check box names are Check Box 1 through Check Box 60. Yes I can see the PROPERTIES when I right click.

My research shows that this should work to run when the check box changes:
Public Sub Checkbox1_Click()
If Sheet5.DrawingObjects("Form").Value > 0 Then
'Do Stuff
Else
'Undo Stuff
End If
End Sub

And I've tested these and they work to detect the current status:
If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = 1 Then MyTest = True
If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value < 1 Then MyTest = False

These may not be the best way but for now they are all I have.

Thank you Tony.

 

[USAMax]

Can someone please help me, I have a checkbox that when created has the name Check Box 1. I can right click on it and choose Format Control. There I have six tabs: Colors and Lines, Size, Protection, Properties, Alt Text and Control.

I do not know if the boxes that are used are form checkboxes or Activex checkboxes and that is why I am giving this information.

I need to know how to run a macro once the check box is changed............

Please any help would be appreciated,

David

 

[Snakehips]

David,

Sorry for not responding to yesterday's post but for some reason, I would appear not to be receiving notifications.

Your check boxes are Form type.

Firstly, with a form checkbox you can right click and from the popup menu, select 'Assign Macro'
You can then select, from established macros, the code that you wish the checkbox to run from the dialog box. You can assign the same code to all checkboxes if you wish or assign different code. When writing code that depends upon the state of the checkboxes you might find it easier to make use the 'Linked Cell' of the checkboxes ( perhaps cells in a hidden column). The value, true or false in the cell(s) reflects the state of the check box(s).
The assigned code will run each time the checkbox is clicked. Ie ticking or unticking. If you only wish the code to run when ticked, then you will need the code to test the state and exit if the cb is not ticked. Again this can be done by testing the True or false value of the linked cell(s). Clearing the contents of a linked cell or cells is a good way to untick checkboxes without actually clicking them. Done this way, no macros will be triggered.

I hope that helps a little. If you want something more specific then please come back with more detail.

 

[USAMax]

Thank you Snakehips, I am embarrassed to say that I did not see, "Assign Macro" in the popup. Thank you for pointing this out.

If I have all the check boxes assigned to the same macro and I use a Case statement, is there anyway I can tell which check box initiated it?

I have been working on your second option, to detect a change in the linked cell of the check box. Unfortunately my skills are limited there too. All examples have the same way it starts:

Private Sub Worksheet_Change(ByVal Target As Range)

Can you tell me what values is this looking for? One checkbox is linked to: 'E1'!$AD$3.

 

[USAMax]

I have tried the following code but I cannot get anything to run. The code is on the E1 sheet so it should see when there are changes anywhere on the worksheet. My checkbox is linked to AD3.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "AD3" Then

Application.EnableEvents = False
Range("AD2") = "FALSE"


Application.EnableEvents = True
Range("AD2") = "TRUE"

End If

End Sub

 

[mikerickson]

Try this code to identify which checkbox was clicked.

If Typename(Application.Caller) = "String" Then
    MsgBox "Control " & Application.Caller & " called this"
Else
    Msgbox "not a control called this"
End If