View Full Version : [SOLVED:] autorun marcos if condition is met
chuck.bailey
09-22-2018, 09:31 AM
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
09-22-2018, 09:39 AM
.
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
09-22-2018, 09:46 AM
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
09-22-2018, 11:13 AM
.
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
09-22-2018, 11:39 AM
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
09-22-2018, 11:50 AM
.
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
09-23-2018, 06:40 AM
This worked perfectly. And it actually will be better for what I want to happen. Thank you very much for the help!
Logit
09-23-2018, 08:05 AM
.
You are welcome.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.