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]

David,

Here is a little code that may give you ideas

Put 2 or 3 checkboxes in a test sheet "Sheet1"
Ensure that the cb is within the height of the row in which it sits.
Copy this code into a code module or the sheet module in the vba editor.

Assign the code to each of the checkboxes, and link each cb to a cell in an adjacent column.

Then as you click each cb you will get some messages that have details of which cb you clicked and it's state and linked cell address and the address of the cell it sits in, etc

Sub CBMacro()
Dim w As Worksheet
myCB = Application.Caller  'set variable to name of cb clicked
MsgBox " My name is  " & myCB 'name of checkbox changed
Set w = Sheets("Sheet1") 'sheet with check box

w.Shapes(myCB).OLEFormat.Object.Value = False  'set value false
MsgBox "Code has just forced this cb to unticked for illustrative purposes"
MsgBox "My state is  " & w.Shapes(myCB).OLEFormat.Object.Value  'will be 1 = true -4146 if false

w.Shapes(myCB).OLEFormat.Object.Value = True  'set value true or edit for false
MsgBox "Code has just forced it to be True ie " & w.Shapes(myCB).OLEFormat.Object.Value   'will be 1 = true

MsgBox "My linked cell is  " & w.Shapes(myCB).OLEFormat.Object.LinkedCell  'address of linked cell
MyLink = w.Shapes(myCB).OLEFormat.Object.LinkedCell  'Set variable MyLink = to Linked cell address for use below***

MsgBox "The value in my linked cell is  " & Range(w.Shapes(myCB).OLEFormat.Object.LinkedCell).Value 'value of linked cell

MsgBox "The address of my top left is  " & w.Shapes(myCB).OLEFormat.Object.TopLeftCell.Address ' cell under top left of the checkbox object
'if check box is within the height of a row
MsgBox "I will now clear the value in the linked cell" & Chr(13) _
& "this will uncheck the check box"
w.Range(MyLink).ClearContents  '***** Linked cell value = 0 unchecks the checkbox withou clicking
'setting the linkedcell value to 0 or "" would do the same thing
End Sub

Hopefully you can put these bits and bobs to some use.

 

[USAMax]

I found this code works if I manualy enter the change but if the Check Box changes the cell nothing happens.

Private Sub Worksheet_Change(ByVal Target As Range)
CurCol = 1
Do
CurCol = CurCol + 1
Loop Until Cells(1, CurCol) = "IA"

ThisCol = Target.Column
If Target.Row = 3 And CurCol >= ThisCol Then
RESULT = MsgBox(Cells(1, ThisCol) & " = " & Cells(3, ThisCol), vbOKOnly, "CLICK RESULTS")
End If
End Sub

 

[Snakehips]

David,

I don't think you will achieve anything with the Worksheet Change event.
Take a look at the last couple of posts and then come back.

 

[USAMax]

Thank you Tony but I cannot believe it is this difficult. An entire day working on one function. The code I entered last will give me exactly what I want if it will detect the change from the check box.

Neither of your codes worked. Here is how I changed the code to work with my Check Box called CheckBoxIA on the new Sheet3.

Sub CBMacro()
Dim w As Worksheet
myCB = Application.Caller 'set variable to name of cb clicked
MsgBox " My name is " & CheckBoxIA 'name of checkbox changed
Set w = Sheets("Sheet3") 'sheet with check box

w.Shapes(CheckBoxIA).OLEFormat.Object.Value = False 'set value false
MsgBox "Code has just forced this cb to unticked for illustrative purposes"
MsgBox "My state is " & w.Shapes(CheckBoxIA).OLEFormat.Object.Value 'will be 1 = true -1417 if false

w.Shapes(CheckBoxIA).OLEFormat.Object.Value = True 'set value true or edit for false
MsgBox "Code has just forced it to be True ie " & w.Shapes(CheckBoxIA).OLEFormat.Object.Value 'will be 1 = true

MsgBox "My linked cell is " & w.Shapes(CheckBoxIA).OLEFormat.Object.LinkedCell 'address of linked cell
MyLink = w.Shapes(CheckBoxIA).OLEFormat.Object.LinkedCell 'Set variable MyLink = to Linked cell address for use below***

MsgBox "The value in my linked cell is " & Range(w.Shapes(CheckBoxIA).OLEFormat.Object.LinkedCell).Value 'value of linked cell

MsgBox "The address of my top left is " & w.Shapes(CheckBoxIA).OLEFormat.Object.TopLeftCell.Address ' cell under top left of the checkbox object
'if check box is within the height of a row
MsgBox "I will now clear the value in the linked cell" & Chr(13) _
& "this will uncheck the check box"
w.Range(MyLink).ClearContents '***** Linked cell value = 0 unchecks the checkbox withou clicking
'setting the linkedcell value to 0 or "" would do the same thing
End Sub

 

[USAMax]

Going back to the larger code that you gave me.

MsgBox " My name is " & CheckBoxIA 'name of checkbox changed

This line shows no value for CheckBoxIA after I assign the macro to the Check Box. CheckBoxIA is the name I gave it.



As for the shorter code you gave me I tried a private subroutine and a standard routine and it did not work but it works if I assign the macro. Based on this I can add this line:

CallingCheckBox = Application.Caller

I am trying it now.

 

[USAMax]

This is working perfectly! Thank you Tony!!!

Can you help me with this line for my understanding as it failes everytime? It gives me error 400.
w.Range(MyLink).ClearContents '***** Linked cell value = 0 unchecks the checkbox withou clicking