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!


--
Jae
------------------------------------------------------------------------
Jae's Profile: http://www.excelforum.com/member.php...o&userid=26055
View this thread: http://www.excelforum.com/showthread...hreadid=393973

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

good luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393973

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

Thanks in advance,

Jae


--
Jae
------------------------------------------------------------------------
Jae's Profile: http://www.excelforum.com/member.php...o&userid=26055
View this thread: http://www.excelforum.com/showthread...hreadid=393973

ActiveSheet.UnProtect
'your code here
ActiveSheet.Protect

HTH

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393973

Bruce,

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

Jae


--
Jae
------------------------------------------------------------------------
Jae's Profile: http://www.excelforum.com/member.php...o&userid=26055
View this thread: http://www.excelforum.com/showthread...hreadid=393973

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393973

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


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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393973

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393973

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

"swatsp0p" wrote:


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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393973