Run macro based on cell value - syntax question

[Sather]

I have my spreadsheet set up so that it tells me when conditions are such that I need to run a macro. I click a button that runs the macro and turns the "run macro" message off. Now I need to automate this function so that macro runs whenever it needs to, just like calculations that are automatically updated when a cell value changes. I have found some useful threads here on MrExcel.com that pointed me in the right direction, but I am struggling with the proper syntax to run my macro.

Currently, my button-click macro ("MaxGW") resides in Module3. I have the following code in Microsoft Excel Objects/Sheet1 (Display) on which the cell I am testing is located. The controlling cell contains a formula that results in either True or False. The code appears to be working properly (i.e., identifying whether the control cell is True or False), but I can't figure out the proper syntax to run my macro. I've tried moving the macro from Module3 to Sheet1, but still couldn't figure out the syntax to run the macro. With the coding below I get the error messages: Run time error '50290': Method 'Run' of object '_Application' failed.

Private Sub Worksheet_Calculate()
x = Worksheets("Display").Cells(33, 6).Value
If x = True Then
Application.Run "'myfilename.xls'!Module3.MaxGW"
End If
End Sub

(I got this syntax by recording a new macro, pressing my macro button, and looking at the VBA code that was produced.)

Thanks in advance to anyone who can point me down the correct path.

Scott

Excel 2003/Visual Basic 6.5

 

[BobUmlas]

Private Sub Worksheet_Calculate()
if Worksheets("Display").Cells(33, 6).Value then MaxGW
End Sub

 

[VoG]

With your macro in module 3 try

Private Sub Worksheet_Calculate()
x = Worksheets("Display").Cells(33, 6).Value
If x = True Then
    Call MaxGW
End If
End Sub

 

[Sather]

Thanks Bob, Peter - both of these methods appear to work as now I get an error in the MaxGW macro itself, so I've made progress, but I'm still not where I need to be.

I know the problem isn't with the MaxGW macro itself, because that has been working fine for about 10 years, and still works if I remove this new code. I get the same errors whether my called macros reside in Sheet1 or Module3. Here are my macros, MaxGW calls a series of other macros which perform a simple GoalSeek:

---------
Sub MaxGW1()
Range("B25").GoalSeek Goal:=0, ChangingCell:=Range("B6")
End Sub
Sub MaxGW2()
Range("C25").GoalSeek Goal:=0, ChangingCell:=Range("C6")
End Sub
Sub MaxGW3()
Range("B26").GoalSeek Goal:=0, ChangingCell:=Range("B7")
End Sub
Sub MaxGW4()
Range("C26").GoalSeek Goal:=0, ChangingCell:=Range("C7")
End Sub
Sub MaxGW()
Application.ScreenUpdating = False
OriginalSheet = ActiveSheet.Name
Sheets("Max_Hover_GW").Select
Sheets("Max_Hover_GW").Range("B40:C41").Copy
Sheets("Max_Hover_GW").Range("B6").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False
Application.Run "'myfilename.xls'!MaxGW1"
Application.Run "'myfilename.xls'!MaxGW2"
Application.Run "'myfilename.xls'!MaxGW3"
Application.Run "'myfilename.xls'!MaxGW4"
Sheets(OriginalSheet).Select
Application.ScreenUpdating = True
End Sub
--------------

The message I get is: Runtime error '50290': Method 'ScreenUpdating' of object '_Application' failed. If I comment out that line, I get the error "Application-defined or object-defined error" on line:
Sheets("Max_Hover_GW").Select

I'm not really how this Private Sub macro behaves. Does it prevent me from working on another sheet?

Scott