Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run Macros from an IF statement within a formula
mIS there anyway we can activate a macro from within a formular for exaple if
cell A1 ="" then run Macro? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run Macros from an IF statement within a formula
Not from a formula.
Maybe you could tie into one of excel's worksheet events--either worksheet_Change or worksheet_calculate. LW_Greeney wrote: mIS there anyway we can activate a macro from within a formular for exaple if cell A1 ="" then run Macro? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run Macros from an IF statement within a formula
Not from within a formula but you can use sheet event code.
Assuming you had a formula in A1 that reads =IF(B1="","",B1) Private Sub Worksheet_Calculate() On Error GoTo endit Application.EnableEvents = False If Me.Range("A1") = "" Then macroname End If endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste to the sheet module. Note: will not fire if A1 is just blank as in contents cleared. For that you would need something like this. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo endit Application.EnableEvents = False If Me.Range("A1") = "" Then macroname End If endit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 14 Aug 2007 09:04:00 -0700, LW_Greeney wrote: mIS there anyway we can activate a macro from within a formular for exaple if cell A1 ="" then run Macro? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run Macros from an IF statement within a formula
If I need to start a new thread just say so. I would like some elaboration on
the macro process described here. I would like to have a worksheet recognize that a cell has data entered into it and then automatically add a date to the date column. Could you please help me Gord? Frank Erb "Gord Dibben" wrote: Not from within a formula but you can use sheet event code. Assuming you had a formula in A1 that reads =IF(B1="","",B1) Private Sub Worksheet_Calculate() On Error GoTo endit Application.EnableEvents = False If Me.Range("A1") = "" Then macroname End If endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste to the sheet module. Note: will not fire if A1 is just blank as in contents cleared. For that you would need something like this. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo endit Application.EnableEvents = False If Me.Range("A1") = "" Then macroname End If endit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 14 Aug 2007 09:04:00 -0700, LW_Greeney wrote: mIS there anyway we can activate a macro from within a formular for exaple if cell A1 ="" then run Macro? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run Macros from an IF statement within a formula
See
http://www.mcgimpsey.com/excel/timestamp.html In article , Frank Erb wrote: If I need to start a new thread just say so. I would like some elaboration on the macro process described here. I would like to have a worksheet recognize that a cell has data entered into it and then automatically add a date to the date column. Could you please help me Gord? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run Macros from an IF statement within a formula
One method using a sheet change event.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A date is entered in column B On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End If enditall: Application.EnableEvents = True End Sub Adjust columns and date format to suit. Gord On Tue, 25 Sep 2007 10:32:03 -0700, Frank Erb wrote: If I need to start a new thread just say so. I would like some elaboration on the macro process described here. I would like to have a worksheet recognize that a cell has data entered into it and then automatically add a date to the date column. Could you please help me Gord? Frank Erb "Gord Dibben" wrote: Not from within a formula but you can use sheet event code. Assuming you had a formula in A1 that reads =IF(B1="","",B1) Private Sub Worksheet_Calculate() On Error GoTo endit Application.EnableEvents = False If Me.Range("A1") = "" Then macroname End If endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste to the sheet module. Note: will not fire if A1 is just blank as in contents cleared. For that you would need something like this. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo endit Application.EnableEvents = False If Me.Range("A1") = "" Then macroname End If endit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 14 Aug 2007 09:04:00 -0700, LW_Greeney wrote: mIS there anyway we can activate a macro from within a formular for exaple if cell A1 ="" then run Macro? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a new formula(s) and/or macros | Excel Discussion (Misc queries) | |||
IF statement formula | Excel Worksheet Functions | |||
Can I use IF statement with a formula? | Excel Worksheet Functions | |||
if statement formula | Excel Discussion (Misc queries) | |||
If statement formula | Excel Worksheet Functions |