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...

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

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

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

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 -

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

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

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

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,

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,