Prev: formula to VBA
Next: Ignore error
From: J.W. Aldridge on 14 May 2010 10:06
On page one. I have various full names listed throughout the worksheet
in various places.

When a user clicks on a cell with a name in it, I want them to be
directed to sheet 2 where the same name (cell values) is found in
column A. (There will be profile info starting with their name in
column A.)

From: Rick Rothstein on 14 May 2010 10:37
I think the following may do what you are looking for. Right click the tab
at the bottom of Sheet1 (which is what I assumed you meant by "page one"),
select View Code from the popup menu that appears and then copy/paste the
following into the code window that appeared...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
If Target.Count > 1 Or Target.Value = "" Then Exit Sub
Set Cell = Worksheets("Sheet2").Columns("A").Find(Target.Value, _
LookAt:=xlWhole, MatchCase:=False)
If Not Cell Is Nothing Then
Worksheets("Sheet2").Activate
Cell.Select
End If
End Sub

--
Rick (MVP - Excel)



"J.W. Aldridge" <jeremy.w.aldridge(a)gmail.com> wrote in message
news:e8a1e189-bc64-430a-b8a4-fdeb18e8d916(a)a16g2000vbr.googlegroups.com...
> On page one. I have various full names listed throughout the worksheet
> in various places.
>
> When a user clicks on a cell with a name in it, I want them to be
> directed to sheet 2 where the same name (cell values) is found in
> column A. (There will be profile info starting with their name in
> column A.)
>
From: J.W. Aldridge on 14 May 2010 11:11
Thanx...

Works perfect on a new workbook where the sheets are "Sheet 1" and
"Sheet 2".
However, when using my existing workbook, it only gives me the
following error:
Run time error 9
Subscript out of range

Would the code suggested be specific to it being the first and second
sheet?
I tried replacing the "sheet 2" with the actual sheet names and it
didnt work for me.
From: J.W. Aldridge on 14 May 2010 11:41
must be something i did...

transfered over to blank sheet and it works.

will make do.


thanx again!!!!
From: Rick Rothstein on 14 May 2010 11:55
Since the code goes in the worksheet module where the names are scattered
all-about ("page one", Sheet1 or whatever you called it), that worksheet's
name will not appear in the code. What you referred to as Sheet2 in your
original post (the sheet that contains the list of names in Column A)
appears twice in my code... you have to replace both occurrences with your
worksheet's actual name.

--
Rick (MVP - Excel)



"J.W. Aldridge" <jeremy.w.aldridge(a)gmail.com> wrote in message
news:d7d99b0e-ae6f-4bde-af01-d730a870faef(a)h9g2000yqm.googlegroups.com...
> Thanx...
>
> Works perfect on a new workbook where the sheets are "Sheet 1" and
> "Sheet 2".
> However, when using my existing workbook, it only gives me the
> following error:
> Run time error 9
> Subscript out of range
>
> Would the code suggested be specific to it being the first and second
> sheet?
> I tried replacing the "sheet 2" with the actual sheet names and it
> didnt work for me.

 | 
Pages: 1
Prev: formula to VBA
Next: Ignore error