Can you use a pull-down to execute a search on the selected pulldown value?

[glennpc]

Is there a way in VBA to have a pulldown with values like 123, 456, 999, 23, 459, etc. and when you select one, the macro takes the value and looks for the first time that value appears on the current worksheet or any other worksheet in the workbook, and takes the user there as a result? In other words, selecting the value sets off a FIND operation.


User goes to the pulldown in a cell, selects 123. The system then takes the user to the next worksheet to cell A97 where the value 123 is.

 

[Luke M]

Sure thing. This should help you out.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim foundRange As Range
Dim firstAddress As String

'Cell with dropdwon
Set MyRange = Range("A1")

If Intersect(Target, MyRange) Is Nothing Then Exit Sub 'didn't change
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each ws In ThisWorkbook.Worksheets
Set foundRange = ws.Cells.Find(MyRange.Value)
If foundRange Is Nothing Then GoTo nextSheet
firstAddress = foundRange.Address
If firstAddress = "" Then GoTo nextSheet
If foundRange.Address <> MyRange.Address Then
GoTo Success
End If
Do
Set foundRange = ws.Cells.FindNext(foundRange)
Loop Until foundRange.Address = firstAddress Or foundRange Is Nothing

nextSheet:
Next ws

If Not foundRange Is Nothing Then
'We found it!
Success:
ws.Select
foundRange.Select
Else
MsgBox "Value not found"
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

[/pre]

 

[glennpc]

I created a new module in the VBE and pasted your code into it. But when I go to View Macros to select it to run it, it is not listed. How can I get to run your code?

 

[glennpc]

I think I got it to work. Your code needs to be attached to the sheet (rightclick worksheet tab and then View Code-- I pasted it there.) It isn't found on the View Macros list because it is running as soon as you enter the sheet.


I did get an error on For Each ws It didn't recognize the ws. So I added


Dim ws as Worksheet. This seemed to work.


Thanks For your help! This is great code.

 

[glennpc]

One more thing-- when it takes you to the page with the cell that you were looking for, a lot of times, that cell is not on the screen-- you need to scroll to get it (the column its in is highlighted.) Is there a way to make the found cell always be displayed at the top of the screen?


I set my options to check the Lotus transition key but that had no effect.

 

[Luke M]

Oops, sorry about that. We just need to turn on the ScreenUpdating sooner, like so:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim foundRange As Range
Dim firstAddress As String
Dim ws As Worksheet

'Cell with dropdwon
Set MyRange = Range("A1")

If Intersect(Target, MyRange) Is Nothing Then Exit Sub 'didn't change
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each ws In ThisWorkbook.Worksheets
Set foundRange = ws.Cells.Find(MyRange.Value)
If foundRange Is Nothing Then GoTo nextSheet
firstAddress = foundRange.Address
If firstAddress = "" Then GoTo nextSheet
If foundRange.Address <> MyRange.Address Then
GoTo Success
End If
Do
Set foundRange = ws.Cells.FindNext(foundRange)
Loop Until foundRange.Address = firstAddress Or foundRange Is Nothing

nextSheet:
Next ws

'Moved to here, so the screen actually shows the right cell!
Application.ScreenUpdating = True

If Not foundRange Is Nothing Then
'We found it!
Success:
ws.Select
foundRange.Select
Else
MsgBox "Value not found"
End If

Application.EnableEvents = True

End Sub

[/pre]

 

[Lymm]

There is a useful little search box mentioned over at

http://windowssecrets.com/forums/showthread.php/151221-VB-Corner-for-Excel-Learn-Visual-Basic-VB-Code-Repository

Posts 63 and 70.

Please delete this post if it is inappropriate.