Add a VBA module to your workbook. In it, add:
Option Explicit
Global BeenThereAlready As Boolean
In the ThisWorkbook module of your workbook, add:
Private Sub Workbook_Open()
BeenThereAlready = False
End Sub
Finally, change the Worksheet_Change event code you already have to the
following:
Private Sub Worksheet_Change(ByVal Target As Range)
If (Range("E23").Value = "RFP") And _
(BeenThereAlready = False) Then
Sheets("RFP").Activate
Sheets("RFP").Range("A1").Select
BeenThereAlready = True
End If
End Sub
This will take the user to the RFP sheet only the first time he/she enters
RFP in cell E23.
Hope this helps,
Hutch
"bluegrassstateworker" wrote:
> I have the below code in use (thanks, L. Howard Kittle ) and noticed
> that while my navigation is correct, every time I hit the enter key in
> subsequent cells, I am annoyingly navigated to the RFP worksheet (which
> I only need to do so once). The code is below:
> *********
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Range("E23").Value = "RFP" Then
> Sheets("RFP").Activate
> Sheets("RFP").Range("A1").Select
> End If
> End Sub
> *********
> My thought was to define a variable and include in the If statement to
> read something like:
> IF Range(E23) .... AND VARIABLESET=T Then
> But, since I would initialize the variable, it would be reset every
> time the spreadsheet is open. Anyone have a thought to keep this
> routine from running after a value is inserted?
>
>
|
Similar Threads
