PDA

View Full Version : Solved: Populating ComboBox from Multi Sheet rows



Mark$1
02-26-2008, 02:02 PM
I have been looking for over a week trying to find code that will help me populate a combobox from several sheets of the first column for every row. I can get the userform1 to load, but nothing else appears in the combobox. I have used some VB in the past, but not in the Excel evironment. I am not sure if I have missed the correct structure of the VBA. I am just looking for a some guidance and help to get me on my way.

Regards,
Mark$1

mdmackillop
02-26-2008, 03:10 PM
Hi Mark,
Welcome to VBAX
I'm not clear if this is quite what you're after, but give it a try

Option Explicit
Private Sub UserForm_Initialize()
Dim oRng As Excel.Range
Dim nColCnt As Integer
Dim nRowCnt As Integer
Dim i As Integer

Set oRng = ActiveSheet.Cells(2, 1)
nColCnt = 1
nRowCnt = 1
nRowCnt = oRng.End(xlDown).Row - 1

nColCnt = oRng.End(xlToRight).Column
For i = 3 To nRowCnt - 1 Step 2
cboParts.AddItem Cells(i, 1)
Next

Dim txtGunDia As Single
End Sub

Public Sub cboParts_Click()

'Issue values to Gun Lathe Program Search form
'
' if first cell is empty, that is end of data
'
Dim c As Range
Set c = Columns(1).Find(cboParts)
txtDrawingNo.Text = c.Offset(0, 2).Text
txtRevision.Text = c.Offset(0, 3).Text
txtGunType.Text = c.Offset(0, 4).Text
txtProgram.Text = c.Offset(0, 7).Text
End Sub

Bob Phillips
02-26-2008, 03:13 PM
VBA is Userform_Initialize



Private Sub Userform_Initialize()
Dim oRng As Excel.Range
Dim nColCnt As Integer
Dim nRowCnt As Integer
Dim i As Integer

With ActiveSheet

Set oRng = .Range("A2")

nColCnt = 1
nRowCnt = .Cells(.Rows.Count, "A").End(xlUp).Row
nColCnt = .Cells(1, .Columns.Count).End(xlToLeft).Row

For i = 3 To nRowCnt - 1 Step 2

cboParts.AddItem .Cells(i, CLM_REV).Text
cboParts.List(cboParts.ListCount - 1, 1) = i
Next
cboParts.ListIndex = 0
End With

End Sub

Mark$1
02-28-2008, 07:44 AM
Thank you for the help.
Marik