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

x
?
Solved

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

Posted on 2017-10-12
5
Medium Priority
?
19 Views
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.

Thanks
HideRows-As-Per-Dropdown-Selection-.xlsm
0
Comment
Question by:cesemj
[X]
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
5 Comments
 
LVL 28

Accepted Solution

by:
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
    Else
        Sheets("Checklist").Rows("4:6").EntireRow.Hidden = False
    End If
End If
Sh.Protect Password:=MyPassword
Application.ScreenUpdating = True
Sh.Activate
End Sub

Open in new window

HideRows-As-Per-Dropdown-Selection_.xlsm
0
 

Author Comment

by:cesemj
Thanks I will try and let you know.
0
 
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.
Run-Macro-On-Protected-Sheet.xlsm
0
 

Author Comment

by:cesemj
Thank you both.  Such tremendous value you insight is.  Thanks.
0
 
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!
0

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