Automatic navigation based on cell text value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bluegrassstateworker - 27 Jul 2006 20:48 GMT

I am creating a budget spreadsheet and wish to have a macro run
automatically and navigate to a specific cell of a different worksheet
based on a determined value of a cell.

For example, Cell A5 in the MAIN worksheet uses a drop down list from a
named range (RFP, MOA, PSC ...) from another worksheet. If the value
is "RFP" then I wish to navigate automatically to cell A1 of the
REQUEST worksheet where additional information is completed related to
"RFP". I'd reverse the process on the last cell of the REQUEST
worksheet, sending the user back to the MAIN worksheet to continue on.
Any ideas??

Reply to this Message

L. Howard Kittle - 27 Jul 2006 21:49 GMT

Perhaps something like this event macro in the worksheet named "Main".

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A5").Value = "REP" Then
Sheets("Request").Activate
Sheets("Request").Range("A1").Select
End If
End Sub

And this event macro in the worksheet named "Request". Change column and
row to suit your last cell in the range REP.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 10 Then
Sheets("Main").Activate
Sheets("Main").Range("A5").Select
End If
End Sub

HTH
Regards,
Howard

>I am creating a budget spreadsheet and wish to have a macro run
> automatically and navigate to a specific cell of a different worksheet
[quoted text clipped - 7 lines]
> worksheet, sending the user back to the MAIN worksheet to continue on.
> Any ideas??

Reply to this Message

bluegrassstateworker - 28 Jul 2006 14:42 GMT

That did it! Thanks! Since I am likely to be moving and manipulating
the location of these cells, I am going to try to give them a name
rather than using the absolute cell reference. -- A

> Perhaps something like this event macro in the worksheet named "Main".
>
[quoted text clipped - 30 lines]
> > worksheet, sending the user back to the MAIN worksheet to continue on.
> > Any ideas??

Reply to this Message