Reset Check Box to a Very Hidden and Password Protected Worksheet in a macro

[fred]

Hi all,


I have created a user form where users can click on a check box (made from Form Control). If the check box is checked, a figure will appear in a different cell. So it is either $0 (unchecked) or $100 (checked). I have this check box linked to a different worksheet Sheet1, cell P2. so P2 would either be false/true.


For protection sake I have sheet1 protected with a password "FGHJ" and set it to Very Hidden.


Due to the large size of the user form I have also created a macro to clear out all the manual entry and reset many of the formulae (in case people over-write it) back to its original format, like a blank form.


Here is my question:

If a user fill out the form with this box checked. He/she would make a hard copy and he/she is done. But then if s/he want to work on a different entry and apply the macro. Some how I cannot set the macro to reset Sheet1!P2 to "False" behind the scene.

I managed to get the whole user form cleared out except that check box. That means the second time the same user uses it, the box would have been check unintentionally.


How should this be done? I would not want the user to know the existence of Sheet1. And in case someone knows how to make Sheet1 visible I don't want them have access to the password.


Any idea/suggestion?

 

[Luke M]

Hi fred,

I think you just need to unprotect the sheet (not unhide it) before you make the change to the sheet. Something like:

[pre]

With Sheet1
.Unprotect "FGHJ"
.Range("P2").Value = False
.Protect "FGHJ"
End With

[/pre]
However, I am curious as to how the user is using the Checkbox, since it's doing the same thing (changing a value on a protected sheet). Or, did you just mean the the workbook is protected, and not the sheet? But if that's the case, I'm not sure why your macro isn't working...perhaps you can clarify?

 

[fred]

Thanks Luke. Let me test it out.


The check box is like a Y/N on ordering some products, it is not quantity driven. So if you want it, click on the box and a price would show up and add to the total price.


Given the long order list, some quantity driven, some are not. So if user #1 click Yes to buy the linked cell in sheet1 would be true. He would then print a hard copy in PDF and send it out the customer for signature.


With the click of a macro button, I would like P2 in sheet 1 automatically switch back to "False" so that the check box would be blank for the next order entry.

 

[fred]

Hi Luke,


The macros didn't work. I tried setting Sheet1 to "visible", "hidden" and "very hidden". .Range("P2").Value = False doesn't seems to work??

 

[Luke M]

There should be no reason to unhide sheet1. The only trick is in knowing if Sheet1 is protected (normally used to prevent changes to the sheet). However, since you have a Checkbox linked to the sheet, which in and of itself is changing a value on the sheet, I'm assuming that Sheet1 is NOT protected. This is where I got confused, as I'm not sure what is being password protected then...


Even if a worksheet is very hidden, you can still modify values in it via VB. Feel free to test this out in a new blank workbook. When you try this code:

[pre]

Worksheets("Sheet2").Range("P2").Value = False

[/pre]
What happens exactly?

a) Nothing happens

b) Something changes, but not what you wanted

c) VB gives an error msg

 

[fred]

Hi Luke,


You ARE AWESOME! first i got an error message. then i realized i have sheet2, not sheet1. I changed sheet 2 to sheet1. Here is the full script I have and it works beautifully.


Sheets("Sheet1").Select

Worksheets("Sheet1").Range("P2").Value = False


Sheet1 is password protected so that even if someone finds sheet1 from being very hidden, he can't message around it. I also have P2 cell unlock so it can change its value in protection.


This is great. I don't even have to show the password on vb script!!


What happened with the original script was that it got me nothing. not even an error message. It didn't even seem to have run at all.


Thank you, thank you, thank you! /bow to Excel Ninja Master!

 

[Luke M]

Hooray, and you're very welcome! Glad we were able to get it working. =)

And thanks for explaining the password protection, that was throwing me for a loop. Have a great day!

 

[fred]

just one more note. I just found out that i can't have


Sheets("Sheet1").Select in the vb script when the worksheet is set to very hidden.


So I deleted that line and only one line left. And it still work!! Yay!