1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA: Prompt for workbook?

Discussion in 'Business Applications' started by theseus75, Jun 8, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. theseus75

    theseus75 Thread Starter

    Joined:
    Jan 7, 2005
    Messages:
    275
    I write a few macros for other users, and right now I just request that they rename the workbooks each month (or however often they run it) to the same names and then offer a reminder at kick-off. Something like:

    Code:
         If MsgBox("Are the workbooks saved as 'Workbook_1.xlsx' and 'Workbook_2.xlsx', and are they both open?", vbYesNo) = vbNo Then
        End
        End If
        Windows("Workbook_1.xlsx").Activate
        Range("a3").Select
        Etc, Etc
    Otherwise, I don't know how to make changes or run actions based on workbooks whose names might change. But is there a better way? I've had some people - those least familiar with automation - forget to rename, ignore the message, and then call with problems. Perhaps there's a way, instead, that asks them to name as variables whatever the sheets are called, though this might require some hand-holding, such as a list of what workbooks are open? I don't know. I'm open to suggestions - just putting it out there.

    Thanks!

    Windows XP
    Excel 2007
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    Hi there,
    Is the fact that the workbooks are not renamed correctly the problem or do you just want something so the user or users can select the workbooks and open them?
    Are all the workbooks and the 'master' workbook in the same folder or can people 'do whatever they want'?
     
  3. theseus75

    theseus75 Thread Starter

    Joined:
    Jan 7, 2005
    Messages:
    275
    The issue is that users forget to rename them and then ignore the message making sure they've renamed them. Or they ignore the naming convention, and rename it "workbook1.xlsx" instead of "workbook_1.xlsx" and then wonder why the macro doesn't work.

    I'd say that the workbooks could be in the same folder, but aren't necessarily. They at least know to have them open. I'm just hoping that no matter what the workbooks are called, they'll be able to use them, either by setting them as variables via some easy windows input or otherwise. Because when they get them from our central data processing department, they have names that change each month, such as "falls_feb2011.xlsx" which will - of course - change the next month.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    You could write a macro around it, the open the workbook they need but the saving routine could be via a macro and the the name will always be conform your policy.
    I don't know if you can follow my rain of thought but maybe this would help.
     
  5. theseus75

    theseus75 Thread Starter

    Joined:
    Jan 7, 2005
    Messages:
    275
    @Keebellah - Maybe something like that. The source is an automated rpt created by query from our central database and delivered via email in Excel. Luckily, it always looks the same, but the naming convention uses the month. However, if I could educate the user to run a renaming macro (which also saved the book in a specific place) then it would remove a bit of human error potential. I might go that route. Ultimately, I hope to find a way to have some graphical interface that displays the open workbooks and has the user tag each (or three), with the proper naming convention.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    Okay, there's some code that I used in another post, I'll put it togerther and post it.
    I'm quite busy this weekend but I'll get back to you
     
  7. theseus75

    theseus75 Thread Starter

    Joined:
    Jan 7, 2005
    Messages:
    275
    @Keebellah - Sounds great. Thank you!
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    @theseus - Excuse the delay, but I have been busy with a large issue, will put the code together and post it asap.
     
  9. theseus75

    theseus75 Thread Starter

    Joined:
    Jan 7, 2005
    Messages:
    275
    @Keebellah - No prob. Sort of one of those issues that would be great to figure out, but not terribly time sensitive. Thanks.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1001214

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice