ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Combox In Form (https://www.excelbanter.com/excel-programming/407621-combox-form.html)

Arunpd
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

Bob Phillips
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

reklamo
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

Arunpd
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

JLGWhiz
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

Arunpd
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


All times are GMT +1. The time now is 11:02 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com