Excel 2010 Active X Combobox, Selecting Sheets

[Rafik]

Hi,
Am a newbie in trying to automate Excel.
I have a workbook with 50 worksheets and a menu sheet.
On the menu sheet I created a Actixe X combobox filling it
with the fifty sheets.
I wish to be able to select from the combo box to go to a
particular sheet. I am using the following code which works fine
if I select and click but when I enter an alphabet it goes direct
to the first sheet which starts with what is entered in the combo
box instead of giving me options to select.

Private Sub ComboBox1_Click()If Me.ComboBox1.Value <> "" Then Sheets(Me.ComboBox1.Value).Activate
ComboBox1.Value = ""
End Sub

Please note I have posted this on other blogs.

[Ken Puls]

Just curious which other sites you posted this too, and if you have an answer yet.

[Rafik]

Just curious which other sites you posted this too, and if you have an answer yet.

MrExcel

answer
As far as I'm aware, you can't set up AutoComplete with a combo box. However, through a little workaround, you can utilize Data Validation, Autocomplete, and a Worksheet_Change() macro to accomplish what you need.

The workaround involves placing the list of all worksheets above the input cell (i.e. since you have 50 worksheets, you might place the worksheet names in cells B1:B50, and have the input cell as B51). Then you use Data Validation for cell B51, and have it Allow:List (B1:B50).

Once that is in place, the first phase of this is complete, setting up a drop-down box that you can also use to type and get an auto-complete feature.

For the second phase, we need to add a Worksheet_Change() macro:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("B51")) Is Nothing And Range("B51").Value <> "" Then Sheets(Target.Value).ActivateEnd IfEnd Sub
To use a Worksheet_Change macro, right-click on the sheet tab you want this to run on, click "View Code", and paste into that window.

Hope this helps!


ITtoolbox

answer
When you say "select an alphabet" do you mean that you want just to type the first few characters of the sheet name required and the system will "auto-fill" the rest of the name or show you the list of matches?

If so that should be the default case with a combo-box. Otherwise I have not understood your question.


dreamincode

answer
I'm sorry to say that I cannot get these Excel combo boxes to behave. This is what I've been playing with, from both the click and change events:

view source
print?

01	Private Sub ComboBox1_Click()

02	Dim theList As Variant

03	Dim x As Integer

04	theList = ComboBox1.List

05	For x = 0 To UBound(theList)

06	If theList(x, 0) = ComboBox1.Value Then

07	Sheets(ComboBox1.Text).Activate

08

Exit For

09

End If

10

Next x

11

End Sub

[I don't think extracting the list is really required, as we have .Text and .Value, and other properties, to play with.]

But I think it needs to work in combination with a number of properties of the combo-box:

AutoWordSelect
EnterFieldBehaviour
MatchEntry
MatchRequired

second answer
If it were me.. I would ignore the change and click events and just have a button next to the combo-box to click on (when ready) />

view source
print?

1	Private Sub CommandButton1_Click()

2	On Error Resume Next

3	Sheets(ComboBox1.Value).Activate

4	If Err.Number <> 0 Then

5	MsgBox "No such sheet"

6

End If

7	On Error GoTo 0

8

End Sub

I think this is much better

Third answer
Sorry, I won't pursue this question myself: I know it won't work reliably and it's more trouble than it's worth.

Ken
I havent tried the other methods as yet
I think its all in the code
Your help will be very much appreciated
Rafik