Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exiting a macro if wrong worksheet (Excel 2003)

    I need code that will check whether the active worksheet is "Invoice Form" and if it is, let the macro continue but if it isn't, terminate the macro without requiring the macro be reset. I tried searching the forum, but I suspect that my search queries were worded incorrectly as I couldn't find anything. I've tried writing it myself, but I always get an error. Any help is appreciated. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    Try

    If Not ActiveSheet.Name = "Invoice Form" Then Exit Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    Does this do what you want:

    <code>
    If ActiveWorksheet.Name <> "Invoice Form" Then Exit Sub
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Thanks, Hans. Why such simple things seem to escape me, I do not know. I tried all kinds of variations on If Worksheets("Invoice Form") including If Worksheets("Invoice Form").ActiveSheet = True, but not once thought about your solution. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> Your solution works almost perfectly. What happens now is that instead of the macro completely stopping (and I put your line of code as the very first line in the macro), the macro opens one of the Invoice Form files and then quits. In case I'm not being clear, here is how the macro works.

    The macro appears on a toolbar in every Excel document (that's because I didn't want to create a special toolbar just for the one macro that would only load with the invoice forms), including the 3 invoice form files (they are read-only .xls files) and the primary P&L workbook. The reason I wanted the code was so that if I accidentally ran the macro while in the P&L workbook or in another workbook in which it has no application, I wouldn't have to open the macro to reset it.

    With your line of code added at the macro beginning and only the P&L workbook open, running the macrot opens a blank invoice form before the macro quits (leaving the P&L workbook as the active workbook, but both files open). Is there a way to prevent it from opening the invoice file? Thanks.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Thanks, Legare. It, too, does the trick. Why I never seem to grasp these simple solutions escapse me. I'm always looking for something more complex. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> However, your solution has the same result as Hans' solution, that is, the macro opens an invoice form file before stopping. Please see my response to Hans for further explanation.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    That line of code (either mine or Hans), will stop the execution of the macro it is contained in immediately when it is executed if "Invoice Form" is not the active worksheet. Therefore, if you are putting it as the first line in the macro, then the macro will not do anything if "Invoice Form" is not the active worksheet. Therefore, either "Invoice Form" is not the active worksheet (or that is not the correct name), or some other macro is doing what you say (possibly some auto-macro or event routine). I can'e tell without seeing the workbook.
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    As Legare wrote, either the name of the active sheet is different from "Invoice Form" (perhaps an extra space?), or the blank invoice form is opened by something else, not by the macro containing the line suggested by us.

  8. #8
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    I have attached a copy of the form that is opened. Does this help? This form is not open when I run the macro. The first lines of the macro are these:

    Sub InvoiceNumber()
    If Not ActiveSheet.Name = "Invoice Form" Then Exit Sub

    If something else is happening, I can't imagine what it could be.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    Nothing in the workbook you have attached will cause something to open if the worksheet is not named "Invoice Form". Check your 'real' workbook for code in any of the worksheet modules and in ThisWorkbook.
    If you can't find it, we'll have to see the 'real' workbook (with data removed).

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    Your workbook only contains one worksheet which is named "Invoice Form", so if this is the active workbook that is going to be the ActiveWorksheet and the macro is going to run. However, I do not see any code in the InvoiceNumber macro (where the line of code is located) that would open any other files or create any other worksheets. So, something must be happening somewhere else.

    I do see a couple of problems in this workboo.

    1- The macro named InvoiceNumber is in a module named InvoiceNumber. It is not a good idea to have a macro and a module with the same name as VBA can get confused.

    2- The macro contains an On Error Resume Next statement to prevent the macro from failing if the Invoice number file is not found. Following opening of the file, the code checks for error numbers 70 and 53. However, if error 70 occurs (file not found), it does not exit from the macro, it continues trying to open the file for 100 tries.

    3- The macro does not do anything if any other error occurs. Specifically if error 71 occurs (which will happen if the directory where the file is supposed to be located does not exist), the error is just ignored.

    4- The On Error Resume Next is not reset after the attempt to open the file is completed. Therefore if an error occurs reading or writing to the file, it is just ignored and the user is not notified.

    The statement Hans gave you (the one you put into the macro) does work. If I run the macro with the Invoice Form worksheet as the active sheet, the macro executes. If I add a sheet to the workbook and run the macro with that sheet active, the macro exits after the first statement.
    Legare Coleman

  11. #11
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    I can rename the macro without any problem and I can omit the troublesome code. Thanks for the suggestions.

    The problem isn't if the active sheet is a second sheet in the same workbook; the problem occurs when workbook A, which does not have a sheet named Invoice Form, is open. I've attached a copy of the P&L workbook to my response to Hans.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    If you put a macro in a specific workbook, then assign that macro to a toolbar button that is visible in all workbooks, Excel will open that workbook when you click the toolbar button: it needs to do so in order to load the macro.
    If you want the toolbar button to be available in all workbooks, you should put the code in your Personal.xls workbook or in an add-in, then reassign it to the toolbar button. See Legare Coleman's Personal.xls Tutorial.

  13. #13
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    Hans, That's the problem. I can fix that. I didn't realize that it would have to open the workbook to run. Your message came as I was trying to reduce the size of the P&L workbook to upload it because I couldn't find anything in the code. But now I see what I need to do. Thanks to both you and Legare.

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    If you have the code in the InvoiceForm workbook, and attach the macro to a toolbar button, then when you click on that button Excel will open the InvoiceForm workbook to be able to run the macro. That is probably why you are getting the InvoiceForm workbook opened. If you want the macro available all the time from a toolbar button, then you should put the macro into your Personal.xls file. See my Personal.xls Tutorial .
    Legare Coleman

  15. #15
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exiting a macro if wrong worksheet (Excel 2003)

    Thanks, again, Legare.

    Originally what I wanted was 1 macro that would only work with my 3 invoice forms (Standard; Project Fee; Non-US) with a single button that only appeared when an invoice form was active. I couldn't do this, it is beyond my skill level. Then what I wanted was each of my 3 invoice forms to have their own macro and their own button that only appeared in the appropriate form. That is, the 1 macro for the Standard form that only worked with the Standard form with a button that appeared only if that form was active. Although I was able, once you and Hans gave me the line of code at the beginning of this thread, to modify the macro so that it would only run with the specific workbook, I was unable to figure out how to assign it to a button that appeared only in the specific workbook. So what I have done is to make the 3 individual macros and added all of them to a custom toolbar that appears in all of my Excel documents. I put the macros in the Personal.xls as you and Hans suggested. Although not what I originally wanted, this solution works and works well. I couldn't have done it without your and Hans help. I much appreciate it. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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