combo box in form

[static69]

i want to add a combo box to my user form that will allow the user to select which sheet they are giong to input data into. This should then make the selected sheet the active sheet. (

[theDude]

Copy this code to the code page of your UserForm (it assumes
that the form name is "UserForm1" and the combo box is named
"ComboBox1"). It sets the contents of the ComboBox just prior
to when the UserForm is displayed based on the 'initialize'
event of the UserForm:

Please Login or Register  to view this content.

In the same code page of your UserForm, copy this code to activate
the worksheet from the selection in the ComboBox when the user clicks
the "OK" button on the UserForm (it assumes that the "OK" button is
named "CommandButton1"):

Please Login or Register  to view this content.

Personally, I would use a ListBox instead of a ComboBox because a
combo box allows the user to type their own entry. If the user doesn't
type the exact name of an existing worksheet, your processing macro
(assigned to "CommandButton1") will not work unless you do extra
coding to capture/recover the user data entry error before hiding
the form & allowing the processing to continue. If you take my
advice, replace "Combo" with "List" in the code...

Hope this helps,
theDude

[static69]

word. thanx

[static69]

the list box comes up empty..... any clues how i screwed up your code?

Private Sub DATA_ENTRY_Initialize()
Dim Sh As Worksheet
' Get each worksheet name in the workbook to add to ComboBox1...
For Each Sh In ThisWorkbook.Sheets
DATA_ENTRY.ListBox1.AddItem Sh.NAME
Next Sh
' Set Combo box to first item in its' dropdown list (the 1st worksheet)..
ListBox1.ListIndex = 0
End Sub

Private Sub CommandButton1_Click()
' Activate the worksheet selected...
ThisWorkbook.Worksheets(ListBox1.Text).Activate
'
' Add your additional processing code for the active sheet here...
End Sub
Private Sub cmdADD_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet

'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.txtNumber.Value) = "" Then
Me.txtNumber.SetFocus
MsgBox "Please enter the card number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtNumber.Value
ws.Cells(iRow, 2).Value = Me.txtQuantity.Value
ws.Cells(iRow, 3).Value = Me.txtName.Value

'clear the data
Me.txtNumber.Value = ""
Me.txtQuantity.Value = "1"
Me.txtName.Value = ""
Me.txtNumber.SetFocus

End Sub

[theDude]

First, you invalidated the UserForm_Initialize event because you replaced the reference to the UserForm object with your name for the object. Second, I don't know what version of Excel you are running, but in my version (Excel 2000), the name you used for the form won't work because of the underscore character. That said, I tested your code with the following changes:

1) I renamed the form to dataEntry instead of DATA_ENTRY

Replace yours:

Please Login or Register  to view this content.

With this (note my highlighted changes):

Please Login or Register  to view this content.

Hope this helps,
theDude