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
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:
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.