Results 1 to 4 of 4

Macro with a Password Prompt routine

Threaded View

  1. #1
    Registered User
    Join Date
    09-19-2008
    Location
    maryland
    Posts
    23

    Macro with a Password Prompt routine

    I am trying to complete a toolbar that will lock and unlock all the sheets in a workbook. While I can set the password automatically I'd like to have the macro ask for the password instead of hardcoding it.

    Here is the code I currently have and works so long as I have already set the password in the workbook to match what is in the macro

    on Explicit
     
    Public Const ToolBarName As String = "Password Protect Worksheets"
     
     
    '===========================================
    Sub CreateMenubar()
     
        Dim iCtr As Long
     
        Dim MacNames As Variant
        Dim CapNamess As Variant
        Dim TipText As Variant
     
        Call RemoveMenubar
     
        MacNames = Array("Protect", _
                         "unprotect")
     
        CapNamess = Array("Lock Sheets", _
                          "Un-Lock Sheets")
     
        TipText = Array("Lock Worksheets", _
                        "Unlock Worksheets")
     
        With Application.CommandBars.Add
            .Name = ToolBarName
            .Left = 200
            .Top = 200
            .Protection = msoBarNoProtection
            .Visible = True
            .Position = msoBarFloating
     
            For iCtr = LBound(MacNames) To UBound(MacNames)
                With .Controls.Add(Type:=msoControlButton)
                    .OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
                    .Caption = CapNamess(iCtr)
                    .Style = msoButtonIconAndCaption
                    .FaceId = 71 + iCtr
                    .TooltipText = TipText(iCtr)
                End With
            Next iCtr
        End With
    End Sub
     
    '===========================================
    Sub Unprotect()
        Dim Wks As Worksheet
        For Each Wks In ActiveWorkbook.Worksheets
            Wks.Unprotect password:="password"
            Next Wks
        MsgBox "All of the WorkSheets are Now Unlocked"
    End Sub
     
    '===========================================
    Sub Protect()
        Dim Wks As Worksheet
        For Each Wks In ActiveWorkbook.Worksheets
            Wks.Protect password:="password"
        Next Wks
        MsgBox "All of the WorkSheets are now Locked"
    End Sub
    Last edited by VBA Noob; 02-13-2009 at 05:14 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1