GOTO another sheet
 

Hi All..........

I have a cell E41 on Sheet1, with a validation list drop-down. One of the
selections on the list is "Custom". Is it possible that whenever that
selection is made that another sheet called "GraphicsForm" could be made
active instead of Sheet1?.....and then when work is done on the
"GraphicsForm" sheet and the user switches back to Sheet1, it will stay
there and not automatically go back to "GraphicsForm" sheet again,
until/unless E41 is changed away from "Custom" and then changed back again
to "Custom".?

TIA
Chuck, CABGx3

GOTO another sheet
 

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hi All..........

I have a cell E41 on Sheet1, with a validation list drop-down. One of the
selections on the list is "Custom". Is it possible that whenever that
selection is made that another sheet called "GraphicsForm" could be made
active instead of Sheet1?.....and then when work is done on the
"GraphicsForm" sheet and the user switches back to Sheet1, it will stay
there and not automatically go back to "GraphicsForm" sheet again,
until/unless E41 is changed away from "Custom" and then changed back again
to "Custom".?

TIA
Chuck, CABGx3

GOTO another sheet
 

Hi Chuck,

You can use the Worksheet_Change event routine to do this:

Private mbWent As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, _
Range("E41")) Is Nothing Then
If Not mbWent And Range("E41").Value = _
"Custom" Then
mbWent = True
Application.Goto Worksheets("GraphicsForm" _
).Range("A1")
End If
End If
End Sub

mbWent keeps track of whether or not a user has gone to the GraphicsForm
sheet yet. But it is reset each time the project starts (each time the
worksheet is opened) or whenever the VBA project gets reset (can happen
unintentionally). If you really want it to be a one-time occurance, then
you could use a hidden defined name to do it, which would look more like
this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bWent As Boolean

If Not Application.Intersect(Target, _
Range("E41")) Is Nothing Then
On Error Resume Next
bWent = Len(ThisWorkbook.Names("hbWent" _
).RefersTo)
On Error GoTo 0
If Not bWent And Range("E41").Value = _
"Custom" Then
ThisWorkbook.Names.Add Name:="hbWent", _
RefersTo:="=True", Visible:=False
Application.Goto Worksheets("GraphicsForm" _
).Range("A1")
End If
End If
End Sub

This code goes "behind" the Worksheet object that contains the validated
cell E41. To get there, you can right-click the sheet tab and select View
Code or go to the VBE (Alt+F11) and double-click the Worksheet icon under
Microsoft Excel Objects in the Project window.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


CLR wrote:
Hi All..........

I have a cell E41 on Sheet1, with a validation list drop-down. One
of the selections on the list is "Custom". Is it possible that
whenever that selection is made that another sheet called
"GraphicsForm" could be made active instead of Sheet1?.....and then
when work is done on the "GraphicsForm" sheet and the user switches
back to Sheet1, it will stay there and not automatically go back to
"GraphicsForm" sheet again, until/unless E41 is changed away from
"Custom" and then changed back again to "Custom".?

TIA
Chuck, CABGx3

GOTO another sheet
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
If Target.Address = "$E$41" Then
If Target.Value = "Custom" Then
Worksheets("GraphicsForm").Activate
End If
End If

End Sub

right-click on the sheet tab, select View Code, and paste it in.

This won't work with XL97 if you keep the DV.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hi All..........

I have a cell E41 on Sheet1, with a validation list drop-down. One of the
selections on the list is "Custom". Is it possible that whenever that
selection is made that another sheet called "GraphicsForm" could be made
active instead of Sheet1?.....and then when work is done on the
"GraphicsForm" sheet and the user switches back to Sheet1, it will stay
there and not automatically go back to "GraphicsForm" sheet again,
until/unless E41 is changed away from "Custom" and then changed back again
to "Custom".?

TIA
Chuck, CABGx3

GOTO another sheet
 

Thank you very much, kind Sir's, Bob and Jake.........I do appreciate your
time and assistance.

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote in message
...
Hi All..........

I have a cell E41 on Sheet1, with a validation list drop-down. One of the
selections on the list is "Custom". Is it possible that whenever that
selection is made that another sheet called "GraphicsForm" could be made
active instead of Sheet1?.....and then when work is done on the
"GraphicsForm" sheet and the user switches back to Sheet1, it will stay
there and not automatically go back to "GraphicsForm" sheet again,
until/unless E41 is changed away from "Custom" and then changed back again
to "Custom".?

TIA
Chuck, CABGx3