Add PassCode requirement for one particular Macro in a Spreadsheet

[almadolan]

Hello, hivemind!

It's been awhile and I need some guidance on using a little VBA to add a PassCode for a spreadhseet. Only certain people are allowed to see certain columns to update data, but many people need the report. I have the following 2 modules. I'm supposed to put one into the other, but i keep getting errors. Can someone help me combine them for a working macro? Thanks!

I have to put the "View" one into the "Secure" one.

Sub SecureMacro()
Dim userInput As String
Dim correctPassword As String

' Set your security code here
correctPassword = "345"

' Prompt user for password
userInput = InputBox("Enter the security code to run this macro:", "Macro Security Check")

' Check if user canceled
If StrPtr(userInput) = 0 Then
MsgBox "Macro canceled.", vbInformation
Exit Sub
End If

' Validate password
If userInput <> correctPassword Then
MsgBox "Incorrect security code. Access denied.", vbCritical
Exit Sub
End If

' ===== Your macro code starts here =====
MsgBox "Security check passed. Running macro...", vbInformation

' Example macro action:
Range("A1").Value = "Macro executed successfully!"

' ===== Your macro code ends here =====
End Sub
________________________________________

Sub INTEGITUS_HQ_FORMAT_VIEW()
'
' INTEGITUS_HQ_FORMAT_VIEW Macro
' Allows authorized viewer to see hidden data with correct PassCode.
'

'
Columns("I:R").Select
Range("I2").Activate
Selection.EntireColumn.Hidden = False
Range("A11").Select
End Sub

 

[shamimarita]

Hello Almadolam,

You can place the column-unhiding commands directly inside the password-protected macro. Try the following code:

Sub INTEGITUS_HQ_FORMAT_VIEW()

    Dim userInput As String
    Const correctPassword As String = "345"

    userInput = InputBox( _
        "Enter the security code to view the hidden columns:", _
        "Security Check")

    'Exit if the user clicks Cancel or leaves it blank
    If userInput = "" Then
        MsgBox "Operation canceled.", vbInformation
        Exit Sub
    End If

    'Check the password
    If userInput <> correctPassword Then
        MsgBox "Incorrect security code. Access denied.", vbCritical
        Exit Sub
    End If

    'Unhide columns I:R after successful validation
    With ActiveSheet
        .Columns("I:R").Hidden = False
        .Range("A1").Select
    End With

    MsgBox "Access granted. Columns I:R are now visible.", vbInformation

End Sub

You do not need to keep the two macros separate. Assign this combined macro to your button instead of the original View macro.

Please note that a password written directly in VBA provides only basic protection because someone with access to the VBA editor may be able to view it. You can also lock the VBA project through

• VBA Editor → Tools → VBAProject Properties → Protection

Although this should not be treated as strong security for highly sensitive information.