PDA

View Full Version : Scroll to column via Combobox



Emoncada
05-08-2012, 11:34 AM
I have a spreadsheet with multiple column headers.
I have Column A with a Combobox that has a list of all headers.
I also have frozen panels so Column A and Row 1 always are showing.

I would like for when I select a header name from the combobox for the column next to A move to that column.

So If I select Desktops from the Combobox and Desktops header is in Column H then I will see Column A then H next to it.

I know you can use something like this
ActiveWindow.SmallScroll ToRight:=5

But the problem with that is it moves over from where it is at that moment not from Column B.

Any suggestions?

Tinbendr
05-08-2012, 12:18 PM
Private Sub ComboBox1_Change()
Worksheets(1).Cells(1, Worksheets(1).ComboBox1.ListIndex + 2).Select
End Sub

Emoncada
05-08-2012, 02:13 PM
Thanks Tinbendr, but that's not what Im trying to accomplish.
I would like for the columns to move mainly for viewing purposes, I don't want to select the column.

So selected option in combobox will be right next to column A, but I will still remain in ComboBox

Tinbendr
05-08-2012, 07:25 PM
OK, I see.

Try this.

Private Sub ComboBox1_Change()
ActiveWindow.SmallScroll toleft:=7
ActiveWindow.SmallScroll ToRight:=Sheet2.ComboBox1.ListIndex

End Sub

First line returns the column to the far left. (There's probably a better way to do this, but i couldn't find it.)
Second line moves the scroll to the right equal to the index in the combobox.

Adjust as required.

bothbest
05-08-2012, 07:59 PM
good to learn it..