Run Macro when a condition is met

[EXCELMACROS]

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?

 

[Simon Lloyd]

Yes you can do that but it would have to be in the worksheet calculat
event and you would have to specify a cell, range of cells or intersec

Hi guys

I have a share file, I will like for a macro to run when a condition i
met
for example, when =count(B7:F7) = 4 then macro kicks and run by itsel

is that possible

-
Thank you..

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com

 

[EXCELMACROS]

Do you have a sample I can take a look at?

 

[excel-ant]

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

 

[Susan]

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

 

[JLGWhiz]

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

 

[Simon Lloyd]

As Susan has show is fine, however you would be better using th
intersect method for non contiguous dat
Code
-------------------
If Not Intersect(Target, Range("A1,B2,C3")) Is Nothing Then
if target.value = 4 the
call NameOfOtherMacr
End I

-------------------
or
Code
-------------------
If Not Intersect(Target, Range("A1:A10,B2,C510")) Is Nothing Then
if target.value = 4 the
call NameOfOtherMacr
End I

-------------------

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 rang

set target = worksheet("sheet1").range("a1"

if target.value = 4 the

call NameOfOtherMacr

end i

End Su
'===================
not tested, but i think that works
:
susa


On Jan 5, 3:56*pm, EXCELMACROS <[email protected]
wrote
Forums' (http://www.thecodecage.com/forumz/showthread.php?t=46739-) Hid
quoted text

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com

 

[EXCELMACROS]

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,

 

[Gord Dibben]

& Format(Date,"mm-dd-yyyy") & "abc,xls"


Gord Dibben MS Excel MVP

 

[EXCELMACROS]

Thank you all
--
Thank you...

& Format(Date,"mm-dd-yyyy") & "abc,xls"


Gord Dibben MS Excel MVP