+ Reply to Thread
Results 1 to 14 of 14

Combobox2 dependent on Combobox1

Hybrid View

  1. #1
    Registered User
    Join Date
    11-08-2011
    Location
    bergen county, NJ
    MS-Off Ver
    Excel 2007
    Posts
    72

    Combobox2 dependent on Combobox1

    Hi, and thanks for reading !
    I've been constructing a pretty big userform in the attempt to automate grade recording for my small private school. Through the help of this forum, I've learned so much, and am so appreciative of all your help !

    The one thing I can't seem to get to work, even when i replicate examples from here is to get a combobox to display certain choices depending on the selection from another combobox.

    I'm attaching a screen shot of the info in cells the way i'd like it to be displayed in the userform. I've intentionally put the data in cells this way, as I've seen working examples of what i want to do, where the author put his data in like this.

    Combobox1 : the teacher selects his/her name
    combobox2 : the classes that teacher assigns grades for come up

    It seems like this should be relatively simple, but i can't get anything even close to a working model.

    any help would be much much appreciated !
    Attached Images Attached Images
    Last edited by teacher_rob; 11-19-2011 at 10:06 AM.

  2. #2
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    Re: Combobox2 dependent on Combobox1

    I was looking around on google but can't find the code to make the combobox change based on an event.

    Could you make one userform, get and save the value entered, and generate the next userform based on the first?

  3. #3
    Registered User
    Join Date
    11-08-2011
    Location
    bergen county, NJ
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Combobox2 dependent on Combobox1

    both comboboxes are on the same userform.

    we have to assign a grade for each student in the class, after each class, based on participation...

    after several different incarnations, i've figured that the quickest way to do this is via 1 userform

  4. #4
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,060

    Re: Combobox2 dependent on Combobox1

    hi teacher,
    see at this example from this forum!
    Attached Files Attached Files
    Last edited by john55; 11-18-2011 at 12:15 PM.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  5. #5
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    Re: Combobox2 dependent on Combobox1

    In VBA userforms have a Click event.

    Private Sub UserForm_Click()
    
        'REPLACE NEXT LINES WITH WHAT YOU WANT TO HAPPEN.
        Me.Height = Int(Rnd * 500)
        Me.Width = Int(Rnd * 750)
    
    End Sub
    Saw Johns post. His is a better way to do it.

  6. #6
    Registered User
    Join Date
    11-08-2011
    Location
    bergen county, NJ
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Combobox2 dependent on Combobox1

    that is the example i was referring to when i said i can't seem to replicate it...

    i want to use the first sheet to have all the teacher and class info, and then i want to hide it. the rest of the sheets in the workbork are all designated for each student.

  7. #7
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,060

    Re: Combobox2 dependent on Combobox1

    as an option
    see Sheet1 properties, in the left side, set visible to veryhidden and add yr button to Sheet2.
    or
    Private Sub UserForm_Initialize()
        Set sht = Sheet1
        'loads the first combobox
        With sht
            Me.ComboBox1.List = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value
        End With
        Sheets("Sheet1").Visible = xlVeryHidden
    End Sub
    Last edited by john55; 11-18-2011 at 12:47 PM.

  8. #8
    Registered User
    Join Date
    11-08-2011
    Location
    bergen county, NJ
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Combobox2 dependent on Combobox1

    Me.cboclass.List = .Range(.Cells(2, lCol), .Cells(.Rows.Count, lCol).End(xlUp)).Value
    this gives me an error... i have the list of teacher names coming up, but an error happens and i get sent to the code... debugging gives me the yellow highlight over this line

  9. #9
    Registered User
    Join Date
    11-08-2011
    Location
    bergen county, NJ
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Combobox2 dependent on Combobox1

    ok, disregard that last post... i figured out a few naming problems i had when i copied and pasted the code... i'm not getting any error messages now, it compiles fine, and there is no break in the code when i run the userform...

    that said, after i select a teacher name from combobox1, nothing happens. combobox2 doesn't populate at all...

  10. #10
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,060

    Re: Combobox2 dependent on Combobox1

    hi teacher,
    well, it's very difficult to say ...
    just try to make a test file with some of yr data, with yr real comboboxes name (ex. cboclass as ComboBox2) and see if this one is populated when you choose the teacher in the first combobox.
    or add here yr entire code for this issue.

    hope we can solve it

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Combobox2 dependent on Combobox1

    Here's an example that I use
    Attached Files Attached Files
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  12. #12
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,060

    Re: Combobox2 dependent on Combobox1

    or as an option see if this one solves yr issue.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-08-2011
    Location
    bergen county, NJ
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Combobox2 dependent on Combobox1

    John,
    I can't thank you enough !!!! works perfect !!!!
    Marking this baby as SOLVED !!!

    -rob

  14. #14
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,060

    Re: Combobox2 dependent on Combobox1

    hi Rob
    glad it helped!
    Last edited by john55; 11-19-2011 at 10:12 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1