autorun marcos if condition is met

[chuck.bailey]

So after spending a lot of time figuring out how to use macros. I found a very complex solution to my issue, and then immediately after found a much simpler solution. Anyway, now that I've got it working, I would like it to work automatically. Like when the condition is met the macro automatically runs. It's designed for an end user that won't understand how it works or be able to use a macro. It's just magic to them. Which is what I want. So here's my code.

Option Explicit

Sub WorksheetActivate()

Worksheets("Sheet1").Activate
Worksheets("Sheet2").Activate

End Sub
Sub RangeSelect()

If Worksheets("Sheet2").Cells(3, 1).Value = "Week1" Then

Worksheets("Sheet1").Range("test").Copy

Worksheets("Sheet2").Range("Calendar1").PasteSpecial

End If

End Sub
Sub RangeSelect2()

If Worksheets("Sheet2").Cells(3, 1).Value = "Week2" Then

Worksheets("Sheet1").Range("test2").Copy

Worksheets("Sheet2").Range("Calendar1").PasteSpecial

End If

End Sub

[Logit]

.
You can have the macros "fire" when the workbook is first opened ... or when the workbook is closed (say, after changes have been made to the worksheets) ?

On opening, in the ThisWorkbook module, call the macro in this sub :


Private Sub Workbook_Open()
RangeSelect2
End Sub

Or - upon closing the workbook :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RangeSelect
End Sub


If you want the macro to "fire" when the user opens a worksheet, in the Sheet Level module :

Private Sub Worksheet_Activate()
RangeSelect2
End Sub

Keep in mind that ALL of these choices will send an email automatically whether you want to or not.

The use of a command button to fire the macros may be the wiser choice. It seems there is always a reason / opportunity to NOT SEND an email.




This macro is not needed in any of the above cases :

Sub WorksheetActivate()

Worksheets("Sheet1").Activate
Worksheets("Sheet2").Activate

End Sub

[chuck.bailey]

So I can create a command button that will run all macros, so then the user just has to fill in the "Weeks" and press the command button? But there is no way for the macro to just activate if a condition is met?

And I understand what you mean by not having to activate the worksheets. is that because I used the worksheet in the code itself?

[Logit]

.

But there is no way for the macro to just activate if a condition is met?

Yes you can do that ... but ... if you are the user, you are typing in data and suddenly the entire workbook stops because it is attempting to send an email, and you
have a deadline to meet and want to continue entering other data that is important and should be included in the email ... ????

It will be less confusion and heartache for everyone if you design the workbook to send the email when everything is completed vs. when the worksheet data meets a requirement.

Yes, using a command button to "fire" the macro is the norm. Or you can send the email when the workbook is first opened or when it is closed, or when a particular worksheet is activated ...
that means when the user opens the sheet in question to view.

I would caution you to not make the process completely automatic - leaving the user absolutely no control over sending the email. There will always be a circumstance when an email should
not be sent and your automatic design has left the user no control over stopping the email being sent. Consider the wrong data being forwarded then having to apologize for your mistake
and sending a second email with the correct data. Or maybe sending an email with confidential information that the receiver should not get ?

A second consideration .. if 'automatically send' means once you click the command button, the email is never displayed but rather simply gets sent without review first, you can
change the .Display command in the code to .Send.

And I understand what you mean by not having to activate the worksheets. is that because I used the worksheet in the code itself?

Yes.

[chuck.bailey]

Ok, I understand. And that makes sense. I’m working on the command button. I haven’t figured out how to connect more than 1 macro to a command button, but I’ll keep working on it. Thank you! This is going to make this portion of our job so much better!

[Logit]

.
To run more than one macro by clicking only one command button :

Sub YourFirstMacro()
    'code here


    YourSecondMacro '<<--- (type in the name of your second macro)


End Sub


Sub YourSecondMacro()
    'code here


End Sub

[chuck.bailey]

This worked perfectly. And it actually will be better for what I want to happen. Thank you very much for the help!

[Logit]

.
You are welcome.