I would like to change the focus from the current worksheet to another when a
value in a specific range is changed. This is an easy VB statement but I am
going round in circles... Please Help

Thanks

Les

Hi Les,

I'll be interested in seeing a better method if someone has one but this
works:-

'Only runs with Target in range B6:C8

If Target.Row = 6 And Target.Row <= 8 _
And Target.Column = 2 And Target.Column <= 3 Then
Sheets("Sheet2").Activate
End If

Regards,

OssieMac

"Les G" wrote:

I would like to change the focus from the current worksheet to another when a
value in a specific range is changed. This is an easy VB statement but I am
going round in circles... Please Help

Thanks

Les

Perhaps something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Sheet3.Activate
End If
End Sub



"Les G" wrote:

I would like to change the focus from the current worksheet to another when a
value in a specific range is changed. This is an easy VB statement but I am
going round in circles... Please Help

Thanks

Les

You are a star... I made a slight modification and it worked a dream...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
Sheet3.Activate
End If
End Sub

Thanks very much

"JMB" wrote:

Perhaps something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Sheet3.Activate
End If
End Sub



"Les G" wrote:

I would like to change the focus from the current worksheet to another when a
value in a specific range is changed. This is an easy VB statement but I am
going round in circles... Please Help

Thanks

Les

you're welcome, thanks for posting back

"Les G" wrote:

You are a star... I made a slight modification and it worked a dream...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
Sheet3.Activate
End If
End Sub

Thanks very much

"JMB" wrote:

Perhaps something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Sheet3.Activate
End If
End Sub



"Les G" wrote:

I would like to change the focus from the current worksheet to another when a
value in a specific range is changed. This is an easy VB statement but I am
going round in circles... Please Help

Thanks

Les

Thank you from a wonderful day in sunny South Africa

"JMB" wrote:

you're welcome, thanks for posting back

"Les G" wrote:

You are a star... I made a slight modification and it worked a dream...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
Sheet3.Activate
End If
End Sub

Thanks very much

"JMB" wrote:

Perhaps something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Sheet3.Activate
End If
End Sub



"Les G" wrote:

I would like to change the focus from the current worksheet to another when a
value in a specific range is changed. This is an easy VB statement but I am
going round in circles... Please Help

Thanks

Les

You're welcome from an overcast, dreary day in Illinois (U.S.)

"Les G" wrote:

Thank you from a wonderful day in sunny South Africa

"JMB" wrote:

you're welcome, thanks for posting back

"Les G" wrote:

You are a star... I made a slight modification and it worked a dream...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
Sheet3.Activate
End If
End Sub

Thanks very much

"JMB" wrote:

Perhaps something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Sheet3.Activate
End If
End Sub



"Les G" wrote:

I would like to change the focus from the current worksheet to another when a
value in a specific range is changed. This is an easy VB statement but I am
going round in circles... Please Help

Thanks

Les