+ Reply to Thread
Results 1 to 8 of 8

Join 2 macros

  1. #1
    Forum Contributor
    Join Date
    10-24-2008
    Location
    England
    Posts
    146

    Join 2 macros

    How could i change this vba code

    Please Login or Register  to view this content.
    To add this vba before the final shDestination.Select

    Please Login or Register  to view this content.
    I have added it but it does not seem to work
    My main aim is so when a "Y" causes this vba to run, before shDestination.Select is executed i would like to run the code above.

    Hope you understand this

    Many Thanks
    Last edited by Directlinq; 06-16-2009 at 08:25 AM.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Join 2 macros

    Try this - depending on which sheet the code is in, you may need to disable events as you are changing sheet values:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Join 2 macros

    Quote Originally Posted by romperstomper View Post
    Try this - depending on which sheet the code is in, you may need to disable events as you are changing sheet values:
    Actually, if you are inserting values ONTO a sheet with a worksheet_change macro active, I would consider the "disableevents" as a REQUIRED first step due to the fact that even if you're changing values in a column that doesn't makes the "intersect" test, the macro is still firing off and checking for every value entered. Eek! Definitely turn it off at the beginning, always, then back on when you're done.

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 06-16-2009 at 08:47 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Contributor
    Join Date
    10-24-2008
    Location
    England
    Posts
    146

    Re: Join 2 macros

    Works a charm thank you

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Join 2 macros

    But if the sheet containing the code is not the one on which the values are being changed by the event code, that would not apply; hence the question!
    But since you brought the subject up, if you are going to disable events, you should always have an error handler in the routine to ensure they get turned back on again (or use a boolean variable to control the event code).
    Last edited by romperstomper; 06-16-2009 at 08:49 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Join 2 macros

    Why we take the following code-line out of for-next statement (for more quick and accurate way )
    Please Login or Register  to view this content.
    and also rewrite it as
    Please Login or Register  to view this content.
    for clarity
    and the SUB change to:


    Please Login or Register  to view this content.
    * and more: why don't you use formula (=) to replace the code-lines
    Please Login or Register  to view this content.
    Last edited by tigertiger; 06-16-2009 at 09:19 AM.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Join 2 macros

    Quote Originally Posted by romperstomper View Post
    ...since you brought the subject up, if you are going to disable events, you should always have an error handler in the routine to ensure they get turned back on again (or use a boolean variable to control the event code).
    Yes, yes, yes. My bad. Absolutely! Something like:
    Please Login or Register  to view this content.
    Do you have some preferred error handling tips?

    Also, you're right about it being unnecessary if none of that action takes place on the sheet itself, but once I start using codes like this in a sheet I hate trying to remember which sheets have it and which don't, so I guess I'm lazy about using it almost all the time. That's probably not good.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Join 2 macros

    More like this:
    Please Login or Register  to view this content.
    I also tend to avoid this:
    Please Login or Register  to view this content.
    unless there is actually a good reason for it. Most of the time it would be perfectly reasonable for users to paste/enter several values at once, so it's usually better to just loop through the relevant cells.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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