Using combobox to find column

[Scotty9349]

I am working on a rolling 12-month budget spreadsheet. The first of the 12
months can be any month.

What I would like to do is have a combobox populate the months in order Jan,
Feb, etc. Then when the user selects say June it will select the column in
which June resides. Keep in mind that June could be anywhere in the first 12
columns.

I can populate the combobox, but the order the months are populated is
identical to the order the months are listed across the top row.

 

[Brotha Lee]

Scotty,

You can use the listindex property to find the column. As you say the order
of the months appear the same as the order in the worksheet. Let's say the
combobox is populated as follows:

March, April, May, June etc.

The listindex for March will be 1, April 2 and so on.

For example if you would like to jump to column in which May resides you
could use the following code:
cells(1,combobox.listindex).select

Regards,

Brotha lee

 

[Scotty9349]

Thanks.

Is there a way to have the combobox be Jan, Feb, Mar,... but the columns be
in a different order, i.e. Mar, Apr, May,...?

I would like to have the combobox(Jan, Feb,...) the same regardless of the
first column, i.e. Mar and still index to the corresponding column?

 

[Dave Peterson]

I used a combobox from the control toolbox toolbar.

My month abbreviations were in row 1.

This was the code behind the combobox (in the worksheet module):

Option Explicit
Private Sub ComboBox1_Change()

Dim res As Variant

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub 'nothing selected
End If

res = Application.Match(Me.ComboBox1.Value, Me.Rows(1), 0)

If IsError(res) Then
MsgBox "No month found!"
Else
Me.Cells(1, res).EntireColumn.Select
End If

End Sub

 

[Scotty9349]

Thanks. Worked wonderfully.