MSExcel97 - running a macro from the spreadsheet

R

Thread Starter

Ron Powers

Hi folks:

I have a need to run a VBA macro (called "shift") every time a particular value in a cell ("I1") goes from "False" to "True". Anyone have any ideas on how to accomplish this? Is it possible to embed a macro into an "if" statement in a spreadsheet?

Thanks!

-----
Ron Gage - Saginaw, Michigan
([email protected])

Visit the Gastracker website: http://gastracker.rongage.org
 
R

Robert ANTONISHEN

You can set up a custom event handler to respond on data changes in the worksheet. The following two subroutines in the workbook set this up.

Private Sub Workbook_Open()
TrapData
End Sub

Sub TrapData() ' Set up OnData trapping.
Worksheets("Sheet1").OnData = "MyFunction"
End Sub

The MyFunction Sub in a module will be called whenever new data changes on
the sheet
(works for DDE links too). This one was set up to colour cells based on
their values.

Sub MyFunction() ' OnData handler example.
For Each Cell In Worksheets("Sheet1").Range("D1:D4")
If IsNumeric(Cell) Then
Cell.Offset(0, 1).Font.ColorIndex = Cell
End If
Next Cell
End Sub

Good Luck. Have fun.
 
I

Irish, William

If you get tired of writing macros in Excel for report generation you might want to take a look at a package called XLReporter from a company at
www.thereportcompany.com
I have only seen demonstrations of their product but it looks pretty nice.

++++++++++++++++
William Irish
St. Paul, Minnesota 55106
++++++++++++++++
 
G

Glass, Philip

Just use the Change event of the Worksheet object in the sheet you are using.
Here's an example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("I1") Then
If Target.Value = True Then
Prompt = MsgBox("Value is True", vbCritical, "TRUE")
End If
If Target.Value = False Then
Prompt = MsgBox("Value is False", vbCritical, "FALSE")
End If
End If

End Sub

Phil
 
Robert:

ANTONISHEN Robert wrote:

>You can set up a custom event handler to respond on data changes in the
>worksheet.
>The following two subroutines in the workbook set this up.
>Private Sub Workbook_Open()
> TrapData
>End Sub
>Sub TrapData() ' Set up OnData trapping.
> Worksheets("Sheet1").OnData = "MyFunction"
^^^^^^

Can this be set up to monitor a single cell as opposed to the entire worksheet?
In my example, cell "I1" (row 1, column 9)... Possibly something like this:

When Worksheets("Sheet1").cells(9,1).value = "TRUE"
do "MyFunction"

MyFunction would block at the end of it's execution until cells(9,1) returns to "FALSE".

I would still feel more comfortable knowing the "MyFunction" bit was called explicitly only when "I1" was set to "TRUE".

If it helps, the application is a data log table. When the trigger (I1) goes true, I shift the first 100 rows by 6 columns down the sheet one row. The first row is my live data from DDE exchange. Until the trigger event occurs, I don't care about capturing the data.

Thanks!

-----
Ron Gage - Saginaw, Michigan
 
Top