asked on

Using VBA to Hide and Lock Cells with a password

Hello,

I opened another question here:

https://www.experts-exchange.com/questions/28531881/Excel-How-to-hide-cells.html

Which outlines how to lock and hide cells with a password. This works great and is what I need. Is there anyway to automate this with VBA?

I am thinking I can unlock cells using this:

Selection.Locked = False
Selection.FormulaHidden = False

and then lock selected cells and hide them with:

Selection.Locked = True
Selection.FormulaHidden = False
Selection.EntireRow.Hidden = True

and then protect the worksheet:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

I would like to progress the above to essentially unlock the entire worksheet, then select the rows where there is the word Internal in the cell and then lock and hide them. Finally I would like to lock it all and set the password in VBA (I am not sure if I can do the password bit in VBA?)

The name of the worksheet is 'My Items'.

Any help in joining up the above would be greatly appreciated ;)

Regards,

GISVPN

Rgonzo1971

Hi,

pls try

Sub macro2()
Cells.Locked = False
Cells.FormulaHidden = False

Selection.Locked = True
Selection.EntireRow.Hidden = True

Sheets("my items").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="myPW"

End Sub

Regards

ASKER CERTIFIED SOLUTION

James Elliott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gisvpn

ASKER

James - thank you again for your input - that's a great automation of the process.

I assume I can build a form to set a custom password each time if wanted?

James Elliott

Option Explicit

Sub EE()

Dim ws As Worksheet
Dim rng As Range
Dim x As Long
Dim sPwd as String

Set ws = ThisWorkbook.Sheets("My Items")
Set rng = ws.Cells

rng.Locked = False

For x = ws.Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1

    Set rng = ws.Range("A" & x)

    If rng = "Internal" Then
        rng.EntireRow.Locked = True
        rng.EntireRow.Hidden = True
    End If
    
Next x

sPwd = InputBox("Please enter password", ThisWorkbook.Name)

    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=sPwd
    ws.EnableSelection = xlUnlockedCells

End Sub

Open in new window