Just curious which other sites you posted this too, and if you have an answer yet.
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.
Please note I have posted this on other blogs.Private Sub ComboBox1_Click()If Me.ComboBox1.Value <> "" Then Sheets(Me.ComboBox1.Value).Activate
ComboBox1.Value = ""
End Sub
Just curious which other sites you posted this too, and if you have an answer yet.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
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
Bookmarks