Private Sub Workbook_SheetChange

Posted by Paul Magruder on April 18, 2001 7:20 PM

How do I specify I only want this macro to run when the data in "A1" changes on sheet1? It currently will run if the data changes in cell a1 on any sheet in the workbook.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Sheets("sheet1").Select
If Range("A1").Value = ("Test") Then
showform1
End If
End sub

Thanks in advance
Paul

Posted by Dave Hawley on April 18, 2001 8:17 PM

Hi Paul

You currently have your code housed in the Private Module of "ThisWorkbook". What you need to do is place the ode in the Private Module of "Sheet1". Easiest way to get there is to right click on the sheet name tab and select "View Code" and try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If Target.Address = "$A$1" Then
If Target = "Test" Then

showform1

End If
End If

End Sub

Dave

OzGrid Business Applications

Posted by Paul Magruder on April 19, 2001 7:11 AM

Re: Thanks Dave........ Again

Dave..... Thanks It did the trick

Posted by Paul Magruder on April 19, 2001 7:21 AM

Control Charts...8 tests for special causes

Does anyone know where I might be able to find code that checks for the 8 tests for special causes in a Control Chart? I started writing the code, but am having great difficulty.

Thanks in Advance
Paul

Posted by polo on April 19, 2001 9:31 AM

WOW!!

I SPENT ALL NIGHT TRYING TO SOLVE THIS ONE, JUST TO GET MY SYSTEM LOCKED-UP. DAVE: YOU'RE THE ONE.

Dave