Solved Data Validation, Combo Box If Statements

June 14, 2016 at 01:10:50
Specs: Windows 7
I need help with my assignment, we are creating a Flight Agency.

1. We have to make departure and return dates, how I can make sure the user cannot travel back in time. (i.e. picking a return date which is before the departure date)
2. How to make a combo box (form control) grey out depending on the selection of a radio button.
3. How to change the range of a combo box depending on the value of a cell.

Thanks


See More: Data Validation, Combo Box If Statements

Report •


#1
June 15, 2016 at 02:03:01
✔ Best Answer
Here is an example of how you can control the combo box dates.

In Column A I have a number of date
From "01/01/2016"
To "30/01/2016"

This code will load all the dates into the first combobox ("Combobox1")

Private Sub UserForm_Activate()
    
    For Each bcell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        ComboBox1.AddItem Format(bcell, "dd/mm/yyyy")
    Next bcell
    
End Sub

This code will be driven by combobox1'2 change event, what it does is, check each date in Column A, compare it with the date selected in Combobox1, if the different is greater than zero, it means the date is in the future and therefore it will add it to combobox2

Private Sub ComboBox1_Change()
    
    For Each bcell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        
        If DateDiff("d", ComboBox1, bcell) > 0 Then
            ComboBox2.AddItem Format(bcell, "dd/mm/yyyy")
        End If
        
    Next bcell
    
End Sub


Report •
Related Solutions


Ask Question