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.

Brettjg wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson