Using Escape on an Input Box?

[Ste_Moore01]

Hi,

I've got some code to make an input box

    Dim go_on1 As Boolean, mycount1 As String
    go_on1 = False
    Do While go_on1 = False
    Dim Company_Name As String
    Company_Name = InputBox("Please type in the Company Name")
    If Company_Name = "" Then
    Call Beeper1
    Beep
    MsgBox "You did not enter a company name!  Please try again!"
    Else: go_on1 = True
    Sheets("DETAILS ENTRY").[c1] = Company_Name
    End If
    Loop

This input box will only let you proceed if you enter some data.

Is there a way of making it so that if you press the Escape key it cancels the input box and goes to the worksheet named "DETAILS ENTRY"?

So, if you press cancel or ok with no data in the box it says "You did not enter a company name! Please try again!" and goes back to the input box, but if you press Escape it get's rid of the input box and goes to the "DETAILS ENTRY" worksheet?

Any help would be appreciated.

Thanks!

 

[BrianB]

I think this will work Ok. Depends if the Escape key is still down when the line to check it runs - so I wouldn't put anything else between the lines.

Declare Function GetKeyState Lib "user32" _
    (ByVal nVirtKey As Long) As Integer
'-----------------------------------------------
Const MePressed = &H1000
'==============================================================
Sub test()
    Dim Company_Name As String
    CompanyName = ""
    While Company_Name = ""
        Company_Name = InputBox("Please type in the Company Name")
        '- check for Escape key
        If GetKeyState(vbKeyEscape) And MePressed Then
            Worksheets("DETAILS ENTRY").Activate
            Exit Sub
        End If
        '- check Company name
        If Company_Name = "" Then
            MsgBox "You did not enter a company name!  Please try again!"
        End If
    Wend
End Sub

 

[Ste_Moore01]

Thanks for the reply but it doesn't work properly for me.

It's acting the exact same as my own code except you haven't put a beep in as I have.

What I'm after is instead of typing in anything in the box just pressing Escape should exit the sub and it doesn't.

 

[Ste_Moore01]

Sorry, it does actually work!

It just wouldn't work in the "ThisWorkbook" section of my sheet.

I'm going to use a "call" function to see if that works.

Thanks a lot Brian

 

[Ste_Moore01]

I've just found that if I made a seperate macro with your code in and used to call to run it, it would still continue with the rest of macro.

for example

If I put your code in a macro called companyname and I wanted it to run when the workbook opened I would use a macro like

Private Sub Workbook_Open()
    Dim Ans As Variant
    Ans = MsgBox("Do you wish to create a pricepack now?", vbYesNo)
    If Ans <> vbYes Then
    Sheets("DETAILS ENTRY").Select
    Exit Sub
    Else
    
    Call CompanyName

    Dim Town As String
    Town = InputBox("Please type in the Company's Town/City if it is needed.", "Area")
    Sheets("DETAILS ENTRY").[c4] = Town
    
    Dim Your_Name As String
    Your_Name = InputBox("Your name is automatically put on the pricepack, if you wish to change the name then please input it here.", "Your Name")
    If Your_Name = "" Then
    Sheets("USER DETAILS").[B1] = name
    Sheets("DETAILS ENTRY").[c8] = nameform
    Else
    Sheets("DETAILS ENTRY").[c8] = Your_Name
    End If
End Sub

So when the input box for Company_Name comes up, if I press Escape then the input box for Your_Name will come up.

Does anyone have any idea how to get around this?