Results 1 to 3 of 3

Thread: Excel 2010 Active X Combobox, Selecting Sheets

  1. #1

    Excel 2010 Active X Combobox, Selecting Sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Just curious which other sites you posted this too, and if you have an answer yet.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    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.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •