CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10

Thread: trying to get combobox to pass variable

  1. #1
    Join Date
    Mar 2011
    Posts
    5

    trying to get combobox to pass variable

    I'm new at programming in vba, so this is probably an easy fix that I'm just missing. I've created a combobox, filled it out with an array of tab names from a sheet, and when the user selects a name it should activate that tab. The first part works okay, but I can't figure out how to get the combobox to pass the name variable to the sheets.activate. Any help would be greatly appreciated, code as it stands is as follows:

    Code:
    Sub avlcompare()
    
    'open the workbook with the tabs
    
    Workbooks.Open Filename:="X:\AlternatePart Approvals2.xls"
    
       Dim wkshtnames()
       Dim wksht As Worksheet
       Dim i As Long
    
           'This is where I load the tabs into an array
    
        i = 0
    
        For Each wksht In ActiveWorkbook.Worksheets
            i = i + 1
            ReDim Preserve wkshtnames(1 To i)
            wkshtnames(i) = wksht.Name
        Next wksht
    
    'create the combobox
    
        Set combobox0 = ActiveSheet.DropDowns.Add(289.5, 89.25, 121.5, 15.75)
    
    'fill the combobox
    
            For i = LBound(wkshtnames) To UBound(wkshtnames)
              combobox0.AddItem wkshtnames(i)
            Next i
       
    'act on combobox selection... better way?
    
       Selection.OnAction = "'avlCompare.worksheettest""  combobox0.value  '"
    
    End Sub
    
    
    Sub workSheetTest(ByVal combobox0 As Variant)
    
    'next line is where I keep getting the error "object required"
    
       Sheets(combobox0.Value).Activate
    
    End Sub
    Again, any help is greatly appreciated, I've tried a dozen different ways of changing dims, removing the second function, recording macros to see what excel does, etc... and I keep getting different errors for each that all boil down to "You're doing it wrong"

    -Dan

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: trying to get combobox to pass variable

    Probably can't activate a VARIANT. Change it to combobox
    Code:
    ByVal combobox0 As Variant
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Mar 2011
    Posts
    5

    Re: trying to get combobox to pass variable

    Tried that, it then gives me a compile error: Type mismatch. Is this the right way to do this?

    -Dan

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: trying to get combobox to pass variable

    Is that supposed to be a BUTTON CLICK EVENT? Needs more params
    http://support.microsoft.com/kb/829070
    Last edited by dglienna; March 22nd, 2011 at 01:33 PM.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Mar 2011
    Posts
    5

    Re: trying to get combobox to pass variable

    I was hoping to avoid userforms, since my goal was to keep everything visible in one macro and, to be fair, having never used userforms they confuse me since I can't follow the code from the userform to the main macro. But that's just me, if that's the only way to do it I'll have to buckle down and figure them out.
    The code I gave wasn't supposed to be a button_click event as I understand it, just a selection from the drop-down list that passes the selection to activate that particular worksheet. If it could be done all in one macro that would be great, but when I tried to do it that way it wouldn't wait for a selection, and the only way to cause it to wait for a selection was the onAction command, which requires a sub-macro (again, new at programming in vba, so forgive me if I get the terminology wrong), or it would break saying invalid selection (since there was no selection) and then you could select something from the dropdown box and continue running the macro, which wouldn't work too well for normal usage.
    So are userforms the only way to do this correctly? Thanks for your help, it's greatly appreciated. In the meanwhile I'll start reading that article.

    -Dan

  6. #6
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: trying to get combobox to pass variable

    Well one thing seems to be wrong:
    Code:
       Selection.OnAction = "'avlCompare.worksheettest""  combobox0.value  '"
    
    End Sub
    
    
    Sub workSheetTest(ByVal combobox0 As Variant)
    
    'next line is where I keep getting the error "object required"
    
       Sheets(combobox0.Value).Activate
    
    End Sub
    You are already passing combobox0.value as a parameter.
    In your submacro you try to take the parameter's .value property, which does not exist, because you do not pass the combobox, but already the value.
    So your submacro must look like:
    Code:
    Sub workSheetTest(ByVal SheetNum As Variant)
    
    
       Sheets(Sheetnum).Activate
    
    End Sub
    The question is, instead of a submacro as you call it, why not entering the selection statement directly (try if it works)
    Code:
    Selection.OnAction = "'Sheets(" & combobox0.value & ").Activate'"
    (I'm not sure if I did the quotes right in the above statement. Am not so familiar with VBA for Excel.)
    But the above change of the codeline in your submacro should work.
    Last edited by WoF; March 22nd, 2011 at 07:15 PM.

  7. #7
    Join Date
    Mar 2011
    Posts
    5

    Re: trying to get combobox to pass variable

    I'm betting on more than just one thing being wrong :P Thanks for your reply, I tried the first fix you gave, and it keeps giving me Run-time error 9, subscript out of range. When I check the value for sheetnum in the sheets(sheetnum).Activate point it's reading "combobox0.value" being stored in sheetnum. Seems like that should work? Unless it's losing the value in combobox0.value somewhere in the passing to the submacro?

    Ideally I'd love to keep it all in one macro, your second suggestion would be genius if I could make it work. I tried several different versions of the code with different quotations, most give me a compile error: method or data member not found. The code as you wrote it give me run-time error 424, object required.

    I'll keep playing with those, it seems like it's something small I'm missing, and feels like I'm so close. I need to read up more on the selection.onaction to see what it can do, I thought it was only for calling submacros but if it can just pass on a combobox selection to activate the worksheet I need that would be perfect. Is there a way to make code wait for a selection from a combobox/dropdown list other than selection.OnAction?

    Thanks again for all y'all's help, were it not for this forum and the help it offers I'd probably be tossing a computer out a window right about now

  8. #8
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: trying to get combobox to pass variable

    Well, yes. The combobox has lots of events firing.
    combobox0_Click() for instance will fire when you do a selection.
    combobox0_Change() fires as soon as the value changes (also from manual input, if allowed)

    If you enter design mode and double click your combobox you get to the coding section where you can immediately select one of the comboboxes events to write code to it.

    Maybe something like this works for you:
    Code:
    Private Sub ComboBox0_Click()
      Sheets(ComboBox0.ListIndex).Activate
    End Sub
    I'm not sure if the Worksheet indices start with 0 or one. ListIndex, as I recall, starts with item 0. So if Sheets start to count with item 1 you have to add 1 to ComboBox0.ListIndex

  9. #9
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: trying to get combobox to pass variable

    In fact I have just tested it. The Sheets() array starts at index 1, so you have to add 1.
    Just write that code to the Click() event of your combobox0:
    Code:
    Private Sub ComboBox0_Click()
      Sheets(ComboBox0.ListIndex + 1).Activate
    End Sub
    This will work perfectly.

  10. #10
    Join Date
    Mar 2011
    Posts
    5

    Re: trying to get combobox to pass variable

    That's what it was, the index starting at 1 instead of 0! I think when I was doing it I was just passing the original value starting at 0, which gave it that error. Thanks, I think that will do it now

    -Dan

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)