automate the finding of a value and highlighting the corresponding row

[batman07]

We are in the process of bidding schedules to 500+ employees. I have
created a workbook that takes care of the what's available, etc., but
cannot figure out or find a way to take the schedule listed on the
'Main' sheet, click a command button and have excel go to the schedule
number on the sheet 'Postings' and/or highlight the row containing the
selected schedule.

I've discovered that a recorded macro will not allow me to paste a
value in the Find dialog box, and then locate the schedule on the other
sheet.

Any and all help will be GREATLY appreciated!!!

 

[Max]

Here's a formulas play
which provides a close navigational functionality

A sample construct is available at:
http://www.savefile.com/files/9536205
AutoHyperlinking_batma_wksfn.xls

In sheet: Main

Assume StaffIDs are listed in col A, from row2 down

StaffID
1111
1112
1113
1114
1115
etc

In sheet: Postings,

StaffIDs are listed in col A &
Schedule#s are listed in col D (col D is the lookup col),
with data from say, row4 down

StaffID FieldX FieldY Schedule#
1114 Data1 Data11 1
1115 Data2 Data12 2
1113 Data3 Data13 3
1111 Data4 Data14 4
1112 Data5 Data15 5
etc

Note that the StaffIDs above are intentionally scrambled to be
different from the order in Main. This is for illustration purposes
when we check that clicking on the hyperlinks we're going to create in
Main does jump to the correct cells in Postings.

In Main
---------
Put a label in C1: Schedule#

Put in C2:
=HYPERLINK("#"&CELL("address",INDIRECT("'Postings'!D"&MATCH(A2,Postings!A:A,0))),INDIRECT("'Postings'!D"&MATCH(A2,Postings!A:A,0)))
Copy C2 down

This will create hyperlinks in C2, C3, etc which extract/display the
schedule #s (matching the StaffIDs) from Postings as the friendly names
in the cells. And when you click on the hyperlinks, they'll bring you
to the correct cells in col D in Postings (Schedule# col)

 

[Max]

Just to add on a little ...
here's a way using a sub by Bob Phillips
to auto-highlight the active row in the destination sheet: Postings

Revised sample available at:
http://www.savefile.com/files/6477199
Autohyperlinking n row highlight in destination sheet.xls

In sheet: Postings,

Right-click on sheet tab > View code
Paste the sub below (adapted from a post by Bob Phillips)
into the code window
Press Alt+Q to get close/get back to Excel

The code will auto-highlight the active row in Postings
(note however that "undo" will be disabled in the book)

'--------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Bob Phillips (slightly adapted)
Cells.FormatConditions.Delete
With Target
With .EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End With
End Sub
'-------

 

[batman07]

Guys, this some great work! I am truly thankful for the explanation
and sample files. I will certainly be able to use it. However, I
failed to mention that on the 'Main' sheet, I have both the employee
name and schedule number set up as list validation. I hyperlinked the
cells that populate the list, but apparently hyperlinks do not carry
over. The reason I chose this method is because 500+ names and
schedule numbers can create a very busy sheet. The columns with the
data are hidden and depending on what is selected in the list boxes,
vlookups populate other information such as who the leader is, the
shift times of the schedule, etc...

If I could just click a command button on the 'Main' sheet and have the
name in cell C8 populate the list on sheet 'Schedules' by using the
schedule ID in 'Main', cell e8, it would be perfect.

I cleaned up the file and tried to upload a sample, but the firewall
here at work disallow's it - I can email the file however.

 

[Max]

It might be out of my depth here (especially with vba) <g>,
but I suppose I could take a look.
Send a copy over to: (e-mail address removed)
Drop a note here to let me know you've sent

Btw, a much better newsgroup to post vba questions is:
microsoft.public.excel.programming
so you might want to bear in mind to try there as well later
(think you've posted in that newsgroup before)

 

[batman07]

Max, I sent you the file w/a brief explanation. Again, thank you for
your help.

 

[Max]

.. To recap, I simply want to select a schedule # from the list
validation menu on 'Main', E8, and either click a hyperlink or command
button to take me to the corresponding schedule in the 'Schedules'
sheet - to where I can enter the name of the employee selecting the
schedule (column D). **Or have it copy the name in 'Main', C8
and paste in 'Schedules', column D** - but I know this can/will be tricky.

The sub below, placed in a regular module
& assigned to a forms button: "Place Name" in Main
seems to do the job** adequately <g>.

Implemented sample at:
http://www.savefile.com/files/5389671
Schedule_Posting.xls

Sub PlaceName()
Dim rng As Range, code As Variant
Application.ScreenUpdating = False
code = Worksheets(1).Range("e8")
Set rng = Worksheets(2).Range("b4:b77")
For i = 1 To rng.Count
If rng.Cells(i).Value = code Then
rng.Cells(i).Offset(0, 2) = Worksheets(1).Range("c8")
End If
Next i
Worksheets(1).Activate
MsgBox "Name placed in Schedules"
Application.ScreenUpdating = True
End Sub

 

[batman07]

Max, this is awesome - works perfect! Your time, knowledge, and
flexibility are greatly appreciated.

The sample file and explanations are first rate - I cannot thank you
enough!


My best regards,
Jason

 

[Max]

Glad it helped, Jason !
Thanks for feedback ..