Using Escape on an Input Box?

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
Hi,

I've got some code to make an input box
Code:
    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! :LOL:
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
Code:
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?
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top