PDA

View Full Version : Fill a combobox with data found in a range based on worksheet chosen in another cbx



lambr
03-08-2014, 05:59 PM
I have a userform with two comboboxes. The first combobox populates with the names of each worksheet in the workbook. How do I get the second combobox to populate with column headings in the worksheet chosen in the first combobox

david000
03-09-2014, 07:37 PM
Private Sub ComboBox1_Change()
Dim iCol As Integer
Dim cel As Range
Dim ws As Worksheet
Set ws = Worksheets(ComboBox1.ListIndex + 1)
ComboBox2.Clear
With ws
iCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For Each cel In .Range("a1").Resize(, iCol)
ComboBox2.AddItem cel.Value
Next cel
End With

End Sub
Private Sub UserForm_Initialize()
Dim i As Integer
With ActiveWorkbook
For i = 1 To Worksheets.Count
With ComboBox1
.AddItem Sheets(i).Name
End With
Next i
End With
End Sub

lambr
03-15-2014, 09:11 AM
Thank You! That worked perfectly

However I have another issue. The data entered into a form will be entered onto a the chosen worksheet. Two items entered will always be entered into the same column, one item however needs to be entered into a cell based on the column heading chosen combobox2 (which varies from worksheet to worksheet). How can I manage this? I cannot simply state:

ActiveCell.Offset(0, 0) = Me.PurchaseDate.Text
ActiveCell.Offset(0, 1) = Me.Description.Text
ActiveCell.Offset(0, 2) = Amount.text (Depending on which column header is chosen in checkbox2 the amount may need to be offset additional columns)

westconn1
03-15-2014, 05:05 PM
try like
col = activesheet.range("1:1").find(combobox2.text).column
ActiveCell.Offset(0, col - 1) = Amount.textnot tested