| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combox In Form
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combox In Form
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) "Arunpd" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combox In Form
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 "Bob Phillips" wrote: 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) "Arunpd" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combox In Form
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 "reklamo" wrote: 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 "Bob Phillips" wrote: 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) "Arunpd" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combox In Form
Be sure your combo box is named ComboBox1.
"Arunpd" wrote: 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 "reklamo" wrote: 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 "Bob Phillips" wrote: 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) "Arunpd" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combox In Form
Hi,
I checked the spelling( ComboBox)..its fine. Thanks Arun "JLGWhiz" wrote: Be sure your combo box is named ComboBox1. "Arunpd" wrote: 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 "reklamo" wrote: 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 "Bob Phillips" wrote: 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) "Arunpd" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to get entries into a combox on a form? | Excel Programming | |||
| Combox Box and 3 worksheets | Excel Programming | |||
| Combox Box and 3 worksheets | Excel Programming | |||
| Inserting a combox | Excel Programming | |||
| Combox Locking | Excel Discussion (Misc queries) | |||
Linear Mode
