vba excel:combobox

Microsoft Office excel 2003
July 30, 2010 at 00:26:46
Specs: Windows 7
I am looking at various threads but I didn’t find any. I used row source property to add column A1:A5 in combo box. If I select A3 in combo box and enter a value in text box, how to add the value to B 3 ? Suppose I enter 5 in the text box, it should update B 3 to 305.
Column A column B
Product 1 100
Product 2 200
Product 3 300
Product 4 400
Product 5 500


See More: vba excel:combobox

Report •


#1
July 30, 2010 at 06:53:10
Hi,

Here is code I put in the Text box Exit event
The text box was TextBox1:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim intSelect As Integer
Dim dblProd As Double
'only try and add if there is a number in the box
If IsNumeric(Me.TextBox1.Text) Then
    'get combobox1 selection line number - First item is Index 0
    intSelect = Me.ComboBox1.ListIndex
    'get product value from column B, using row offset
    dblProd = Worksheets("Sheet3").Range("B1").Offset(intSelect, 0).Value
    'add text in text box, (converted to a number) to product value
    Worksheets("Sheet3").Range("B1").Offset(intSelect, 0).Value = _
        dblProd + CDbl(Me.TextBox1.Text)
    End If
End Sub

You will need to change the worksheet name to match where your product list is.

The code tests that you have entered a number to avoid errors with an empty text box or a non-numeric entry.

I used the Exit rather than the Change event, as the change event gives unpredictable results if you try adding more than single digit values, as it responds to the change from empty to 1 (adds 1) and then as you enter 5 to make it 15, it adds 15, for a total of 16.

Exit is not so smooth - you can enter the number and hit enter, or enter the number and select another object. You could add a button labeled 'Add' or something like that, to use as the action button.

Then move the code into the Button Click event.

The ComboBox ListIndex returns an index for the selected item, starting at zero for the first item, so this can be used directly as a row offset to get the relevant product value.

I split the code into discrete steps, just to make it easier to see what is happening, but you could combine the 'add' part of the code into one line:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'only try and add if there is a number in the box
If IsNumeric(Me.TextBox1.Text) Then
    'add textbox 'value' to selected product value
    Worksheets("Sheet3").Range("B1").Offset(Me.ComboBox1.ListIndex, 0).Value = _
        Worksheets("Sheet3").Range("B1").Offset(Me.ComboBox1.ListIndex, 0).Value + _
        CDbl(Me.TextBox1.Text)
    End If
End Sub

Note that a space followed by underscore "  _" is a line-continuation marker.

This is the Button Click 'version'

Private Sub CommandButton1_Click()
'only try and add if there is a number in the box
If IsNumeric(Me.TextBox1.Text) Then
    'add textbox 'value' to selected product value
    Worksheets("Sheet3").Range("B1").Offset(Me.ComboBox1.ListIndex, 0).Value = _
        Worksheets("Sheet3").Range("B1").Offset(Me.ComboBox1.ListIndex, 0).Value + _
        CDbl(Me.TextBox1.Text)
    End If
End Sub

Try the different options and see which works best for you.

Regards


Report •

#2
July 31, 2010 at 00:54:42
Its so impressive , Thank you , Thank you again.

Report •

#3
July 31, 2010 at 04:03:40
You're Welcome,

Regards

Humar


Report •

Related Solutions


Ask Question