How can I insert a password to run a macro

Question

I'd like to require a password before a macro will run so that only certain users of a spreadsheet have the macro-ability

Answer 1

Dear Gretchen,

You can first right-click on VBAProject>VBAProject Properties>Protection>insert the password you want to enable for users to run the VBA code and check Lock protect for viewing.

Then you can add the code below in the macro to pop up a window for users to run the macro.

Dim password As Variant

password = Application.InputBox("Enter Password", "Password Protected")

For example, you want to highlight red on B3:C9 and protect the macro with a password. You can type the following example code.

Sub Highlight_Duplicate_Values()

Dim password As Variant

password = Application.InputBox("Enter Password", "Password Protected")

Dim ws As Worksheet

Dim ColorRng As Range

Dim ColorCell As Range

Set ws = Worksheets("Sheet1")

Set ColorRng = ws.Range("B3:C9")

For Each ColorCell In ColorRng

If WorksheetFunction.CountIf(ColorRng, ColorCell.Value) > 1 Then

ColorCell.Interior.Color = RGB(255, 0, 0)

Else

ColorCell.Interior.ColorIndex = xlNone

End If

Next

End Sub

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator