Dropdown Menu That Jumps To Corresponding Row After Selection

[IH8XEL]

Ok, I just discovered VBA in Excel so take it easy on me guys.

I'm trying to create a dropdown menu that is full of dates. I know how to create the menu and use data validation and all that. I would like to know how to enable a scroll function however instead of having to click on the direction arrows so if you could tell me how to do that I'd appreciate it.

My real question though is how to make the worksheet automatically scroll to a specific row that will correspond with the selection of a date in the dropdown menu.

Can someone point me to a tutorial for that specific code?

 

[MixedUpExcel]

Hi,

Does it have to be Data Validation that you are using or would you be open to using an Active X Drop Down Box?

The reason I ask, is that I am currently using some code which allows a user to make a selection in an Active x Drop Down Box and then that selection put the line to the top.

Thanks.

Simon

 

[IH8XEL]

I'm not sure how to make an Active X Drop Down Box. But if you feel like walking me through it I'd be open to it.

 

[MixedUpExcel]

OK... I hope my descriptions are ok as I can't post screenshots.. but I'll give it a go.

To test this, this is what I've just created... I'll warn you in advance.. I'm still trying to get the trigger (when you change a value in a cell) to work to activate the macro.. it's working on mine but I'm trying to see if I'm missing something on my test version.. I'll sort that out at the end.. anyway:

To test this.. put a list of names in cell range K2 to K17
Highlight the range K2:K17 and name that range "names"

Copy all of the names K2:K17 and paste them in B6 to B21 - name the range B6 to B21 "allnames"

Go to Developer Tab>Controls>Insert> In the Active X Controls - Chose Combo Box

Using your mouse, draw out a small box in cells B3 to D3

Make sure you are in 'Design Mode' in the Controls Section under the Developer Tab

Right click on your new Combo Box and then select Properties

In the blank box next to LinkedCell put B2
In the blank box next to ListFillRange put "names" without the quotes

 

[MixedUpExcel]

The above should get you up and running with a Combo Box.

Next make sure you have a Freeze Pane (under View section) between Rows 4 and 5

Then what you need to do is create a macro so that when you select a name... it will find the name in the range B6 to B21 and move that name to just below the Freeze Pane

Right Click on the tab name at the bottom of the active sheet and select 'View Code'

Put the following code into the white code box which is now open

Private Sub FindMeNow()


Dim searchval1 As Long
Dim searchval2 As Long
Dim rng As Range
Dim productrng As Range


Set rng = Sheet1.Range("B2")
Set productrng = Worksheets("Sheet1").Range("allnames")


    searchval1 = Application.Match(rng, productrng, 0)


    searchval2 = searchval1 + 6
    
' SHOW ROW SEARCH RESULT AS TOP VISIBLE ROW UNDER THE FREEZE PANEL


On Error Resume Next


    Range("A" & searchval2 - 1).Select


    ActiveWindow.ScrollRow = ActiveCell.Row


End Sub

 

[MixedUpExcel]

To test to make sure the above is working

Select a name - possibly half way down the list

That name should now appear in Cell B2

*The bit I have to get right now is that each time the name changes in B2, it triggers the macro*

for now.. go back to your code by right clicking sheet name on the tab and then selecting 'View Code'

At the top of the screen is a green triangle - 2 lines (pause) and a square

If you click on the green triangle.. it will run the macro and should put the name you have selected to the top of the excel screen - just under the freeze pane

 

[MixedUpExcel]

Ok got it working..

Change the first line to this:

Private Sub ComboBox1_Change()

instead of

Private Sub FindMeNow()

Let me know how you get on?

 

[IH8XEL]

That's AWESOME bro! Thank you so much. I've been googling and reading forums for almost 5 hours now. Works like a charm, just have to tailor it to my specifics.

 

[IH8XEL]

You wouldn't happen to know how to enable a scrolling feature within the combo box instead of manually clicking on the direction arrows to navigate through the box would you?

 

[MixedUpExcel]

Pleased it worked.

Not sure what you mean by enable scrolling feature within the combo box?

There are quite a few things you can do with the combo box - some are set in properties - where you put the named range and cell link, others may require macros - I've got a mixture of both on the workbook I've been using for the above code.

Can you give me more detail in how you want it to work and I'll see if it matches any uses I'm already putting in play in mine?

Thanks.