Complicated Combobox sequence: Dependent comboboxes and return value to cell.

[JaredMcCullough]

The intentions are to have the two comboboxes provide the location to return the value of a textbox. The user enters there name in the textbox, select there approval level, and submit which returns to the appropriate spot.

Current Controls: Userform1, Combobox1, Combobox2, Textbox1, Textbox2, Commandbutton1
Worksheet Information: ID = Column A, Type = Column E, Approval 1 = Column Q, Approval 2 = Column R, Approval 3 = Column S, Approval 4 = Column T

Current Code:

Combobox1 pulls in the ID number from column A of a worksheet. Which then populates a critical value (Type) for later in the code in Textbox1.

Private Sub UserForm_Initialize()

Dim Rng As Range
Dim con As Control
Set Rng = Sheets("Variance Database").Range("A7:A" & Sheets("Variance Database").Range("A" & Rows.Count).End(xlUp).Row).Find(Me.ComboBox1.Value)

    With Worksheets("Variance Database")
        ComboBox1.List = .Range("A8", .Range("A" & Rows.Count).End(xlUp)).Value
    End With

    If Not Rng Is Nothing Then
       Rng.Offset(, 4).Value = Me.TextBox1.Value
    End If

End Sub

The next step would be to have the above code to populate Combobox2 with the "Approval Levels" (see above for approvals and their associated columns) based upon if the row (from combobox1) currenlty maintains a value. If has it HAS a value under the approval level then ingore or DONT put in the Combobox2 . If it DOES NOT have a value then put the Approval term (ex. "Approval 1" if no value in column Q) in combobox2.

The user will then select the available approval level from Combobox2 and type there name in Textbox2.

The final step would be to have commandbutton1 return the value to the chosen "Approval Column" of the row indicated by Combobox1


I would greatly appreciate anyone who is willing to help with this portion of my project as it is nearing completion and I am stuck on just a few last pieces.

 

[JaredMcCullough]

Bump for any help as I am pretty stumped