Combox In Form

[Arunpd]

Hi,

I have a combo box in an Excel Form and have the following values added to
it( code below).I have two issues over here.

a.When i select a value say "wife" from the combo box all the values get
repeated in the combo box .
b.Add when the value is selected the value written to the cell is a "Number"
and not the value.

Private Sub ComboBox1_Change()
ComboBox1.AddItem "Father"
ComboBox1.AddItem "Mother"
ComboBox1.AddItem "Son"
ComboBox1.AddItem "Daughter"
ComboBox1.AddItem "Husband"
ComboBox1.AddItem "Wife"
'Set combo box to first entry
'ComboBox1.ListIndex = 0
End Sub

Pls help me out.

Regards
Arun

 

[Bob Phillips]

The first is because you are adding all the items in the Change event, you
should load the combobox in some other event, such as Userform_Activate. You
can also use the Clear method to clear it out.

On the second, if you have a cell linked to the Combobox you just get the
index number. You will have to dump the value within the code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[reklamo]

When you open (show) the Userform (activate) add the items to the Combobox.
When you select any item in the combobox write the selected value to a cell:

Private Sub UserForm_Activate()
ComboBox1.AddItem "Father"
ComboBox1.AddItem "Mother"
ComboBox1.AddItem "Son"
ComboBox1.AddItem "Daughter"
ComboBox1.AddItem "Husband"
ComboBox1.AddItem "Wife"
End Sub

Private Sub ComboBox1_Change()
' Write selected value to cell A1
ActiveSheet.Cells(1, 1).Value = ComboBox1.Value
End Sub

regrads
reklamo

 

[Arunpd]

Hi,

Thanks for your help..the first one is working fine..except that i have a
Add button on the form and still I am getting the Index Number".pls find the
below code...

Private Sub CMDADD_Click()

Dim Irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

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


'copy the data to the database
ws.Cells(Irow, 1).Value = Me.TxtEid.Value
ws.Cells(Irow, 2).Value = Me.TxtFirstName.Value
ws.Cells(Irow, 3).Value = Me.TxtLastName.Value
ws.Cells(Irow, 4).Value = Me.TxtDesignation.Value
ws.Cells(Irow, 5).Value = Me.TxtManager.Value
ws.Cells(Irow, 6).Value = Me.TxtBillDate.Value
ws.Cells(Irow, 7).Value = Me.TxtAmount.Value
ws.Cells(Irow, 9).Value = Me.TxtDependentName.Value
ws.Cells(Irow, 10).Value = Me.ComboBox1.Value

Thanks
Arun

 

[JLGWhiz]

Be sure your combo box is named ComboBox1.

Hi,

Thanks for your help..the first one is working fine..except that i have a
Add button on the form and still I am getting the Index Number".pls find the
below code...

Private Sub CMDADD_Click()

Dim Irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

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


'copy the data to the database
ws.Cells(Irow, 1).Value = Me.TxtEid.Value
ws.Cells(Irow, 2).Value = Me.TxtFirstName.Value
ws.Cells(Irow, 3).Value = Me.TxtLastName.Value
ws.Cells(Irow, 4).Value = Me.TxtDesignation.Value
ws.Cells(Irow, 5).Value = Me.TxtManager.Value
ws.Cells(Irow, 6).Value = Me.TxtBillDate.Value
ws.Cells(Irow, 7).Value = Me.TxtAmount.Value
ws.Cells(Irow, 9).Value = Me.TxtDependentName.Value
ws.Cells(Irow, 10).Value = Me.ComboBox1.Value

Thanks
Arun

 

[Arunpd]

Hi,

I checked the spelling( ComboBox)..its fine.

Thanks
Arun