+ Reply to Thread
Results 1 to 3 of 3

VBA Combobox dependent pointing to range

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    10

    VBA Combobox dependent pointing to range

    Hello everyone,

    I'm stuck in my code trying to have one combobox depdendant from another and displaying choices based on a worksheet range.
    The range I'm using is a bit particular:
    =Validations!$D$2:INDEX(Validations!$D$2:$D$1000,SUMPRODUCT(--(Validations!$D$2:$D$1000<>"")))
    This allow me to have my users changing info in the worksheet and the macro adapts automaticaly.

    Now it's all good for the first combobox when I set its RowSource to the range in question.
    But I can't find a way to make the second combobox dependant from the first one and have it take various ranges from the worksheet.

    The last bit of code I have tried is this one:
    Please Login or Register  to view this content.
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. Also your INDENT tags were all applied incorrectly so I removed them.--6StringJazzer

    Validations being the name of the worksheet, GroupType the first combobox and GroupName the second combobox.
    The Macro is in the form MeetingAdd at the start of it. I start the macro with the button on the top of the first sheet "Add".

    Does is make sense to anyone?

    PS: I'm new to VBA so every constructive criticism is more than welcome.

    Cheers.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 01-19-2021 at 05:09 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Combobox dependent pointing to range

    Try this code instead:

    I have installed my Date and Time entry Userforms into your Userform to help you out.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 01-19-2021 at 07:14 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    11-13-2012
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    10

    Re: VBA Combobox dependent pointing to range

    Dear Mehmetcik,

    First of, thank you for your swift answer.
    There's a lot for me to check and learn from your files, thanks for that too.

    I had to edit my original file for data protection and I should have mentioned it. I couldn't find the solution with what you proposed because of that, I assume.
    Nevertheless, it got me thinking about another possible way and found something which currently works.
    For the last group, as there's only one row in the column, the code I used doesn't work so I had to put it manually. Let's just hope I'll still work there if they ever need to add another row in this column

    LR As Long

    Private Sub GroupType_Change()

    'Depending on the selected Group Type it picks a range from the Validations sheet
    If MeetingAdd.GroupType.Value = "Group1" Then
    MeetingAdd.GroupName = vbNullString
    LR = Worksheets("Validations").Range("C" & Rows.Count).End(xlUp).Row
    GroupName.List = Worksheets("Validations").Range("C2:C" & LR).Value
    Else

    If MeetingAdd.GroupType.Value = "Group2" Then
    MeetingAdd.GroupName = vbNullString
    LR = Worksheets("Validations").Range("D" & Rows.Count).End(xlUp).Row
    GroupName.List = Worksheets("Validations").Range("D2:D" & LR).Value
    Else

    If MeetingAdd.GroupType.Value = "Group3" Then
    MeetingAdd.GroupName = vbNullString
    LR = Worksheets("Validations").Range("E" & Rows.Count).End(xlUp).Row
    GroupName.List = Worksheets("Validations").Range("E2:E" & LR).Value
    Else

    If MeetingAdd.GroupType.Value = "Group4" Then
    MeetingAdd.GroupName = vbNullString
    GroupName = "Group4"
    Else

    End If
    End If
    End If
    End If

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Value Dependent Dynamic Range ComboBox in Userform
    By Dferrier in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2016, 04:27 AM
  2. Populate combobox dependent on contents of previous combobox
    By ed1967 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 10-08-2014, 11:40 AM
  3. Populating combobox dependent on another combobox
    By JChaney17 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2014, 12:36 PM
  4. [SOLVED] VBA ComboBox Value Dependent on Conditional Value from Another ComboBox
    By Brianandstewie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2013, 02:35 AM
  5. [SOLVED] Userform with two combobox dependent on one combobox
    By bigfoot007 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-10-2012, 01:01 PM
  6. Dependent Combobox on Form not updating from dynamic range
    By ron2k_1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2011, 12:59 PM
  7. Pointing directly to a cell in a range
    By Tomski in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-13-2006, 12:14 PM

Tags for this Thread

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