Dependent combo boxes via vba

[Eric Carolus]

Good day Folks

I have searched the forum, but none of the posts address my issue.

I wish to populate a combo box via vba , based on two prior selections. this what I have done so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Worksheets("Sheet1").Range("M8").Value = "Day 1" Then

Dim i As Integer
Dim cell As Range
Dim periodrange As Range

Set periodrange = Worksheets("Sheet1").Range("C7:C27")

For Each cell In periodrange
'This is where I do not know how code further.
'What I want to do though is if the user Select, say, Day 1
'then in C7:C26 there may appear, for example, Admin, Break, Period 1, Period 2, Period 3, etc.
' Only in the corresponding range F7:F26 a data validation combo should appear
'with the data of the corresponding names of people.

'Differently said, if (in C7:C26), say the value of C8 is Period 1, them in F8 a combo should
'appear with the names of D1P1, opposite Period 2 Should appear the names in D1P2.
' Is this even possible?
'Is there a better way of doing this?
'Please help

Next cell
End If
End Sub

I really need all the help I can get.
Thanks in advance.

Eric
Ps. I inclued the workbook CombosHelp.xlsm

 

[Akuini]

I wish to populate a combo box via vba , based on two prior selections.

1. You said combo box, but you meant data validation, right?
2. What should the data validation (in col F) have if value in col C is Admin or Break ?

 

[Eric Carolus]

Hi Akuini

1. Yes, you are right - data validation.
2. Data validation in col F should be ignored.

Thank you.

Eric

 

[Akuini]

Ok, try this:
1. In sheet "Days&Periods", create a named range "xName", refer to cell A1
2. In sheet "Sheet1" cells"F7:F27" create data validation, use "=xName" as the source.
3. The code must be located in sheet "Sheet1" code module.

Option Explicit

'The sheet where the list is located
Private Const xList As String = "Days&Periods"

'column of the list header
Private Const xHead As String = "H:O"

'range where you want to use data validation
Private Const sRange As String = "F7:F27"

'the named range
Private Const tx As String = "xName" 'named range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range(sRange)) Is Nothing Then
            
        Dim c As Range, f As Range
        Dim p As String
        
        With Sheets(xList)
            If InStr(1, Target.Offset(, -3), "Period", 1) Then

                p = Replace(Target.Offset(, -3), "Period ", "")
                p = "D" & Replace(Range("M8"), "Day ", "") & "P" & p
                
                Set c = .Columns(xHead).Find(What:=p, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
                
                If Not c Is Nothing Then
                    If c.Offset(1) <> "" Then
                        Set f = .Range(c.Offset(1), c.End(xlDown))
                        ThisWorkbook.Names.Add Name:=tx, RefersTo:=f
                    Else
                        ThisWorkbook.Names.Add Name:=tx, RefersTo:=c.Offset(1)
                    End If
                
                End If
                
            Else
                ThisWorkbook.Names.Add Name:=tx, RefersTo:=.Cells(1, Columns.Count)
            End If
            
        End With
        
    End If
End Sub

The workbook:
CombosHelp - Eric Carolus 1.xlsm

 

[Eric Carolus]

Hi Akuini

Absolutely brilliant!

There is just now that I would have been
able to figure this out!

It works like a charm!

Eric

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.