Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good evening

    I am just about to start the process of rebuilding a workbook because of some sort of cell reference problem. Before I start I would like to ask a question that may seem simple to experienced users so please excuse my ignorance.

    I have the same sub running in 9 different worksheets, would it be more effecient to make it a macro (not quite sure how yet) and then use some sort of reference in each of the worksheets that calls the macro so that it is only stored once.

    Thanks
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    What Triggers the Sub to Run?

    If it is worksheet event, then you can possibly look at one of the workbook events instead

    many Sheet Events have a workbook equivalent that passes both the sheet name and other parameters.
    Andrew

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    You can put the macro in a personal.xls module and run it thus from whatever event or button you want it triggered in your workbooks:

    Run "Personal.xls!MyCode"

    or if it needs arguments:

    Run "Personal.xls!MyCode", <parameter1>, <parameter2>, <parameter3>, ... etc
    -John ... I float in liquid gardens
    UTC -7DS

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On the basis that it's the same workbook as you mentioned here, can you give us some links to your previous threads so we can get a better feel for what the Worksheet Code is?

    It may be the case that previous advice avoided combining the code - or it may just be that JohnBF's proposal is smething that simply got overlooked.
    Gre

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Malcolm Wagner View Post
    On the basis that it's the same workbook as you mentioned here
    If it's a distributed workbook, having all its users add code to personal.xls may not be practical.
    -John ... I float in liquid gardens
    UTC -7DS

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Malcolm Wagner View Post
    On the basis that it's the same workbook as you mentioned here, can you give us some links to your previous threads so we can get a better feel for what the Worksheet Code is?

    It may be the case that previous advice avoided combining the code - or it may just be that JohnBF's proposal is smething that simply got overlooked.
    Hi Malcolm

    I am trying to implement Johns Suggestion but I am quite slow at learning new things, it occurs to me though that a personal Macro may bot be relevant as I share the workbook with 2 others. I am currently searching for an article that was posted in the lounge, I think by Don? on personal.xls to help me along. And yes you are right it is linked to the following post and I do realise that my workbook has become a monster over 3 years so I am trying to tidy it up at the same time as rebuilding it.

    Thanks for the input
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by stevehocking View Post
    I share the workbook with 2 others.
    Then it's time for you to learn about personal.xls! Are you the only person who runs the macros? If so, personal.xls is your best bet. Even if the other two do, with only three "installations" to maintain, personal.xls may be the easiest solution. Search this Forum for creating and using personal.xls. These may also be helpful http://office.microsoft.com/en-us/ex...872961033.aspx and http://www.rondebruin.nl/personal.htm.
    -John ... I float in liquid gardens
    UTC -7DS

  8. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    A Public Sub in a Module can be called from any worksheet. This is also where custom worksheet functions go.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Posting Permissions

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