I have a cell with pull down list for user to select on of
two values. After selection I would like to move user
automatically to particular cell. How I can do it ?

Regards,

Tony

Tony,

As you are using Data Validation, this needs XL2000 or later, as XL97
doesn't recognise a change created by DV

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H10")) Is Nothing Then
Range("J15").Activate
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP

"Tony" wrote in message
...
I have a cell with pull down list for user to select on of
two values. After selection I would like to move user
automatically to particular cell. How I can do it ?

Regards,

Tony

Hi Tony, try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$3" Then 'B3 changed to your list output cell
Range("D1").Select ' D1 changed to the cell where you want to go
End If
End Sub


Just paste this code in the worksheet your list is in......

hope this helps

seeya ste

Hi Ste Mac

Thank you for your help but I need a bit more of your assistance if possible.

Following your suggestion I have created the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$J$13" Then
Worksheets("SGE").Activate
' Cells(38, 3).Select
Range("C38").Select
End If
End Sub

After changing selection of my list selection moves to cell C38 but I am
getting the following error message:

Run-time error '1004'
Select method of Range class failed

What is wrong and how I can fix it.

Thank you for your help.

Regards,

Tony


"ste mac" wrote:

Hi Tony, try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$3" Then 'B3 changed to your list output cell
Range("D1").Select ' D1 changed to the cell where you want to go
End If
End Sub


Just paste this code in the worksheet your list is in......

hope this helps

seeya ste

Unqualified ranges refer to the active sheet when the code is in a general
module, but when you have it behind a worksheet, those unqualified ranges refer
to the sheet that own the code.

so Range("c38") still refered to the sheet that held the code--and you can't
select a range on a sheet that isn't selected.

Try it this way:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$J$13" Then
With Worksheets("SGE")
.Activate
.Range("C38").Select
End With
End If
End Sub



Tony wrote:

Hi Ste Mac

Thank you for your help but I need a bit more of your assistance if possible.

Following your suggestion I have created the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$J$13" Then
Worksheets("SGE").Activate
' Cells(38, 3).Select
Range("C38").Select
End If
End Sub

After changing selection of my list selection moves to cell C38 but I am
getting the following error message:

Run-time error '1004'
Select method of Range class failed

What is wrong and how I can fix it.

Thank you for your help.

Regards,

Tony

"ste mac" wrote:

Hi Tony, try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$3" Then 'B3 changed to your list output cell
Range("D1").Select ' D1 changed to the cell where you want to go
End If
End Sub


Just paste this code in the worksheet your list is in......

hope this helps

seeya ste


--

Dave Peterson

Thank you for help. Now all is working just fine.

Tony

"Dave Peterson" wrote:

Unqualified ranges refer to the active sheet when the code is in a general
module, but when you have it behind a worksheet, those unqualified ranges refer
to the sheet that own the code.

so Range("c38") still refered to the sheet that held the code--and you can't
select a range on a sheet that isn't selected.

Try it this way:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$J$13" Then
With Worksheets("SGE")
.Activate
.Range("C38").Select
End With
End If
End Sub



Tony wrote:

Hi Ste Mac

Thank you for your help but I need a bit more of your assistance if possible.

Following your suggestion I have created the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$J$13" Then
Worksheets("SGE").Activate
' Cells(38, 3).Select
Range("C38").Select
End If
End Sub

After changing selection of my list selection moves to cell C38 but I am
getting the following error message:

Run-time error '1004'
Select method of Range class failed

What is wrong and how I can fix it.

Thank you for your help.

Regards,

Tony

"ste mac" wrote:

Hi Tony, try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$3" Then 'B3 changed to your list output cell
Range("D1").Select ' D1 changed to the cell where you want to go
End If
End Sub


Just paste this code in the worksheet your list is in......

hope this helps

seeya ste


--

Dave Peterson