+ Reply to Thread
Results 1 to 26 of 26

How to add multiple subs / criteria to one work book macro vba

  1. #1
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    How to add multiple subs / criteria to one work book macro vba

    Hi there , i hope someone will be able to help me with this, for my last post on this category i got no help.

    i have a automated email macro that goes off when certain cell reaches a certain value. so i got it to work but there are multiple cells i need to search the rest after the first allocated cell gets checked..
    now i'm really new at macro and taking me few weeks reading up and playing around to figure out this single macro,I'm willing to learn so please help will be appreciated..

    the macro:

    Please Login or Register  to view this content.
    so this works but only for single cell "F1" from sheet "List_sheet", which looks like this, it changes the "No_Warnings" to "Warning_sent" and a email get sent giving the adjacent cell information and warning message as seen above in macro.
    example.jpg
    I need to search F2 ,F3 ,F4.. and so on, and send individual email if any of them reach certain value, how will i amend, to my current macro to do this, i tried re adding the the macro but stops at "F1" search ,if makes sense.
    Can anyone help me on this,if don't understand my explanation , apologies, ask and i will try explain more.

    Kind regards

    Edwin
    Last edited by no1freeman; 02-18-2015 at 06:36 AM. Reason: Requested to change due to not complying to rule 3 from forum rules

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi, Edwin,

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    apologies ,so i learn. is this correct?

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    i updated my first post as well

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to add multiple subs / criteria to one work book macro vba

    Do you want the code to loop down the rows?
    If posting code please use code tags, see here.

  6. #6
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Loop as in go through F1,F2,F3,F4... and run the criteria of If statements for each specified cell ,then yes. every time the workbook opens this macro must run.
    When find that the specified cell has reached below 0 that will send "WARNING" email and change the "No_warnings" to "Warning_sent" in the H column adjacent to the specified cell,
    and if it goes through each specified cell where no values exceed its parameters that will display message "Nothing to email today"
    i only succeeded in getting it to work for one cell. hope all makes sense.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to add multiple subs / criteria to one work book macro vba

    Perhaps.
    Please Login or Register  to view this content.
    Which you could call from the workbook open event.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    i placed the code in Module 1 and the
    Please Login or Register  to view this content.
    In the ThisWorkbook
    When i run it i get a "Type mismatch" Run-time error '13'
    when select debug it highlights the following line
    Please Login or Register  to view this content.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to add multiple subs / criteria to one work book macro vba

    That's a typo, try this.
    Please Login or Register  to view this content.

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi, no1freeman,

    I wonder why you use
    Please Login or Register  to view this content.
    instead of using
    Please Login or Register  to view this content.
    And I would not create an independant instance of Outlook for each dataset to be send but see if using the the autofilter may narrow the number of rows down amd loop through the visible ones in order to send mails.

    Ciao,
    Holger

  11. #11
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Great that worked! Thanks Norie, i see the "nothing to email today" msg comes up for each cell that complies with the IF , is there a way to limit this to one msg box,
    theirs 500 entries which could throw up allot of "nothing to email today" messages

  12. #12
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Quote Originally Posted by HaHoBe View Post
    Hi, no1freeman,

    I wonder why you use
    Please Login or Register  to view this content.
    instead of using
    Please Login or Register  to view this content.
    And I would not create an independant instance of Outlook for each dataset to be send but see if using the the autofilter may narrow the number of rows down amd loop through the visible ones in order to send mails.

    Ciao,
    Holger
    Hi, HahoBe

    as i said before im still learning and very new to macro so the

    Please Login or Register  to view this content.
    was basically what i read up on and it worked , could u explain from what u said bellow so i may learn the better way of doing things
    And I would not create an independant instance of Outlook for each dataset to be send but see if using the the autofilter may narrow the number of rows down amd loop through the visible ones in order to send mails.
    Thanks in advance

  13. #13
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi, no1freeman,

    could you attach a sample workbook for checking the code to do what itīs supposed to do?

    Ciao,
    Holger

  14. #14
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi have made a sample workbook and attached it,but now macro giving "user-defined type not defined"
    hope it helps
    Attached Files Attached Files

  15. #15
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi, no1freeman,

    maybe try this code. I couldnīt test the sending of the mail (no OL installed on this pc and no mailing list available):

    Please Login or Register  to view this content.
    If you have questions about the code place the mouse inside the code in a module and press F8 to single step through the code. If you hover with the mouse over variables they should show their actual values.

    Ciao,
    Holger

  16. #16
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi HaHoBe

    i placed your code in the Module 1 and works great when i run it from there, only get one "Nothing to email today" msg box, but it wont run when i close and re open the workbook. in "this workbook' the following code is

    Please Login or Register  to view this content.

    as Norie suggested and i get a message "user-defined type not defined" , i removed it and then macro runs fine but same issues with re opening of work book the Macro doesn't automatically run.

    there's allot of new things i haven't seen in your code which gonna have to look up to understand for my knowledge base is low on macro, for now :p

  17. #17
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi, no1freeman,

    youīre mixing things up when copying the codes.

    First of all the procedure in ThisWorkbook is
    Please Login or Register  to view this content.
    as this is an event which gets started from the application when opening the workbook.

    And, my bad, the procedure in Module1 should be
    Please Login or Register  to view this content.
    in order to be started from the Workbook_Open-event.

    Ciao,
    Holger

  18. #18
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi , HaHoBe

    I change the private sub to public in the module and worked! thanks HaHoBe

    Just when thought I'm done i found myself stuck again. guess something small but wanted to change the criteria range from lower then 0 to about lower then 5 which = to 120 hours.
    which was easy enough but now it picks up all the 0 in my list and sending email , i received like 400 warning emails because of that haha.
    so i need to get it to ignore "0" or create IF functions for "IF the cell in column "G" = 0 , which contains project code,then ignore adjacent F cell)" then should move on with the list ?

    I tried adding
    Please Login or Register  to view this content.
    it seams to work but i think will probably only work if there word "Text" in the cell? how do i specify that column G should have text not = 0 before runs skrip?

    Edwin

  19. #19
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi, Edwin,

    please try the Autofilter line for Column F from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Ciao,
    Holger

  20. #20
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi HaHoBe

    thanks for your help really appreciate it, it all works fine. if i would want to add another macro to the workbook later, will i just add Call "macro_name" below the Call Email_warning that's placed "in workbook"
    and then create another module and place macro inside there? last question before close the thread

    Edwin

  21. #21
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi Edwin, Yes you can call a MacroName using
    Call Macro_name according to the requirenement, rather you can place a sample module code & call a macro below Email_Warning just to confirm/check whether you can do it or not
    Regards
    Parth

    I appreciate your feedback. Hit * if u Like.
    Rules - http://www.excelforum.com/forum-rule...rum-rules.html

  22. #22
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi, Edwin,

    to me the answer is depeneding on some issues: will the macro be used on itīs own, will it laways run in conjunction with the other macro?

    If you want to hold any single macro in itīs own module the way you explained is the best one (but I would recommend to rename the modules to some useful names instead of keeping them standard).

    An alternative may be (and not that clean in the means of progress) to call the macro at the end of macro Email_warning.

    Anhow: you should consider to change the type from Sub to Public to return a boolean to see if the procedure has been run without issue or use a global variable to store the success and evaluate that variable for progress or abort or rerun.

    Itīs up to you and your plans on how you want to proceed.

    Ciao,
    Holger

  23. #23
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Hi HaHoBe

    I want to run a similar macro with automated email but with other criteria , when certain cell reaches "100%" to send email stating a delivery.

    Please Login or Register  to view this content.
    but seams to change my in_progress to deliverd even though not 100% ? i don't know if because i put in new module or what im missing, i placed this macro in module 2 and my "thisworkbook" looks like this
    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    I fixed it! yeah!

    all i did was change the criteria from

    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Thanks for all the help!

  25. #25
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Re: How to add multiple subs / criteria to one work book macro vba

    Never mind dont know what happen, it worked and then stopped working?! it actually didn't stop it just started sending thousands of emails .....
    Can anyone help me where i went wrong with second macro

  26. #26
    Registered User
    Join Date
    11-19-2014
    Location
    Capetown South Africa
    MS-Off Ver
    2013
    Posts
    37

    Unhappy Re: How to add multiple subs / criteria to one work book macro vba

    Never mind dont know what happen, it worked and then stopped working?! it actually didn't stop it just started sending thousands of emails .....
    Can anyone help me where i went wrong with second macro

+ 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. Replies: 3
    Last Post: 02-24-2014, 02:39 PM
  2. Recording a Macro to be used on multiple spreadshets within work book
    By sds33 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-18-2013, 04:49 AM
  3. Create PDF files from multiple work sheets in a single work book
    By erprasannaa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2012, 03:42 AM
  4. [SOLVED] Need help allowing multiple subs to work within one sheet.
    By tknox827 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-13-2012, 12:32 PM
  5. copy data from one work book to other based on criteria
    By arnab0711 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-10-2009, 01:35 AM

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