PLEASE help with VB macro fix????

Nica,

I think this code is what you are looking for. I have assumed that the "s" for survey will be typed into column "A". To change this find the line in the code that says:

Set TargetColumn = Me.Range("A:A")

Then change "A:A" to whatever column you need it to be, e.g. for column "B" change it to "B:B"

Follow these steps.

1. Right-click on the name tab of the "Sales" worksheet.
2. Select "View Code" from the pop up menu
3. Copy and paste the following code into the right hand panel of the window that appears.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Declare Variables
Dim TargetColumn As Range
Dim TriggerText As String

'Initialise Variables
Set TargetColumn = Me.Range("A:A") 'This is the column that will contain the "s" for "survey"
TriggerText = "s" 'This is the Text that is used to denote survey

If Target.Column = TargetColumn.Column Then
If LCase(Target.Value) = TriggerText Then
Sheets("Survey").Activate
End If
End If
End Sub 'End of the code

If you have any problems just repost.

The following macro will bring up the survey worksheet anytime the word "Survey" or "survey" is typed into any cell on the sales sheet. To install this open the workbook containing the two sheets and right click on the tab for the "sales" sheet. Choose view code from the menu then paste this macro into the VB editor window.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Value = "Survey" Then
Sheets("Survey").Select
End If
If Target.Value = "survey" Then
Sheets("Survey").Select
End If
Application.ScreenUpdating = True
End Sub

Hope this helps,
Rick

One More thing

Forgot to tell you if you want to change the trigger word merely change the Target.Value word to whatever you want.

Rick

If Target.Column = TargetColumn.Column Then If LCase(Target.Value) = TriggerText Then Sheets("Survey").Activate End If End If 'End of the code

Mark, you the man!!
Works fine, except when I delete data from cells
in that column(more than one at a time - individually works ok) now, I get a run-time error '13' type mismatch. Have any idea on how
I can prevent this from happening?
Of course, other than this small setback - its works beautifully. I can certainly live without the deletion fix if you don't know what gives.

Thanks again,
nica

Easy fix, to save explaining where to put code though, just copy and paste this code instead of the original code I gave you:

Private Sub Worksheet_Change(ByVal Target As Range)
'Declare Variables
Dim TargetColumn As Range
Dim TriggerText As String

On Error GoTo ERR_DELETE

'Initialise Variables
Set TargetColumn = Me.Range("A:A") 'This is the column that will contain the "s" for "survey"
TriggerText = "s" 'This is the Text that is used to denote survey

If Target.Column = TargetColumn.Column Then
If LCase(Target.Value) = TriggerText Then
Sheets("Survey").Activate
End If
End If

ERR_DELETE:

End Sub 'end of code

, If Target.Column = TargetColumn.Column Then If LCase(Target.Value) = TriggerText Then Sheets("Survey").Activate End If End If

If Target.Column = TargetColumn.Column Then If LCase(Target.Value) = TriggerText Then Sheets("Survey").Activate End If End If 'End of the code

Mark, you the man!!
Works fine, except when I delete data from cells
in that column(more than one at a time - individually works ok) now, I get a run-time error '13' type mismatch. Have any idea on how
I can prevent this from happening?
Of course, other than this small setback - its works beautifully. I can certainly live without the deletion fix if you don't know what gives.

Thanks again,
nica

Thanks Mark!!
I definately owe you one!

nica

If Target.Column = TargetColumn.Column Then If LCase(Target.Value) = TriggerText Then Sheets("Survey").Activate End If End If If Target.Column = TargetColumn.Column Then If LCase(Target.Value) = TriggerText Then Sheets("Survey").Activate End If End If

MARK,
The fix on the error when I 'delete' more than
one cell in this column is not working. It still flashes over to the sheet as I needed - I just hate to give this out to everyone knowing that when they delete cells simultaniously(as they do) - they will get an error. I this fix working on your System?
Thanks again,
nica

: On Error GoTo ERR_DELETE If Target.Column = TargetColumn.Column Then If LCase(Target.Value) = TriggerText Then Sheets("Survey").Activate End If End If If Target.Column = TargetColumn.Column Then If LCase(Target.Value) = TriggerText Then Sheets("Survey").Activate End If End If

This fix is working on my system. I've tried every combination I can think of to try to duplicate your error. Could you post again and describe exactly what you are doing and give me a small set of test data that you would use? I've been away from the office or else I would have replied to this sooner.