VBA ComboBox Userform Query

[ForumMember]

Hi all,

I have put together a table as an example of what I will be working with. What I would like ideally is:

• Each of the column headers to form an option in Combobox 1 (More will be added)

• Each of the values in the cells under the Headers to represent dependent options in Combobox 2 (these will also change, so need to comboboxes to change accordingly)

• Also, Instead of populating the existing comboboxes in the code with the “AddItem” command, I would like to reference the relevant column on the “Data Input” sheet.

I have included a small piece of existing code to give some idea of where I am at and how I have got there.
What I really need now is the code to do the above, an explanation of what the code is doing would be great – and crucially, how to insert the new code into the existing code so it works!
I hope that has clarified?

[FONT=Arial][SIZE=2][COLOR=#000000]Private Sub UserForm_Initialize()                
                
        Application.Visible = False                
                        
        txtCustomer.Value = ""                
                
        txtDetails.Value = ""                
                        
        txtResDetails = ""                
                        
        txtDate.Value = Format(Date, "dd/mm/yyyy")                
                        
        txtTime.Value = Format(Time, "hh:mm")                
                        
With cboOperator                
                
.AddItem "Operator 1"                
                
        .AddItem "Operator 2"                
                
        .AddItem "Operator 3"                
                
        .AddItem "Operator 4"                
                
        .AddItem "Operator 5                
                        
 End With                
                
    cboOperator.Value = ""                
                    
With cboIssue                
                
.AddItem "Missing"                
                
        .AddItem "Quality"                
                
        .AddItem "Short Delivered"                
                
        .AddItem "Damaged"                
                
        .AddItem "Late"                
                
 End With                
                
    cboIssue.Value = ""                
                    
End Sub                
                
Private Sub cmdExit_Click()                
                    
    Unload Me                
    Application.Quit                
                
End Sub                
                
                
Private Sub cmdClear_Click()                
                
    Call UserForm_Initialize                
                    
End Sub                
                
Private Sub cmdUnlock_Click()                
                
If Me.txtPassword.Value = "password" Then                
                
Unload Me                
                
Application.Visible = True                
                
Else                
                
Me.Hide                
                
Retry = MsgBox("The password is incorrect. Do you wish to try again?", vbYesNo, "Retry?")                
                
Select Case Retry                
                
Case Is = vbYes                
                
Me.txtPassword.Value = ""                
                
Me.txtPassword.SetFocus                
                
Me.Show                
                
Case Is = vbNo                
                
Unload Me                
                
End Select                
                
End If                
                
End Sub[/COLOR][/SIZE][/FONT]

Purchasing.....Customer......Operator......Salesperson......Driver
Purchasing 1....Customer 1.....Operator 1....Salesperson 1.....Driver 1
Purchasing 2....Customer 2.....Operator 2....Salesperson 2.....Driver 2
Purchasing 3....Customer 3.....Operator 3....Salesperson 3.....Driver 3
Purchasing 4....Customer 4.....Operator 4....Salesperson 4.....Driver 4
Purchasing 5....Customer 5.....Operator 5....Salesperson 5.....Driver 5
Purchasing 6..........................Operator 6..............................Driver 6
Purchasing 7.......................................................................Driver 7

 

[Norie]

Your code already has 2 comboboxes, do you want to add another 2 for the headers etc.

Add 2 comboboxes, cboHeadings and cboData to the userform.

Integrate this code with the rest of the userform initialize event - you can just stick it after what you've got (apart from the variable declarations)

Dim NoHeaders As Long
Dim arrHeaders
    With Worksheets("Sheet1")
        NoHeaders = .Cells(1, Columns.Count).End(xlToLeft).Column
    
        arrHeaders = .Range("A1").Resize(, NoHeaders).Value
     
     End With
     
     Me.cboHeaders.List = Application.Transpose(arrHeaders)

Add this to the userform module.

Private Sub cboHeaders_Change()
Dim rngData As Range
Dim LastRow As Long
 
 If Me.cboHeaders.ListIndex <> -1 Then

      With Worksheets("Sheet1")

         LastRow = .Cells(Rows.Count, Me.cboHeaders.ListIndex + 1).End(xlUp).Row - 1

         Set rngData = .Cells(2, Me.cboHeaders.ListIndex + 1).Resize(LastRow - 1)

      End With

      Me.cboData.List = rngData.Value

  End If

End Sub

Obviously change the worksheet name and range if required - I've used Sheet1 and put the data/headers in columns A:E.

 

[ForumMember]

Thanks Norie, that's a fantastic post! Exactlywhat I needed!

If I could extend your generosity a little further, I could really use a couple of small additions:

1. If the Header value andthe Data value are selected, then a user decides to change the Header value, how would I make it so the Data value defaulted back to blank?
2. If I added a seperate ComboBox and wanted the options in that box to be the values from A2 - The last cell in column A with a data value, how would I go about that?

Thanks again for your help thus far - and I promise I won't ask anything else in this thread!

 

[Norie]

See this file.

http://www.box.net/shared/7f5rcy1ly1dxd9zeumg9

 

[ForumMember]

Once again a perfect solution to my problem Norie.

I really can't thank you enough for your time!

I sincerely appreciate it.