Macro to automate page protection based on combo box reply
 

I have a workbook that consists of 13 worksheets. 1st worksheet is an
instructions page. the remaining 12 worksheets correspond to each month of a
calendar year. Each monthly worksheet is protected to prevent entry by users
in certain cells. whilst allowing them to complete the return by filling in
information in given cells.

At the end of the page is a combo box where they select either yes or no to
whether the worksheet is complete. This in turn refers back to the
instruction page updating that the particular worksheet is complete on the
instruction page.

What I am trying to do is a macro, that when the combo box is selected as
yes, automates to protect the entire worksheet regardless of whether a cell
is locked/unlocked, so that the information cannot be changed by the user
without the entering of a password.

Any ideas?
thanks

If you are using a combobox from the control toolbox then maybe like this:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "Yes" Then
Me.Unprotect Password:="yourpassword"
Cells.Locked = True
Me.Protect Password:="yourpassword"
End If
End Sub

Hope this helps
Rowan

wongard wrote:
I have a workbook that consists of 13 worksheets. 1st worksheet is an
instructions page. the remaining 12 worksheets correspond to each month of a
calendar year. Each monthly worksheet is protected to prevent entry by users
in certain cells. whilst allowing them to complete the return by filling in
information in given cells.

At the end of the page is a combo box where they select either yes or no to
whether the worksheet is complete. This in turn refers back to the
instruction page updating that the particular worksheet is complete on the
instruction page.

What I am trying to do is a macro, that when the combo box is selected as
yes, automates to protect the entire worksheet regardless of whether a cell
is locked/unlocked, so that the information cannot be changed by the user
without the entering of a password.

Any ideas?
thanks

Rowan thanks for that, that actually looks pretty good. Quick question not
being a guru in code, but will this protect all cells or only those that are
locked, I will need to protect unlocked cells also.
Thanks
Peter

"Rowan" wrote:

If you are using a combobox from the control toolbox then maybe like this:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "Yes" Then
Me.Unprotect Password:="yourpassword"
Cells.Locked = True
Me.Protect Password:="yourpassword"
End If
End Sub

Hope this helps
Rowan

wongard wrote:
I have a workbook that consists of 13 worksheets. 1st worksheet is an
instructions page. the remaining 12 worksheets correspond to each month of a
calendar year. Each monthly worksheet is protected to prevent entry by users
in certain cells. whilst allowing them to complete the return by filling in
information in given cells.

At the end of the page is a combo box where they select either yes or no to
whether the worksheet is complete. This in turn refers back to the
instruction page updating that the particular worksheet is complete on the
instruction page.

What I am trying to do is a macro, that when the combo box is selected as
yes, automates to protect the entire worksheet regardless of whether a cell
is locked/unlocked, so that the information cannot be changed by the user
without the entering of a password.

Any ideas?
thanks

Cells.Locked = true

Will lock all the cells (no matter if they were locked or unlocked when the
macro started).



wongard wrote:

Rowan thanks for that, that actually looks pretty good. Quick question not
being a guru in code, but will this protect all cells or only those that are
locked, I will need to protect unlocked cells also.
Thanks
Peter

"Rowan" wrote:

If you are using a combobox from the control toolbox then maybe like this:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "Yes" Then
Me.Unprotect Password:="yourpassword"
Cells.Locked = True
Me.Protect Password:="yourpassword"
End If
End Sub

Hope this helps
Rowan

wongard wrote:
I have a workbook that consists of 13 worksheets. 1st worksheet is an
instructions page. the remaining 12 worksheets correspond to each month of a
calendar year. Each monthly worksheet is protected to prevent entry by users
in certain cells. whilst allowing them to complete the return by filling in
information in given cells.

At the end of the page is a combo box where they select either yes or no to
whether the worksheet is complete. This in turn refers back to the
instruction page updating that the particular worksheet is complete on the
instruction page.

What I am trying to do is a macro, that when the combo box is selected as
yes, automates to protect the entire worksheet regardless of whether a cell
is locked/unlocked, so that the information cannot be changed by the user
without the entering of a password.

Any ideas?
thanks


--

Dave Peterson

Thanks for that Dave, hopefully one last query to solve this issue. Have
entered the VB code as previously mentioned by Rowan. When I run the macro
though, VB editor wants to debug for an improper use of Me. Any suggestions
how to rectify this?

"Dave Peterson" wrote:

Cells.Locked = true

Will lock all the cells (no matter if they were locked or unlocked when the
macro started).



wongard wrote:

Rowan thanks for that, that actually looks pretty good. Quick question not
being a guru in code, but will this protect all cells or only those that are
locked, I will need to protect unlocked cells also.
Thanks
Peter

"Rowan" wrote:

If you are using a combobox from the control toolbox then maybe like this:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "Yes" Then
Me.Unprotect Password:="yourpassword"
Cells.Locked = True
Me.Protect Password:="yourpassword"
End If
End Sub

Hope this helps
Rowan

wongard wrote:
I have a workbook that consists of 13 worksheets. 1st worksheet is an
instructions page. the remaining 12 worksheets correspond to each month of a
calendar year. Each monthly worksheet is protected to prevent entry by users
in certain cells. whilst allowing them to complete the return by filling in
information in given cells.

At the end of the page is a combo box where they select either yes or no to
whether the worksheet is complete. This in turn refers back to the
instruction page updating that the particular worksheet is complete on the
instruction page.

What I am trying to do is a macro, that when the combo box is selected as
yes, automates to protect the entire worksheet regardless of whether a cell
is locked/unlocked, so that the information cannot be changed by the user
without the entering of a password.

Any ideas?
thanks


--

Dave Peterson

Rowan wrote the code for use with a Combobox from the control toolbox toolbar
placed on the worksheet.

The code goes behind that worksheet--not in a general module.

If you didn't use that combobox (and don't want to change to it), what did you
use?

wongard wrote:

Thanks for that Dave, hopefully one last query to solve this issue. Have
entered the VB code as previously mentioned by Rowan. When I run the macro
though, VB editor wants to debug for an improper use of Me. Any suggestions
how to rectify this?

"Dave Peterson" wrote:

Cells.Locked = true

Will lock all the cells (no matter if they were locked or unlocked when the
macro started).



wongard wrote:

Rowan thanks for that, that actually looks pretty good. Quick question not
being a guru in code, but will this protect all cells or only those that are
locked, I will need to protect unlocked cells also.
Thanks
Peter

"Rowan" wrote:

If you are using a combobox from the control toolbox then maybe like this:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "Yes" Then
Me.Unprotect Password:="yourpassword"
Cells.Locked = True
Me.Protect Password:="yourpassword"
End If
End Sub

Hope this helps
Rowan

wongard wrote:
I have a workbook that consists of 13 worksheets. 1st worksheet is an
instructions page. the remaining 12 worksheets correspond to each month of a
calendar year. Each monthly worksheet is protected to prevent entry by users
in certain cells. whilst allowing them to complete the return by filling in
information in given cells.

At the end of the page is a combo box where they select either yes or no to
whether the worksheet is complete. This in turn refers back to the
instruction page updating that the particular worksheet is complete on the
instruction page.

What I am trying to do is a macro, that when the combo box is selected as
yes, automates to protect the entire worksheet regardless of whether a cell
is locked/unlocked, so that the information cannot be changed by the user
without the entering of a password.

Any ideas?
thanks


--

Dave Peterson


--

Dave Peterson

Hi

The code assumes that the combobox is from the controls toolbox and must
be pasted into the sheets code module not a standard module. Right click
the sheet tab, select View Code and paste the event in there.

Regards
Rowan

wongard wrote:
Thanks for that Dave, hopefully one last query to solve this issue. Have
entered the VB code as previously mentioned by Rowan. When I run the macro
though, VB editor wants to debug for an improper use of Me. Any suggestions
how to rectify this?

"Dave Peterson" wrote:


Cells.Locked = true

Will lock all the cells (no matter if they were locked or unlocked when the
macro started).



wongard wrote:

Rowan thanks for that, that actually looks pretty good. Quick question not
being a guru in code, but will this protect all cells or only those that are
locked, I will need to protect unlocked cells also.
Thanks
Peter

"Rowan" wrote:


If you are using a combobox from the control toolbox then maybe like this:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "Yes" Then
Me.Unprotect Password:="yourpassword"
Cells.Locked = True
Me.Protect Password:="yourpassword"
End If
End Sub

Hope this helps
Rowan

wongard wrote:

I have a workbook that consists of 13 worksheets. 1st worksheet is an
instructions page. the remaining 12 worksheets correspond to each month of a
calendar year. Each monthly worksheet is protected to prevent entry by users
in certain cells. whilst allowing them to complete the return by filling in
information in given cells.

At the end of the page is a combo box where they select either yes or no to
whether the worksheet is complete. This in turn refers back to the
instruction page updating that the particular worksheet is complete on the
instruction page.

What I am trying to do is a macro, that when the combo box is selected as
yes, automates to protect the entire worksheet regardless of whether a cell
is locked/unlocked, so that the information cannot be changed by the user
without the entering of a password.

Any ideas?
thanks

--

Dave Peterson

The Combo box I am using is from the FORMS Toolbar. should I just change it
to one from the controls toolbox? Would that make things easier?

"Rowan" wrote:

Hi

The code assumes that the combobox is from the controls toolbox and must
be pasted into the sheets code module not a standard module. Right click
the sheet tab, select View Code and paste the event in there.

Regards
Rowan

wongard wrote:
Thanks for that Dave, hopefully one last query to solve this issue. Have
entered the VB code as previously mentioned by Rowan. When I run the macro
though, VB editor wants to debug for an improper use of Me. Any suggestions
how to rectify this?

"Dave Peterson" wrote:


Cells.Locked = true

Will lock all the cells (no matter if they were locked or unlocked when the
macro started).



wongard wrote:

Rowan thanks for that, that actually looks pretty good. Quick question not
being a guru in code, but will this protect all cells or only those that are
locked, I will need to protect unlocked cells also.
Thanks
Peter

"Rowan" wrote:


If you are using a combobox from the control toolbox then maybe like this:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "Yes" Then
Me.Unprotect Password:="yourpassword"
Cells.Locked = True
Me.Protect Password:="yourpassword"
End If
End Sub

Hope this helps
Rowan

wongard wrote:

I have a workbook that consists of 13 worksheets. 1st worksheet is an
instructions page. the remaining 12 worksheets correspond to each month of a
calendar year. Each monthly worksheet is protected to prevent entry by users
in certain cells. whilst allowing them to complete the return by filling in
information in given cells.

At the end of the page is a combo box where they select either yes or no to
whether the worksheet is complete. This in turn refers back to the
instruction page updating that the particular worksheet is complete on the
instruction page.

What I am trying to do is a macro, that when the combo box is selected as
yes, automates to protect the entire worksheet regardless of whether a cell
is locked/unlocked, so that the information cannot be changed by the user
without the entering of a password.

Any ideas?
thanks

--

Dave Peterson

For the macro I gave you to work you would need to change to a combobox
from the Control Toolbox.

If you want to stick with the Forms ComboBox then you could use a macro
in a standard module eg:

Sub prtct()
If ActiveSheet.Range("Z1").Value = 1 Then
ActiveSheet.Unprotect Password:="yourpassword"
Cells.Locked = True
ActiveSheet.protect Password:="yourpassword"
End If
End Sub

Where Z1 is the Cell Link for the combobox and Yes is the first option
in the combobox followed by No, and prtct is the macro assigned to the
combobox.

Regards
Rowan

wongard wrote:
The Combo box I am using is from the FORMS Toolbar. should I just change it
to one from the controls toolbox? Would that make things easier?

Thanks Rowan I will try that one

"Rowan" wrote:

For the macro I gave you to work you would need to change to a combobox
from the Control Toolbox.

If you want to stick with the Forms ComboBox then you could use a macro
in a standard module eg:

Sub prtct()
If ActiveSheet.Range("Z1").Value = 1 Then
ActiveSheet.Unprotect Password:="yourpassword"
Cells.Locked = True
ActiveSheet.protect Password:="yourpassword"
End If
End Sub

Where Z1 is the Cell Link for the combobox and Yes is the first option
in the combobox followed by No, and prtct is the macro assigned to the
combobox.

Regards
Rowan

wongard wrote:
The Combo box I am using is from the FORMS Toolbar. should I just change it
to one from the controls toolbox? Would that make things easier?