Can't protect sheet when macro enabled

1

1Roberta1

Board Regular
Joined
Jul 27, 2007
Messages
230
Hi
I was wondering if someone could help me. I have a worksheet named Questionnaire that has several questions and gives a choice of answers from a drop downlist. The user filling in the worksheet can pick as many answers as they want from the drop downlist and it places each answer chosen from the same question in the adjacent cell. The dropdown list is populated from a worksheet called list. I used the following macro to get it to place the answers in the adjacent cell which allows them to choose as many as they want (I got this off the internet and take no credit for the macro that is written ~ What I will say it does work perfectly for my needs:))

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
If Target.Count > 1 Then GoTo exitHandler
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
  'do nothing
Else
    If Target.Value = "" Then GoTo exitHandler
    If Target.Offset(0, 1).Value = "" Then
      Target.Offset(0, 1).Value = Target.Value
    Else
      Target.Offset(0, 1).Value = _
        Target.Offset(0, 1).Value _
        & Chr(10) & Target.Value
    End If
  End If
exitHandler:
  Application.EnableEvents = True
End Sub

Anyway the problem that I am having at the moment is that I want to protect the worksheet so that no-one can change the questions or the answers that are on the sheet named list.

I am probably doing something stupid but I am having real trouble trying to protect the sheets. It doesn't like it and the macro will not work.

I would really appreciate it if someone could help me with protecting the questionnaire and the sheet named list.

Thanks in advance for reading this and thanks in advance if you are able to help:)
 
Sort by date Sort by votes
Can you be more explicit than "It doesn't like it and the macro will not work."? In what way does the macro not work? Does it not fire at all? Or does it crash? Or does it produce unexpected results? I'm assuming that you unprotected the results areas ( the adjacent cells ) of the sheet before switching protection on.
 
Upvote 0
You can add code to the macro to UNprotect the sheet at the beginning, then Reprotect it at the end.

Modify the bolded parts for the correct Password
Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
 
Me.Unprotect "Password Here"
If Target.Count > 1 Then GoTo exitHandler
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
  'do nothing
Else
    If Target.Value = "" Then GoTo exitHandler
    If Target.Offset(0, 1).Value = "" Then
      Target.Offset(0, 1).Value = Target.Value
    Else
      Target.Offset(0, 1).Value = _
        Target.Offset(0, 1).Value _
        & Chr(10) & Target.Value
    End If
  End If
exitHandler:
  Application.EnableEvents = True
Me.Protect "Password Here"
End Sub
 
Last edited:
Upvote 0
Re: How do you protect worksheets if you have a macro running?

Hi
I am sorry to bump this, I am really stuck.

I may not have explained correctly what I am looking to do, so I will try and reword it better.

As you can see in the above post I have a macro running on my spreadsheet, this macro allows the user to select several answers from a drop down list.

In column A I have Question 1, Question running down the column2 etc
In column B I have the actual Questions running down the column
In column C I have the drop down list with a choice of answers running down the column.
The macro allows the user to choose multiple answers for each question and place all the choice from that particulare question into column D.

I am trying to protect my sheets so that users can not change the Questions and the sheet that has the answers on which makes up the drop down list.

When I protect the sheets the macro stops working.

How can I protect the sheets but allow the macro to work?

Again sorry for bumping this, I am really stuck and have not been able to come up with a solution.

I hope someone can help:(

Thanks in advance for reading this and thanks in advance if you are able to help.:)

From a Desperate 1Roberta1:(
 
Upvote 0
Sorry as I was formulating my bump jonmo1 and GlennUK had replied.

To answer GlennUK question : It doesn't give me any error message at all.
When I protect the sheet the drop down box works fine however it will only allow you to select one answer.

jonmo1 I can see where you are coming from however, if the sheet is unprotected on opening then the user can still be mischeivous and change the questions etc:(
 
Upvote 0
You protect it by hand to begin with, save the file. Now it will be protected upon opening.
 
Upvote 0
I'm assuming that you unprotected the results areas ( the adjacent cells ) of the sheet before switching protection on.
Click to expand...

That was my problem, I can't believe I overlooked that :eeek:

Thank you both so much for helping

From an embarrassed:oops:
1Roberta1
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Data Validation Multi Select Code needs boosting
Replies
3
Views
296
rlv01
R
S
  • Question Question
Autofilter in VBA when criteria is has more words separated by a comma
Replies
0
Views
826
shafiey
S
D
  • Question Question
Make the code work in a protected sheet
Replies
7
Views
493
dmt32
D
D
  • Question Question
Locking of cells after not working
2
Replies
11
Views
270
Dazzatron
D
S
  • Solved
Worksheet Change Event
Replies
3
Views
660
sspatriots
S

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top