CODEWITHSUNDEEP
vbaexcel
Christian Emil Johansen
Christian Emil Johansen

Reputation: 191

Exit sub after cancelling an InputBox

I'm making an inputbox that allows the user to enter a password into it, and I'd like it to be able to contain both text and numbers. If possible, I'd like it to allow special characters as well.

What I'm currently for the InputBox is this:

Sub NewPass()
Dim Pass As String, OldPass As String, ws As Worksheet

OldPass = Ark2.Range("B6").Value
Pass = Application.InputBox("Enter new password", Type:=2)
If Not Pass = vbNullString Then

    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect Password:=OldPass
        ws.Protect Password:=Pass, UserInterfaceOnly:=True
    Next

    Ark2.Range("B6").Value = Kode

End If
End Sub

But for some reason it still enters the loop when hitting cancel or the X in the corner of the prompt.

I've tried my luck with VbNullString, 0 and "" but so far none of them works, and 0 ends with a Run-time Error '13': Type Mismatch in the if statement.

Upvotes: 0

Views: 2567

Answers (1)

Jarom
Jarom

Reputation: 1077

Instead of using pass = vbNullString as your condition you can use pass = FALSE When a user hits cancel it puts the value 'FALSE' in your variable.

Since you are using an application.inputbox when the user hits cancel it will return FALSE instead of being null. If you used just pass = inputbox(...) it would give you a null string.

When the user clicks 'ok' with the application.inputbox; leaving the field blank, the output will be a null string.

One of the big advantages of the application.inputbox is that it can put restrictions on what the user can type in. If you do not want any restrictions, the inputbox function will be better. It returns a null when the users clicks 'ok' without typing anything in and when the user hits 'cancel'

 Sub NewPass()
 Dim Pass As String, OldPass As String, ws As Worksheet

 OldPass = Ark2.Range("B6").Value
 Pass = InputBox("Enter new password")
 If Not Pass = VBNullSTring Then

    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect Password:=OldPass
        ws.Protect Password:=Pass, UserInterfaceOnly:=True
    Next

Ark2.Range(WB6").Value = Kode

End If
End Sub

Upvotes: 2

Related Questions