Dynamic Scrolling based on partial cell entry

Column A is sorted alpha numerically beginning in A3
A2 is the header
A1 is blank
TheData = dynamic named range, which is currently A2:T610
Panes frozen under the header row 2

I would like to begin typing into A1 and have TheData scroll based on what is being typed into A1.

So upon typing D into A1, the visible row under A2 becomes the first entry in column A beginning with D (possibly Row 32)

Then upon typing a second character, for example DE, the visible row under A2 becomes the first entry in column A beginning with DE (possibly Row 35)

Then upon typing a third character, for example DE3, the visible row under A2 becomes the first entry in column A beginning with DE3 (possibly Row 37)

and so on...

I don’t want to have to hit enter on A1 for this to happen, I want the scrolling to happen concurrent with the typing,
Because on hitting enter, I want to clear A1 and jump to the first visible cell under A2, which should be the data I’m looking for.

Is this possible?

Hello Carstowal,

The easiest way to accomplish this is by using a Control Toolbox ComboBox. Once you have placed the combo box on row 1, add the following code to the worksheet it is on. The code here using column "C". Change this to whichever column you are using. The combo box is named ComboBox1. Change this if your's is different.

Worksheet Macros

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S

Workbook Open Macro
Change the Sheet name, column and ComboBox name to match what you are using.

Please Login or Register  to view this content.

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

Hello carstowal,

If you post your workbook, I'll take a look to see what the problem might be. The workbook I have with this same macro functions fine. I am using Excel 2003.

Sincerely,
Leith Ross

here it is, thanks,

I have 2000 but
I tried it on another PC running 2003 and it still didn't work

Hello carstowal,

I made a correction to the Workboo_Open() event. The code to load the ComboBox was missing. The attached file works now.

Sincerely,
Leith Ross

thanks a million

I have a timer set so that the cursor goes to cell A1 after some idle time.
How can I move the cursor inside the ComboBox1 so it's ready for typing.

I can clear the ComboBox but I cannot get the cursor inside it.

In the "Scroll test v1.0.xls" the last entry in Col A, Y9220Z, is on Row 496

If I add lines or delete lines within the database, it still associates Y9220Z with Row 496 and scrolls to Row 496 when Y9 is entered into the ComboBox.

bump......

Hello carstowal,

I am catching up with emails and projects with the Forum members. I lost 10 days of work due and a lot of money due to hardware problems caused by a power surge. I was working on this problem when it happened. I was close to a solution. Placing the cursor in the ComboBox on a worksheet requires using API calls. I am not starting at zero, but it will take me a little while to catch up again.

Sincerely,
Leith Ross

This is rearing it's ugly head again for me.

I care less about putting the cursor into the ComboBox (post #8) and more about why the dynamic range doesn't update to the ComboBox when changes are made (post #9).

Any Ideas?


I don't want to get busted for double posting, see
http://www.excelforum.com/excel-prog...ddress-a1.html
I tried a workaround with no luck, (same problem, it doesn't go to the right found cell). After posting this new one I remembered trying to get help on the ComboBox method a long time ago!

These are two problems with potentially the same solution.