| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro when a condition is met
Hi guys,
I have a share file, I will like for a macro to run when a condition is met, for example, when =count(B7:F7) = 4 then macro kicks and run by itself is that possible? -- Thank you... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
Yes you can do that but it would have to be in the worksheet calculate event and you would have to specify a cell, range of cells or intersect that the trigger should happen on!EXCELMACROS;168746 Wrote: Hi guys, I have a share file, I will like for a macro to run when a condition is met, for example, when =count(B7:F7) = 4 then macro kicks and run by itself is that possible? -- Thank you... -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46739 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
Do you have a sample I can take a look at?
-- Thank you... "Simon Lloyd" wrote: Yes you can do that but it would have to be in the worksheet calculate event and you would have to specify a cell, range of cells or intersect that the trigger should happen on!EXCELMACROS;168746 Wrote: Hi guys, I have a share file, I will like for a macro to run when a condition is met, for example, when =count(B7:F7) = 4 then macro kicks and run by itself is that possible? -- Thank you... -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46739 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
Of course
================================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) If WorksheetFunction.Count(Range("B7:F7")) = 4 Then Call Macro1 End If End Sub ================================================== (Macro1 in Module1) ================================================== Sub macro1() MsgBox "Result" End Sub ================================================== Ant http://www.excel-ant.co.uk |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
say your formula if =count(B7:F7) resides in sheet1, cell A1.
in sheet1's macro area, you would have: '========================= Private Sub Worksheet_Calculate() dim target as range set target = worksheet("sheet1").range("a1") if target.value = 4 then call NameOfOtherMacro end if End Sub '==================== not tested, but i think that works. :) susan On Jan 5, 3:56*pm, EXCELMACROS wrote: Do you have a sample I can take a look at? -- Thank you... "Simon Lloyd" wrote: Yes you can do that but it would have to be in the worksheet calculate event and you would have to specify a cell, range of cells or intersect that the trigger should happen on!EXCELMACROS;168746 Wrote: Hi guys, I have a share file, I will like for a macro to run when a condition is met, for example, when =count(B7:F7) = 4 then macro kicks and run by itself is that possible? -- Thank you... -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=46739- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
This is untested, but it will give you an idea of how to set up
the macro call using a worksheet event. All worksheet event code must be in the worksheet code module. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B7:F7")) Is Nothing Then If WorksheetFunction.Count(Range"B7:F7") = 4 Then 'Put your Macro name here End If End If End Sub "EXCELMACROS" wrote: Do you have a sample I can take a look at? -- Thank you... "Simon Lloyd" wrote: Yes you can do that but it would have to be in the worksheet calculate event and you would have to specify a cell, range of cells or intersect that the trigger should happen on!EXCELMACROS;168746 Wrote: Hi guys, I have a share file, I will like for a macro to run when a condition is met, for example, when =count(B7:F7) = 4 then macro kicks and run by itself is that possible? -- Thank you... -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46739 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
As Susan has show is fine, however you would be better using the intersect method for non contiguous data Code: -------------------- If Not Intersect(Target, Range("A1,B2,C3")) Is Nothing Then if target.value = 4 then call NameOfOtherMacro End If -------------------- or Code: -------------------- If Not Intersect(Target, Range("A1:A10,B2,C5:D10")) Is Nothing Then if target.value = 4 then call NameOfOtherMacro End If -------------------- Susan;168810 Wrote: say your formula if =count(B7:F7) resides in sheet1, cell A1. in sheet1's macro area, you would have: '========================= Private Sub Worksheet_Calculate() dim target as range set target = worksheet("sheet1").range("a1") if target.value = 4 then call NameOfOtherMacro end if End Sub '==================== not tested, but i think that works. :) susan On Jan 5, 3:56*pm, EXCELMACROS wrote: Do you have a sample I can take a look at? -- Thank you... "Simon Lloyd" wrote: Yes you can do that but it would have to be in the worksheet calculate event and you would have to specify a cell, range of cells or intersect that the trigger should happen on!EXCELMACROS;168746 Wrote: Hi guys, I have a share file, I will like for a macro to run when a condition is met, for example, when =count(B7:F7) = 4 then macro kicks and run by itself is that possible? -- Thank you... -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread:'Run Macro when a condition is met - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=46739-) Hide quoted text - - Show quoted text - -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46739 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
Thank you all, let me try it, I'll come back. In the mean time, I have
another question.... I want to call the report : Filename:=Path & Date & "abc.xls" But Date is not an option, i'm guessing the "/" is not letting it save, does any of you have a trick to get my date on the file name without having to enter it? thanks, -- Thank you... "JLGWhiz" wrote: This is untested, but it will give you an idea of how to set up the macro call using a worksheet event. All worksheet event code must be in the worksheet code module. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B7:F7")) Is Nothing Then If WorksheetFunction.Count(Range"B7:F7") = 4 Then 'Put your Macro name here End If End If End Sub "EXCELMACROS" wrote: Do you have a sample I can take a look at? -- Thank you... "Simon Lloyd" wrote: Yes you can do that but it would have to be in the worksheet calculate event and you would have to specify a cell, range of cells or intersect that the trigger should happen on!EXCELMACROS;168746 Wrote: Hi guys, I have a share file, I will like for a macro to run when a condition is met, for example, when =count(B7:F7) = 4 then macro kicks and run by itself is that possible? -- Thank you... -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46739 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
& Format(Date,"mm-dd-yyyy") & "abc,xls"
Gord Dibben MS Excel MVP On Mon, 5 Jan 2009 14:37:01 -0800, EXCELMACROS wrote: Thank you all, let me try it, I'll come back. In the mean time, I have another question.... I want to call the report : Filename:=Path & Date & "abc.xls" But Date is not an option, i'm guessing the "/" is not letting it save, does any of you have a trick to get my date on the file name without having to enter it? thanks, |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro when a condition is met
Thank you all
-- Thank you... "Gord Dibben" wrote: & Format(Date,"mm-dd-yyyy") & "abc,xls" Gord Dibben MS Excel MVP On Mon, 5 Jan 2009 14:37:01 -0800, EXCELMACROS wrote: Thank you all, let me try it, I'll come back. In the mean time, I have another question.... I want to call the report : Filename:=Path & Date & "abc.xls" But Date is not an option, i'm guessing the "/" is not letting it save, does any of you have a trick to get my date on the file name without having to enter it? thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro if condition | Excel Discussion (Misc queries) | |||
| macro to run IF condition met | Excel Programming | |||
| use more than 4 condition in cf without using macro | Excel Worksheet Functions | |||
| run macro only if condition is met | Excel Programming | |||
| macro to run only under certain condition - how? | Excel Programming | |||
Linear Mode
