vendredi 6 mars 2015

enable/disable checkbox by its resident cell location

I want to disable/enable a checkbox in an excel sheet using vba based on the value/conditon of another checkbox. I cannot use the checkbox name, I want to use it's cell location in reference to the cell location of the checkbox that is enabling/disabling it. something like this:



Sub Software2()

Dim myRange As Range

Set myRange = Range(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address)

If ActiveSheet.Shapes(Application.Caller).ControlFormat.Value = 1 Then

myRange.Interior.ColorIndex = 35
myRange.Offset(0, 1).Interior.ColorIndex = 35
myRange.Offset(0, 2).Interior.ColorIndex = 35
myRange.Offset(1, 1).Interior.ColorIndex = 44
myRange.Offset(1, 2).Interior.ColorIndex = 44
myRange.Offset(2, 1).Interior.ColorIndex = 44
myRange.Offset(2, 2).Interior.ColorIndex = 44
Else

myRange.Interior.ColorIndex = 44
myRange.Offset(0, 1).Interior.ColorIndex = 44
myRange.Offset(0, 2).Interior.ColorIndex = 44
myRange.Offset(1, 1).Interior.ColorIndex = 0
myRange.Offset(1, 2).Interior.ColorIndex = 0
myRange.Offset(2, 1).Interior.ColorIndex = 0
myRange.Offset(2, 2).Interior.ColorIndex = 0
'ActiveSheet.Shapes(location of other checkbox).ControlFormat.Enabled = 0
'ActiveSheet.Shapes(location of other checkbox).ControlFormat.Enabled = 0

End If
End Sub

Aucun commentaire:

Enregistrer un commentaire