Column headings in a combobox!

praveenkrishnag · Jul 7, 2013

I would like to know how you can incorporate a combobox with column headings in a combobox so that when a column is selected from the list, the data entered into the corresponding column appears in the data sheet.

lhkittle · Jul 7, 2013

Try this where the combo box refers to F1 on sheet1 and the data "to appear" is in columns A, B, C, D of sheet2.

Regards,

Howard

[pre]

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colA As Long
Dim colB As Long
Dim colC As Long
Dim colD As Long
Dim ColDa As Variant

If Target.Address &#60;&#62; &#34;$F$1&#34; Then Exit Sub

Range(&#34;A:A&#34;).ClearContents

Select Case Target.Value

Case Is = &#34;colA&#34;
colA = Sheets(&#34;Sheet2&#34;).Range(&#34;A&#34; & Rows.Count).End(xlUp).Row
Sheets(&#34;Sheet2&#34;).Range(&#34;A1:A&#34; & colA).Copy Sheets(&#34;Sheet1&#34;).Range(&#34;A100&#34;).End(xlUp).Offset(1, 0)

Case Is = &#34;colB&#34;
colA = Sheets(&#34;Sheet2&#34;).Range(&#34;B&#34; & Rows.Count).End(xlUp).Row
Sheets(&#34;Sheet2&#34;).Range(&#34;B1:B&#34; & colA).Copy Sheets(&#34;Sheet1&#34;).Range(&#34;A100&#34;).End(xlUp).Offset(1, 0)

Case Is = &#34;colC&#34;
colA = Sheets(&#34;Sheet2&#34;).Range(&#34;C&#34; & Rows.Count).End(xlUp).Row
Sheets(&#34;Sheet2&#34;).Range(&#34;C1:C&#34; & colA).Copy Sheets(&#34;Sheet1&#34;).Range(&#34;A100&#34;).End(xlUp).Offset(1, 0)

Case Is = &#34;colD&#34;
colA = Sheets(&#34;Sheet2&#34;).Range(&#34;D&#34; & Rows.Count).End(xlUp).Row
Sheets(&#34;Sheet2&#34;).Range(&#34;D1:D&#34; & colA).Copy Sheets(&#34;Sheet1&#34;).Range(&#34;A100&#34;).End(xlUp).Offset(1, 0)

End Select
End Sub

[/pre]

Column headings in a combobox!

praveenkrishnag

New Member

lhkittle

New Member