+ Reply to Thread
Results 1 to 6 of 6

How do I change a Macro to use ThisWorkbook properly?

  1. #1
    Registered User
    Join Date
    07-08-2014
    Location
    Santa Cruz, CA
    MS-Off Ver
    2010
    Posts
    9

    Question How do I change a Macro to use ThisWorkbook properly?

    Hi,

    I created the following macro by recording the macro and going through the steps manually, however I need to make some changes and can't seem to accomplish what I'm trying to do.

    The Macro opens a master inventory file, creates a new line, and then links certain column cells in the inventory to corresponding places within the original form (the macro is executed from the original form once it's completed).

    Problem is, the macro is written using the form "template" so whenever I save the template as the name of the unique item, it won't update the macro language as well.

    What I'm trying to accomplish is when someone opens the template, the save immediately with a different file name, and once the form is completed and the macro is run, it's creating the new line in the inventory pointing to that specific file.

    I thought somehow utilizing ThisWorkbook within the macro instead of explicitly using something like "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3" would accomplish what I'm trying to do.

    Any help would be greatly appreciated. Here's the code...

    Please Login or Register  to view this content.

    Thanks
    Last edited by jfleisher; 07-09-2014 at 04:22 PM.

  2. #2
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: How do I change a Macro to use ThisWorkbook properly?

    Please Login or Register  to view this content.
    Now wb will always refer to that specific workbook within your code. you can us it in many ways e.g. wb.close,

    You can do the same with a specific sheet...
    Please Login or Register  to view this content.
    This gives you the ability to refer to exactly that sheet. Examples of use include:
    Please Login or Register  to view this content.
    Hopefully this helps you get started.
    If your questions has been answered to your satisfaction please don't forget to do the following:

    Add Reputation ... and ... Mark Thread as Solved

    Thanks,

    Ma 10:8b Freely you have received; freely give.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: How do I change a Macro to use ThisWorkbook properly?

    Change this...
    "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3"

    To this...
    "='[" & ThisWorkbook.Name & "]FUND SET_UP PG_1'!R2C3"
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    07-08-2014
    Location
    Santa Cruz, CA
    MS-Off Ver
    2010
    Posts
    9

    Re: How do I change a Macro to use ThisWorkbook properly?

    AlphaFrog,

    That is precisely what I was looking for, works great. Thanks very much. Before I Add rep and mark as solved. There is a couple of minor additional questions...

    When the macro runs, it stops after opening the inventory workbook and asks whether to update links, is there a simple action I can add to the macro to automatically update so the pop up box doesn't occur?

    Lastly, I want to put a feature in the macro that, before creating a new line and linking the cells in the inventory workbook, checks the inventory workbook to see if the value in "='[" & ThisWorkbook.Name & "]FUND SET_UP PG_1'!R2C3" already exists and if so, skips to the end of the macro (avoids duplicate entries).

    If you'd like me to resolve this thread and add rep first, then put these ques in a new thread I'd be happy to. Just thought I'd ask since you hit my main prob spot on.

    Thanks a ton.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: How do I change a Macro to use ThisWorkbook properly?

    Please Login or Register  to view this content.
    TIP: in the VBA editor, highlight the Open keyword in the code and press F1 for help\syntax\example code.

    The second part is too vague; what exists where? How is it tested for?

  6. #6
    Registered User
    Join Date
    07-08-2014
    Location
    Santa Cruz, CA
    MS-Off Ver
    2010
    Posts
    9

    Re: How do I change a Macro to use ThisWorkbook properly?

    After the open command, using something like an if and match formula, ie.

    =if(match('[" & ThisWorkbook.Name & "]FUND SET_UP PG_1'!R2C3" ,N:\Accounting\userdata\EXTRA\EXSHARE\INVENTORY\New Inventory project 2014\johnS Inventory test.xls!A1:A99999,0)>0, skip to the end of macro to save and close inventory, goto next step in macro

    I realize my code is a little goofy, but should be most specific.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to get macro to run only in ThisWorkbook
    By scottatbuckman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2014, 12:27 PM
  2. [SOLVED] How to stop Private sub Sheet change (located at Thisworkbook) working in worksheet 3
    By alexnkc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2013, 09:34 PM
  3. Deleting a macro in thisworkbook
    By polska2180 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2007, 03:05 PM
  4. [SOLVED] Spin Button control change capture in ThisWorkbook Module
    By STEVE BELL in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-12-2005, 05:05 PM
  5. Module1 vs Thisworkbook for Macro
    By Craigm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-28-2005, 08:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1