linked cell to change another cell

[Brettjg]

I have a checkbox linked to cell A1. In the cell B1 I have a number which
could be (say) 1 or 2. When the checkbox is "unchecked" and therefore A1 =
FALSE I want to clear the contents of B1. I can't do it by a formula in B1
because the number has been entered manually.

The best way I can think of is to run a macro when checkbox is clicked, and
if A1 = FALSE then clearcontents of B1, but how do I refer to the linked cell
for the particular checkbox in the macro? This same macro would be run for up
to 50 checkboxes all in column "A" so I could just refer to the cell by
Cell("A" & rownum).Offset(0, 1) but how do I find out what 'rownum' is for
the checkbox that has been clicked please?

Regards, Brett

 

[Dave Peterson]

First, I would use the checkbox from the Forms toolbar--not the checkbox from
the Control Toolbox toolbar.

Each of the checkboxes from the Forms toolbar could have the same macro assigned
to it.

Here are two routines. The first lays out checkboxes from the forms toolbar in
C3:C10. That's a one time only routine (or rerun when you need to).

The second routine is the one that does the work when you click the checkboxes.

Option Explicit
Sub LayOutCheckboxes()

Dim myCBX As CheckBox
Dim myCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.CheckBoxes.Delete 'nice for testing
For Each myCell In .Range("C3:C10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, -2).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!CbxClick"
End With
End With
Next myCell
End With
End Sub
Sub CBXClick()

Dim myCBX As CheckBox

With ActiveSheet
Set myCBX = .CheckBoxes(Application.Caller)
If myCBX.Value = xlOff Then
.Cells(myCBX.TopLeftCell.Row, "B").ClearContents
End If
End With
End Sub

 

[Brettjg]

Thanks alot Dave, as always (?) your code works first time.

One other thing with this is that I want to change the colour of the
checkbox depending on whether it's checked or not. In the interim I have made
the check boxes transparent and get the illusion of them changing colour by
changing the colour of the cell behind it (which is also the linked cell) but
it's not really a very elegant way to do it. The code I have is as follows
and I have commented out the two lines that don't work for changing checkbox
colour:

Sub clear_batch_qty()
Dim myCBX As CheckBox
With ActiveSheet
Set myCBX = .CheckBoxes(Application.Caller)
If myCBX.Value = xlOff Then
.Cells(myCBX.TopLeftCell.Row, "E").ClearContents
.Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 40
.Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 40
' .Shapes(myCBX).ShapeRange.Select
' Selection.Fill.ForeColor.SchemeColor = 47
Else
.Cells(myCBX.TopLeftCell.Row, "E").Value = 1
.Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 10
.Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 10
End If
.Cells(myCBX.TopLeftCell.Row, "E").Select
End With
End Sub

Regards, Brett

 

[Dave Peterson]

Maybe you can merge this into your code:

With myCBX.ShapeRange
With .Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = 47
.Transparency = 0#
End With
'delete this if you don't want to change the line attributes
With .Line
.Weight = 0.75
.DashStyle = msoLineSquareDot
.Style = msoLineSingle
.Transparency = 0#
.Visible = msoTrue
.ForeColor.SchemeColor = 57
.BackColor.RGB = RGB(255, 255, 255)
End With
End With

Wouldn't you want the opposite to happen if the checkbox is checked? (in the
Else portion of your if/then/else statement?)

 

[Brettjg]

Just what I needed, thakyou very much. My colour palette is quite different
so maybe that threw you off when you asked if I wanted the opposite. Regards.

 

[Dave Peterson]

I meant that if you change the color when the checkbox is not checked, then
maybe you want to change the color back if the checkbox is checked. Much like
you did with column D of that row.