Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    There's No Escape

    Is there a way to disable the "escape" key? I have a macro that copies a sheet in the workbook and asks for a name for the tab. If the user hits Escape rather that entering text for the Input Box it cause the macro to give a run time error. The code follows:

    Dim Message, Title, MyValue
    Message = "Enter A Tab Name"
    Title = "Tab Name?"
    MyValue = InputBox(Message, Title)
    ScreenUpdating = False
    Sheets("Summary").Select
    Sheets("Summary").Copy Before:=Sheets(3)
    Sheets("Summary (2)").Select
    Sheets("Summary (2)").Name = MyValue
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: There's No Escape

    You are getting the error not because they press the escape button but because the value returned by the inputbox is null. Just test for a null value in the inputbox return and take appropiate action.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: There's No Escape

    Hi Shelly,

    I tried to run your code and nothing is wrong with the inputbox when pressing escape. The error is due to the last line in your code. You want to name a sheet with the contents of MyValue, which is "Escape" at that moment, and this does not work of course. You should add some code that does not allow MyValue to be empty, or which does not allow to rename the sheet if MyValue is empty.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: There's No Escape

    Sorry Sherry for this Chinese slip of the tongue.

    Just want to add that there is also the Excel inputbox, which can be used very easy as a method of an object:

    object.inputbox(prompt, title, default, left, top, helpfile, context, type)

    e.g. MyValue = Application.InputBox(Prompt:=Message, Title:=Title, Type:=1)

    Interesting here is the Type property, which allows you to select what type of data you want the user to enter. Type:=0 only allows the user to enter a formula, 1 = numeric data, 2 = text, 4 = boolean, 8 = range, 16 = an error value like #N/A and 64 = array. You can also allow multiple data types by combining codes; e.g. 1+2 is a numeric value or text

    Pay attention to the following: ScreenUpdating is a method of the Application object. Therefore, it is good programming practice (GPP) to write Application.ScreenUpdating = false
    In some cases, you will experience trouble if you don't put the application object in front of the method.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: There's No Escape

    Are you saying that if I use a Type of 2 they will only be able to enter a tab name that is data (alpha, not numeric)?

    [i.e. MyValue = Application.InputBox(Prompt:=Message, Title:=Title, Type:=2)]

    I did fix the Escape issue by checking for a null MyValue and handling.

    I also added the application object in front of ScreenUpdating = False and it works much better.

    Thanks.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: There's No Escape

    I fixed it to handle a null MyValue and it works great!

    Thanks a lot.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: There's No Escape

    Sherry,
    I don't know if what you posted was your entire code, but just in case it was, I'd recommend specifically adding Application.ScreenUpdating = True to the end. According to the MSDN library, Excel 97 and later versions do not automatically reset this value when the code has executed (I'm not actually convinced that's true if the code completes properly, but if there's an error that interrupts it you might have problems)
    Just a thought.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: There's No Escape

    It was just a portion of the code and yes, I do have it at the end.

    Thanks for the heads-up though. [img]/w3timages/icons/smile.gif[/img]
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: There's No Escape

    Sherry,

    FYI, you can disable the escape key by Application.EnableCancelKey = xlDisabled

    Also, Application.Interactive = False might be of interest to you.

    Make sure to read the VBA help file with these two though. If you have some runaway code, you might need the escape key yourself. Also, I believe that Excel does not automatically restore the Interactive property to True, so be very careful.

    HTH,
    Mike

Posting Permissions

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