Results 1 to 6 of 6

Thread: InputBox OK and Cancel Button Problem

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    18
    Rep Power
    0

    Exclamation InputBox OK and Cancel Button Problem

    I have this macro code that can protect and unprotect, It will show the input box and ask for a password. my problem is when I choose cancel, it still protect the file, the same thing with my unprotect code. here is my code below

    Protect Code
    Code:
    Sub ProtectAll()
          
        Dim wSheet          As Worksheet
         Dim Pwd             As String
          
        Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
         For Each wSheet In Worksheets
             wSheet.Protect Password:=Pwd
         Next wSheet
          
    End Sub
    UnProtect Code
    Code:
    Sub UnProtectAll()
          
        Dim wSheet          As Worksheet
         Dim Pwd             As String
          
        Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
         On Error Resume Next
         For Each wSheet In Worksheets
             wSheet.Unprotect Password:=Pwd
         Next wSheet
         If Err <> 0 Then
             MsgBox "You have entered an incorect password. All worksheets could not " & _
             "be unprotected.", vbCritical, "Incorect Password"
         End If
         On Error GoTo 0
          
    End Sub

  2. #2
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0
    Try this

    Code:
    Sub ProtectAll()
          
        Dim wSheet          As Worksheet
         Dim Pwd             As String
          
        Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
        If Pwd <> "" Then
         For Each wSheet In Worksheets
             wSheet.Protect Password:=Pwd
         Next wSheet
        End If
          
    End Sub
    
    
    
    Sub UnProtectAll()
          
        Dim wSheet          As Worksheet
         Dim Pwd             As String
          
        Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
         On Error Resume Next
         If Pwd <> "" Then
            For Each wSheet In Worksheets
                wSheet.Unprotect Password:=Pwd
            Next wSheet
            If Err <> 0 Then
                MsgBox "You have entered an incorect password. All worksheets could not " & _
                "be unprotected.", vbCritical, "Incorect Password"
            End If
         End If
         On Error GoTo 0
          
    End Sub

  3. #3
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Try this also:


    Code:
    Sub ProtectAll()
          
        Dim wSheet          As Worksheet
        Dim Pwd             As String
          
        Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
        If Pwd = vbNullString Then GoTo ExitPoint
        For Each wSheet In Worksheets
            wSheet.Protect Password:=Pwd
        Next wSheet
    
    
    ExitPoint:
        'Memory cleaning
        Set wSheet = Nothing
        Pwd = vbNullString
          
    End Sub
    
    
    Sub UnProtectAll()
          
        Dim wSheet          As Worksheet
        Dim Pwd             As String
          
        Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
        If Pwd = vbNullString Then GoTo ExitPoint
        On Error GoTo ExitPoint
        For Each wSheet In Worksheets
            wSheet.Unprotect Password:=Pwd
        Next wSheet
        
    ExitPoint:
        If Err <> 0 Then
            MsgBox "You have entered an incorect password. All worksheets could not " & _
            "be unprotected.", vbCritical, "Incorect Password"
            On Error GoTo -1: On Error GoTo 0: Err.Clear
        End If
        
        'Memory cleaning
        Set wSheet = Nothing
        Pwd = vbNullString
          
    End Sub

  4. #4
    Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    13
    This is cross post

    InputBox OK and Cancel Button Problem

    You got the answer here but did not answer
    Last edited by Ingolf; 05-26-2014 at 06:39 PM.

  5. #5
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Dear mackypogi,

    As you can see cross-posting isn't very much appreciated. Read following link regarding this issue.
    http://www.excelfox.com/forum/f25/me...-posters-1172/

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Although this thread is effectively closed, I thought I would use it to introduce those reading it to my latest mini-blog article here which shows how to be able to react to the Cancel button in an VBA InputBox...

    How To React To The Cancel Button in a VB (not Application) InputBox

Similar Threads

  1. Problem with line (row) in a macro
    By k0st4din in forum Excel Help
    Replies: 1
    Last Post: 03-19-2014, 07:59 AM
  2. Replies: 2
    Last Post: 11-02-2013, 04:17 PM
  3. Solve Block If Without End If Problem
    By jffryjsphbyn in forum Excel Help
    Replies: 3
    Last Post: 06-12-2013, 11:06 AM
  4. Replies: 8
    Last Post: 05-21-2013, 06:34 AM
  5. Print VBA cancel command not working
    By Tony in forum Excel Help
    Replies: 1
    Last Post: 02-07-2013, 07:09 PM

Posting Permissions

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