Error when setting Interior ColorIndex

[Glaswegian]

I'm sure there is an easy explanation for this, but I can't see it at the moment!
All I'm trying to do is clear some cells and change the interior colour based on the value of a Control Toolbox checkbox. However, I receive an error '1004 - unable to set ColorIndex property of Interior Class'. The code is so simple


Private Sub CheckBox1_Click()
Dim myRange As Range
Set myRange = Worksheets("Sheet7").Range("A1:D1")
If CheckBox1.Value = True Then
With myRange
.Interior.ColorIndex = 6
.Value = ""
End With
End If
End Sub

It works fine if I wrap the ColorIndex line in a single sub.

Sorry if this is a bit stupid but I'm stumped!

Thanks

Iain - XL97 on NT4

[Andy Pope]

Hi Iain ,

The problem is caused by the control taking focus.
Add the line to select a cell and all should be ok.


Private Sub CheckBox1_Click()
Dim myRange As Range

ActiveCell.Activate ' to take focus away from control

Set myRange = Worksheets("Sheet1").Range("A1:C1")
If Sheet1.CheckBox1.Value = True Then
With myRange
.Interior.ColorIndex = 6
.Value = ""
End With
End If
End Sub

[Glaswegian]

Andy

Of course!! :mad:

It's always the most obvious things that catch me out...

Many thanks.

Regards