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?

 

[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

 

[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