Dependent combobox1 and combobox2

[Rvittur]

I have 2 comboboxes, which I would like to have combobox2 dependent on combobox1 selection. I have combobox1 with a named ranged listed with dates. For combobox2 I have 7 different named ranges for it it based off combobox1 selection. So if date selected is 3/13 combobox2 list is for Monday (named range), 3/14 combobox2 is Tuesday etc.. these named ranges for combobox2 is coming from another workbook.

I've tried select case but not sure if I'm doing it right. Currently the combobox1 is populated with dates.

 

[James006]

Hi,
Have a look at following simple solution:

How to Build Excel UserForm With Dependent ComboBoxes Lists

In Excel UserForm, make it easy to enter data. Add dependent combo boxes - list in one combo box depends on selection in another combo box

www.contextures.com

 

[Rvittur]

Hi,
Have a look at following simple solution:

How to Build Excel UserForm With Dependent ComboBoxes Lists

In Excel UserForm, make it easy to enter data. Add dependent combo boxes - list in one combo box depends on selection in another combo box

www.contextures.com

I looked at that one but still not having much luck. This is the code i got so far.

Private Sub cmdDate_Change()
Select Case cmdDate 'cmdDate is the combobox1. Currently has dates in it, starting at 3/13-4/02
Case Is = "3/13" 'the first date in the list of dates
cmdSuitDown = .List("Monday") 'cmdSuitdown is the Second combobox to be dependent on combobox1. I have 7 named ranges for it. Each a day of the week. The range for this one is in another workbook. However is in my named ranges for current workbook that has the userform. So UserForm(Workbook1) and cmdSuitdown list comes from Workbook2. I am trying to get it to where if 3/13 is selected then combobox2 populates Monday list. 3/14 populates Tuesday list etc.

End Select

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Dim cDates As Range
Dim ws As Worksheet
Set ws = Worksheets("Info")

For Each cDates In ws.Range("Dates")
With Me.cmdDate
.AddItem cDates.Value

End With

Next cDates


End Sub

 

[James006]

Hi again,
You can test this simple solution

Private Sub UserForm_Initialize()
  Me.cmdDate.List = Range("Dates").Value
End Sub

Private Sub cmdDate_Change()
' If you have structured your Data with all your 7 ranges
' aligned in Eight consecutive columns : dates,mondays,tuesdays, etc...
 Me.cmdSuitDown.List = Range("cmdDate").Offset(0, Me.cmdDate.ListIndex + 1).Value
End Sub

 

[Rvittur]

Hi again,
You can test this simple solution

Private Sub UserForm_Initialize()
  Me.cmdDate.List = Range("Dates").Value
End Sub

Private Sub cmdDate_Change()
' If you have structured your Data with all your 7 ranges
' aligned in Eight consecutive columns : dates,mondays,tuesdays, etc...
 Me.cmdSuitDown.List = Range("cmdDate").Offset(0, Me.cmdDate.ListIndex + 1).Value
End Sub

Monday, tuesday, etc is not in consective columns. Thats why i was trying to do it by select case. however i did try you code but got a method of Range of object_Global failed


Private Sub cmdDate_Change()

Me.cmdSuitDown.List = Range("cmdDate").Offset(0, Me.cmdDate.ListIndex + 1).Value


End Sub



Private Sub UserForm_Initialize()
Me.cmdDate.List = Range("Dates").Value

End Sub

 

[James006]

Sorry ... but tested at my end with a simple worksheet (3 columns) and a UserForm with two comboboxes ... and it is working fine ...

 

[Rvittur]

Sorry ... but tested at my end with a simple worksheet (3 columns) and a UserForm with two comboboxes ... and it is working fine ...

Ok not sure what I'm doing wrong then. I been at it for several days. I might try and move my data for combobox2 to the same workbook and try it that way and see if i can get it to work.

 

[Rvittur]

Sorry ... but tested at my end with a simple worksheet (3 columns) and a UserForm with two comboboxes ... and it is working fine ...

I did get it to work finally. However i ran into another problem. Once a specific date is reached, the data is pulled from a different workbook. I have it working all the way to the 26th. On the 27th it switches to another book. I created a named ranged with that book, the same way i did with week 1. However once i select the 3/27 in the drop down it does not load. I switched the range name in code however still not working. Any thoughts.

In cmdDate_Change()

If UserForm1.cmdDate.Value = "3/20" Then 'Monday
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex).Value
ElseIf UserForm1.cmdDate.Value = "3/21" Then 'Tues
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex + 4).Value
ElseIf UserForm1.cmdDate.Value = "3/22" Then 'Wed
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex + 8).Value
ElseIf UserForm1.cmdDate.Value = "3/23" Then 'Thur
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex + 12).Value
ElseIf UserForm1.cmdDate.Value = "3/24" Then 'Fri
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex + 16).Value
ElseIf UserForm1.cmdDate.Value = "3/25" Then 'Sat
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex + 20).Value
ElseIf UserForm1.cmdDate.Value = "3/26" Then 'sunday
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex + 24).Value

ElseIf UserForm1.cmdDate.Value = "3/27" Then 'sunday
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday1").Offset(0, UserForm1.cmdDate.ListIndex).Value

in userform initialize

Private Sub UserForm_Initialize()
Workbooks.Open ("C:\Users\jvittur\OneDrive\Desktop\Schedules\Sched Pickup 03.26.xlsm")
Workbooks.Open ("C:\Users\jvittur\OneDrive\Desktop\Schedules\Sched Pickup 04.02.xlsm")
Application.Windows("Sched Pickup 03.26.xlsm").Visible = False
Application.Windows("Sched Pickup 04.02.xlsm").Visible = False
End Sub

 

[James006]

Glad to hear you managed to solve your initial problem

 

[Rvittur]

So i got all the dates to work accordingly. Now the problem is, with the 6 comboboxes. If only 3 of the boxes get filled, ie Date, SuitDown and TSC, it doesn't go in on the worksheet. However if I fill in all comboboxes all the data goes in. The pic is to help understand. I would like to have it if just those 3 boxes have value then those values goes into the approiate columns just like if they were all filled. The ones with n/a should be blank.