Application InputBox Cancel/Wrong text entered problems

[melewie]

Hi All,

I am having some problems with application input box's, I'm trying to use them as a way to enter a password before the user is allowed access to a sheet, the code below is what I have, this works if the user enters the right password and if the user hits the cancel button.........however if the user enters the wrong password I get a Runtime 13 type mismatch on this line

If pword = False Then

which I don't understand . I must be missing something........(like a basic understanding of VBA!!)

Private Sub CommandButton1_Click()
Dim pword As String


pword = Application.InputBox("Enter password to view safety sheet", "PASSWORD REQUIRED")


If pword = "Safety" Then


    Worksheets("safety").Visible = True
    Worksheets("safety").Select
    Worksheets("Navigator").Visible = xlVeryHidden


Else
    If pword = False Then Exit Sub
    MsgBox "Password incorrect please try again"
End If


End Sub

Any help is hugely appreciated (I've got a right headache )

Cheers

Lewie

 

[MickG]

Try this:-
NB:- To have:- Pword = false , pWord would need to be a "Boolean" variable, That is, the Result would need to be "True/False"

Dim pword As String
pword = Application.InputBox("Enter password to view safety sheet", "PASSWORD REQUIRED")
If pword = "Safety" Then
    Worksheets("safety").Visible = True
    Worksheets("safety").Select
    Worksheets("Navigator").Visible = xlVeryHidden
ElseIf Not pword = "Safety" Then
    MsgBox "Password incorrect please try again"
    Exit Sub
End If

 

[melewie]

Cheers for the reply Mick, Already tried that exact thing, but when the user selects cancel (cancel returns False(or so I believe)) the msgbox saying wrong password is displayed, I want to avoid this and have cancel exit the sub?? which works now, but if user enters wrong password and clicks 'OK' it errors out

I have tried setting as Boolean but this bugs out on

[COLOR=#333333]pword = Application.InputBox("Enter password to view safety sheet", "PASSWORD REQUIRED")[/COLOR]

 

[MickG]

You could change the "pword" Variable Type to "Variant" as below, this would cover your String or Boolean
Something like:-

Dim pword As Variant
pword = Application.InputBox("Enter password to view safety sheet", "PASSWORD REQUIRED")
If pword = "Safety" Then
    Worksheets("safety").Visible = True
    Worksheets("safety").Select
    Worksheets("Navigator").Visible = xlVeryHidden
ElseIf pword = False Then
    Exit Sub
ElseIf Not pword = "Safety" Then
     MsgBox "Password incorrect please try again"
     Exit Sub
End If

 

[melewie]

Mick I love you!!!! completely forgot about variant. all working now cheers!!

 

[MickG]

You're welcome

 

[supergrass]

Microsoft still don't document how to handle errors with Application.Inputbox

If your Sub is just taking a single input, you could try something like this:

Sub getRangeInput()
Dim defaultRange as Range, actualRange as Range

Set defaultRange = ActiveSheet.Range("$A$1:$A100")

On Error GoTo ErrHandler
Set actualRange = Application.InputBox(Prompt:="Enter the Range", Title:="Range Gizmotron", Default:=defaultRange.Address,  _
                                      Type:=8)

'do your stuff on the range here
Exit Sub

ErrHandler:
On error GoTo 0
End Sub