Checkboxes issues

[KittyS]

Hi,

I have a file with multiple worksheets (tabs). The checkboxes can be filled in one sheet, and are also visible on another. But on this other tab, they may not be altered! I have code like this:
Sub CheckBox3_Click()
'ga film
Worksheets("Products").Select
Range("b13").Select
If ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = True Then
MsgBox "Checked! Changes only allowed by Branch Office."
ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = False
Else
MsgBox "Unchecked! Changes only allowed by Branch Office."
ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = False
End If

End Sub
It never seems to read the first "if", it always goes straight to the "Else" condition. Can anyone please help? Thanks!!! Kitty

 

[Norie]

Kitty

Change ActiveSheet to Worksheets("Sheet3") or whatever.

 

[MickG]

Try changing the line below:-

If ActiveSheet.Shapes("Check Box 3").ControlFormat.Value > 0 Then

 

[KittyS]

Thanks Mick and Norie, the "if" routine now works, but actually I need it to prevent the checkbox to be changed on this particular worksheet. And that still doesn't work like this.. it keeps accepting the change. I also tried "checkbox3_change" but that doesn't work. Somehow I need to keep the checkboxes visible but not changeable from this sheet. Any more ideas?
Thanks for you help with this!

Kitty

 

[ukmike007]

Have you tried protecting the sheet with the review ribbon? You will need to lock the cell in format cells first.

 

[KittyS]

Thanks Mick and Norie, the "if" routine now works, but actually I need it to prevent the checkbox to be changed on this particular worksheet. And that still doesn't work like this.. it keeps accepting the change. I also tried "checkbox3_change" but that doesn't work. Somehow I need to keep the checkboxes visible but not changeable from this sheet. Any more ideas?
Thanks for you help with this!

Kitty

 

[KittyS]

Hi, Yes I did, but that doesn't work. I can only protect the source sheet for the check boxes but then all changes from all worksheets for the checkboxes don't work....

 

[MickG]

Perhaps something like:-

Sub CheckBox3_Click()
With ActiveSheet.Shapes("Check Box 3").ControlFormat
   If .Value > 0 Then
        .Value = False
   Else
        .Value = True
   End If
End With
MsgBox "The CheckBox Cannot be changed !!!"
End Sub

 

[KittyS]

I reverted to not having the checkboxes on the other sheet, but just a conditional formatting based on the contents True/False of the initial checkboxes. Did the trick. Thanks all for your suggestions!!!