Fill Combobox Based On Values In Adjacent Column

[rydysh]

hi all ,

id like to seek some help regarding my problem. I have a worksheet with three columns in it. the first column is the "channel column", the second column is the "status column"; which indicates the status of the channel column wheter it is active or not, the third column is the "circuit name" column which indicates the circuit name for each channel. now, the status of the channel depends on the value of the circuit name column.. if the cell in the circuit name column is blank, the status column has an if formula that automatically changes it to vacant status, otherwise active.

i added command buttons namely "active" and "vacant". this buttons correspons to active userform and vacant useform when clicked. in each userform there is a combobox. My problem is how would i fill the combobox in the active userform with the active channels only , and same in the vacant userform with vacant channels only. the comboboxes list should correspond to changes in the status column, that is if one channel gets an active status it would be deleted from the vacant userform and be added to the active userform.

i uploaded my workbook for your reference.

regards,
ryan

[Excel Fox]

Use this in the userform for active channels...

Private Sub UserForm_Activate()

    Call ActiveVacant("Active")
    
End Sub

Function ActiveVacant(strActiveVacant As String) As Variant

    Dim lng As Long
    Dim var As Variant
    var = Range("D2:E100").Value2
    For lng = LBound(var) To UBound(var)
        If UCase(var(lng, 1)) = UCase(strActiveVacant) Then
            Me.ComboBox1.AddItem var(lng, 2)
        End If
    Next lng
    
End Function

You can replicate for vacant also. But since your vacant channels don't have any value in column E, it will add empty items to the combobox. Not sure why you want that.

[rydysh]

the comboboxes should display the channels that are active or vacant.. so if a cell in column E does not have a value then the vacant userform should display the vacant channel correspoding to that vacant cell.

[alansidman]

crossposting: combobox values depends on cell status.
https://www.youtube.com/channel/UCnx...RbjOo_MO54oaHA

[rydysh]

sorry didnt know about that.. it will never happen again..

[rydysh]

just wandering why my post here changed title, anyway sorry for the trouble.. its my first post here..it will never happen again
https://www.youtube.com/channel/UCnx...RbjOo_MO54oaHA