+ Reply to Thread
Results 1 to 6 of 6

Macro help: Adding Code to save file or email based on a condition

  1. #1
    Registered User
    Join Date
    12-26-2012
    Location
    St. louis
    MS-Off Ver
    Excel 2010
    Posts
    11

    Macro help: Adding Code to save file or email based on a condition

    Hi,

    I am new to macros and was able to put together a template using this forum that works for now but need some help with making it work on a larger scale.

    Background:

    The workbook is a tool for users that populates employee data and allows them to recommend a salary increase based on certain conditions. Once the user has populated the tool, they click a command button and it emails the active worksheet as a pdf file to a specific mailbox for review.

    Problem:

    What I am looking for help on is how to add a macro to the same command button that looks at a specific cell and if it says "Yes" then send the file to the mailbox. If that same cell says "No" then save the workbook to a specific folder location with a specific file name. The file name needs to be equal to a different cell value which is a combination of different cells. (Ex. Employee Name + Type of a change + Date + user Name) so we can identify one request from another during an audit.

    All of the current macros are assign to a single click of the command button.

    Current macros:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Moderators Note: Please follow Forum Rule #3 and use code tags. Added this time, but please use them in the future…Thanks.
    Last edited by jeffreybrown; 12-26-2012 at 11:28 AM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,642
    Add an If at the start of the Prompt sub to check the value in the cell to decide what to do.

    PS Name and Time are both VBA statements and shouldn't be used for the name of subs/functions/variables etc.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    12-26-2012
    Location
    St. louis
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Macro help: Adding Code to save file or email based on a condition

    Thanks Norie. Do you have any code examples of how or where I could enter the VBA?

    I tried entering an If statement but confused myself more than made progress.

    If the cell on the active sheet says "no" then I am trying to save to the location "X:\Administration\Compensation\Comp Changes\Recruiter files"

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,642
    What is the existing If statement for?

  5. #5
    Registered User
    Join Date
    12-26-2012
    Location
    St. louis
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Macro help: Adding Code to save file or email based on a condition

    The If statement would be if cell "C23" on the active sheet where the command button with the above macros assigned to it says "Yes" then prompt the VBA coding all of the above coding. If cell "C23" says "no" then prompt all of the above macros but instead of stepping into the "Sub Send PDF" create a different macro that saves the entire workbook with a specific name to the specific location "X:\Administration\Compensation\Comp Changes\Recruiter files"

  6. #6
    Registered User
    Join Date
    12-26-2012
    Location
    St. louis
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Macro help: Adding Code to save file or email based on a condition

    To follow up it appears this needs to all be done into the IF macro below:
    Sub Prompt()

    Dim iReply As Integer

    iReply = MsgBox(Prompt:="Are you sure you wish to authorize this Comp Recomendation and submit to Regional C&B for final approval?", _
    Buttons:=vbYesNoCancel, Title:="UPDATE MACRO")

    If iReply = vbYes Then

    Run "Name"
    Run "Time"
    Run "SendPDF"

    ElseIf iReply = vbNo Then

    'Do Other Stuff

    Else 'They cancelled (VbCancel)

    Exit Sub

    End If
    End Sub

    However, the prompt in the above macro is based on a pop-up message. Therefor does anyone have examples of how to input a second "IF" statement that would do the following:

    If iReply = vbYes Then (Look at cell C23 and if Yes, "Run name, Run Time, Run PDF" and if No, "Run Name, Run Time, Run Save (I need to create a new macro to save the file to the drive listed above"

    Run "Name"
    Run "Time"
    Run "SendPDF"

+ 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