ComboBox Code

[John Calder]

Hi

I run Excel 2K

I have opened the VB editor and inserted a "form".

I have added a combobox to the form

What I need to know is how do I populate the combobox with lets say 5 items:-

Example

Item1
Item2
Item3
Item4
Item5


and make it run in the spreadsheet.


Thanks

John

 

[Dave Peterson]

If you only have a small number of entries to add, I'd use the
UserForm_initialize event.

Option Explicit
Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "Item1"
.AddItem "Item2"
.AddItem "Item3"
End With
End Sub

Debra Dalgleish has some instructions here:
http://contextures.com/xlUserForm01.html
http://contextures.com/xlUserForm02.html
and
http://contextures.com/xlVideos05.html#UserForm01
http://contextures.com/xlVideos015html#UserForm01

 

[John Calder]

Dave thanks for the prompt response

What you gave me works good. However, I have about 6 of these comboboxes to
put on my user form (these are to replace textboxes presently being used).

The first one I put in worked great but when I tried to add a second one I
got a error message:-


Compile error: ambiguous name detected Userform_Initialise.

I thought it would be just a case of once i got one working the others would
be easy.....WRONG !

Any ideas what I need to do to add the other comboboxes to my userform?


I have included a copy of my code for you to look at if thats any help.


rivate Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DATA")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TxtDate.Value) = "" Then
Me.TxtDate.SetFocus
MsgBox "Please enter a the date"
Exit Sub
End If





'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtDate.Value
ws.Cells(iRow, 2).Value = Me.ComboBox1.Value
ws.Cells(iRow, 3).Value = Me.ComboBox2.Value
ws.Cells(iRow, 4).Value = Me.TxtCrew.Value
ws.Cells(iRow, 5).Value = Me.TxtNonProdDel.Value
ws.Cells(iRow, 6).Value = Me.TxtCalShift.Value
ws.Cells(iRow, 7).Value = Me.TxtInput.Value
ws.Cells(iRow, 8).Value = Me.TxtOutput.Value
ws.Cells(iRow, 9).Value = Me.TxtDelays.Value
ws.Cells(iRow, 10).Value = Me.TxtCoils.Value
ws.Cells(iRow, 11).Value = Me.TxtThrd.Value
ws.Cells(iRow, 12).Value = Me.TxtEps.Value
ws.Cells(iRow, 13).Value = Me.TxtType.Value
ws.Cells(iRow, 14).Value = Me.TxtNpft.Value
ws.Cells(iRow, 15).Value = Me.TxtScrp.Value
ws.Cells(iRow, 16).Value = Me.TxtDwnGrd.Value
ws.Cells(iRow, 17).Value = Me.TxtRawCoil.Value
ws.Cells(iRow, 18).Value = Me.TxtInj.Value
ws.Cells(iRow, 19).Value = Me.TxtSlowRun.Value
ws.Cells(iRow, 20).Value = Me.TxtPlanOutput.Value
ws.Cells(iRow, 21).Value = Me.TxtBudgOutput.Value

'As an option to the code below, unload and reload the form...
Unload Me
EntryForm.Show

'clear the data
'
'Me.TxtDate.Value = ""
'Me.TxtWeek.Value = ""
'Me.TxtShift.Value = ""
'Me.TxtCrew.Value = ""
'Me.TxtNonProdDel.Value = ""
'Me.TxtCalShift.Value = ""
'Me.TxtInput.Value = ""
'Me.TxtOutput.Value = ""
'Me.TxtDelays.Value = ""
'Me.TxtCoils.Value = ""
'Me.TxtThrd.Value = ""
'Me.TxtEps.Value = ""
'Me.TxtType.Value = ""
'Me.TxtNpft.Value = ""
'Me.TxtScrp.Value = ""
'Me.TxtDwnGrd.Value = ""
'Me.TxtRawCoil.Value = ""
'Me.TxtInj.Value = ""
'Me.TxtSlowRun.Value = ""
'Me.TxtPlanOutput.Value = ""
'Me.TxtBudgOutput.Value = ""
'Me.TxtDate.SetFocus


End Sub
----------------------------------------------------------------------------

Private Sub cmdClose_Click()
Unload Me


End Su
-----------------------------------------------------------------------------------

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

------------------------------------------------------------------------------------
Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim sEntry As String
Dim iLoc As Integer

sEntry = Trim(Me.TxtDate.Value)
iLoc = InStr(sEntry, "/")
If iLoc > 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" & Left$(sEntry,
iLoc - 1)
On Error Resume Next
Me.TxtDate.Value = Format(CDate(sEntry), "dd-mmm-yy")
If Err <> 0 Then
GoTo Had_Problem
End If
Exit Sub
End If

Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of d/m."
& vbLf & "Please try again..."
Cancel = True

End Su
---------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
.AddItem "32"
.AddItem "33"
.AddItem "34"
.AddItem "35"
.AddItem "36"
.AddItem "37"
.AddItem "38"
.AddItem "39"
.AddItem "40"
.AddItem "41"
.AddItem "42"
.AddItem "43"
.AddItem "44"
.AddItem "45"
.AddItem "46"
.AddItem "47"
.AddItem "48"
.AddItem "49"
.AddItem "50"
.AddItem "51"
.AddItem "52"


End With
End Sub
---------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
With Me.ComboBox2
.AddItem "N"
.AddItem "D"


End With
End Sub

---------------------------------------------------------------------------------

The last bit of code is where I get the error



Thanks

John

 

[Dave Peterson]

You only get one _initialize event per userform:

Option Explicit
Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "Item1"
.AddItem "Item2"
.AddItem "Item3"
End With

With Me.ComboBox2
.AddItem "Item4"
.AddItem "Item5"
.AddItem "Item6"
End With

With Me.ComboBox3
.AddItem "Item7"
.AddItem "Item8"
.AddItem "Item9"
End With
'and so forth
End Sub

But in your case, since you're adding a sequence of numbers:

Private Sub UserForm_Initialize()

Dim iCtr as long

With Me.ComboBox1
for ictr = 1 to 52
.additem ictr
next ictr
end with

With Me.ComboBox2
.AddItem "N"
.AddItem "D"
End With

End Sub

There could be other ways to do the rest of the 6 comboboxes, too. But that
would depend on what you're adding to each.

 

[John Calder]

Dave

Oustanding !!!


I have limited knowledge of VB (as you have no doubt worked out) but I am
very greatful for your support.

This is work in progress and no doubt I will ask more questions in the forum.

Once again, your help has been very much appreciated


John