Macro on Cell Change (Again)

Posted by JAF on May 17, 2000 2:45 AM

I need to run a macro that opens another spreadsheet whenever any cell in Column D is completed with the text "Yes".

I already have the basic code for this (from Celia) which is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 4 then Call macro_name
End Sub

There are however 2 problems with the above:
1) If I enter a value in column D and then press Tab (or Shift+Tab), the macro does not run because the ActiveCell is no longer in Column D.
2) The macro will run (when either Enter, CursorUp or CursorDown is pressed), but it runs irrespective of the value in Column D - I only want it to run when "Yes" is entered.

I can't change the number of the Column reference to 5 because a user could input data in Column E and then press Return which would run the macro (as it currently stands) which I do not want to happen.

Any suggestions as to how I get around this?

Cheers - JAF

Posted by Ivan Moala on May 17, 2000 4:57 AM

Hi Jaf
Try this;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Ans As String
Dim LastCell As String

LastCell = ActiveCell.Offset(-1, 0)
If Target.Column = 4 Then
If Target.Rows.Count = 1 Then
Ans = Format(Target, ">")
If Ans = "YES" Then Call Macro_Name
Else
Ans = Format(LastCell, ">")
If Ans = "YES" Then Call Macro_Name
End If
End If
End Sub

There are a few Mods to get over any user input
errors Eg. If you users enter in lower case or
any combo other then the "Yes" then macro wouldn't
work hence the use of the formating to uppercase.

Also your users could select a range in D and enter
values, the 2nd part Target.Rows.Count) takes care of this.

The macro example you gave didn't work because
you had not taken it further with getting the
"Yes" variable to activate the macro.
This is why it only activated on Enter as when
you entered data you were still in the same column.
BUT when you tabed out of it you were in another
column PLUS, it's a good idea to use the the Target variable instead of Activecell as the
Target refers to the Change range/cell and not
neccesarly the Activecell.

Ivan

Posted by JAF on May 17, 2000 5:21 AM

Thanks again Ivan.

I'd just figured out a way of doing it (If Target.Column = 4 and Target.Value = "Yes" then Call macro_name) and had come back to post a "don't bother" message.

You keep managing to solve these just before I figure out what to do!

JAF