[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


VBA Code does not function when the worksheets are password protected.

Posted on 2017-10-12
Medium Priority
Last Modified: 2017-10-12
Hi and thanks in advance:

The spreadsheet hides-rows-on-one-spreadsheet-based-on-data-selected on the Source worksheet cell B4 .   When I protected the code stopped working.  Please suggest a way to allow the code to work and still protect the worksheets.

Question by:cesemj
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 28

Accepted Solution

Shums earned 1000 total points
Try below, change your password accordingly in vba code as well in sheet protection.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sh As Worksheet
Dim MyPassword As String
Set Sh = Sheets("Checklist")
MyPassword = "test123"
Application.ScreenUpdating = False
Sh.Unprotect Password:=MyPassword

If Target.Address = "$B$4" Then
    If Range("B4") = "Moderate" Or Range("B4") = "Low" Then
        Sheets("Checklist").Rows("4:6").EntireRow.Hidden = True
        Sheets("Checklist").Rows("4:6").EntireRow.Hidden = False
    End If
End If
Sh.Protect Password:=MyPassword
Application.ScreenUpdating = True
End Sub

Open in new window


Author Comment

Thanks I will try and let you know.
LVL 22

Assisted Solution

by:Roy Cox
Roy Cox earned 1000 total points
The code can be much simpler

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Const PW As String = "secret"
''/// assumes password is secret, change to whatever you want
    With Sheets("Checklist")
        If .ProtectContents Then .Unprotect PW
        If Target.Address = "$B$4" Then
            .Rows("4:6").EntireRow.Hidden = Range("B4") = "Moderate" Or Range("B4") = "Low"
        End If
        .Protect PW
    End With
End Sub

Open in new window

Or use ProtectWithUserInterFace only which allows macros to run on protected sheets, see the attached example.

Author Comment

Thank you both.  Such tremendous value you insight is.  Thanks.
LVL 22

Expert Comment

by:Roy Cox
Sorry, but how can you award assist when I offered two more efficient methods. Maybe you consider unnecessary lines of code an advantage!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Make the most of your online learning experience.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question