Check box help please!

[Jae]

I have form that I am working on that has approximately 120 check boxes
(I used the forms check boxes). I read previous threads about creating
a macro to clear out the checks, which I have done successfully, but I
would like the macro to do one more thing ... After clearing out all
of the checks, I would like it to place checks on a range of check
boxes.

I thought I could use the following script, but it doesn't work when
the sheet is protected.

Sub UnCheckBox()

With Sheet1

..CheckBoxes.Value = False

Range("N55:N209").Select
Selection.Replace What:="FALSE", Replacement:="TRUE",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

End With

End Sub

Can someone help please?

Thanks!

 

[swatsp0p]

have the macro 'unprotect' the sheet, run the code and 'reprotect' th
sheet.

good luc

 

[Jae]

Can you tell me how I would do that in the script? I'm fairly new t
using visual basic.

Thanks in advance,

Ja

 

[swatsp0p]

ActiveSheet.UnProtect
'your code here
ActiveSheet.Protect

HTH

Bruce

 

[Jae]

Bruce,

Thanks for all your help. One more question, how do I incorporate a
password to this script?

Jae

 

[swatsp0p]

We will assume you have used the password "Lock" to protect your sheet.
Use this code:

ActiveSheet.UnProtect Lock
'your code here
ActiveSheet.Protect Lock

of course, adjust the password to meet your needs.

Reminder: passwords on sheets only stop someone from inadvertantly
changing data. Any one so inclined can break an Excel password in a
matter of a few minutes!

HTH

Bruce

 

[Guest]

Bruce,

Thanks again. I ran across a problem though ... It appears as though the
password to protect the sheet does not get re-applied. Will you look at my
script and advise?

Thanks again!

Sub UnCheckBox()

ActiveSheet.Unprotect abc123

With Sheet1

..CheckBoxes.Value = False

Range("N55:N209").Select
Selection.Replace What:="FALSE", Replacement:="TRUE", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

ActiveSheet.Protect abc123


End With

End Sub

 

[swatsp0p]

typo on my part, sorry... code should read:


Sub UnCheckBox()

ActiveSheet.Unprotect Password:="abc123"
With Sheet1

..CheckBoxes.Value = False

Range("N55:N209").Select
Selection.Replace What:="FALSE", Replacement:="TRUE", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End With

ActiveSheet.Protect Password:="abc123"

End Sub
Does this work for you?

Bruce

 

[Guest]

Bruce, you're a life saver. Thanks for all you help. Jae