PDA

View Full Version : Display cell value into a textbox



fadib
12-24-2007, 12:16 PM
Hi guys,
This thing is frustrating.... I am not understanding what is wrong.
Here is my code.
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim RowNumm As Variant
On Error Resume Next
With Worksheets("Vol_data")
RowNumm = Application.Match(ComboBox1.SelText, .Column(3), 0)
TextBox1.Text = .Cells(RowNumm, "B").Value
End With
End Sub

The code is supposed to take a slected item from a combobox, make a match in sheet "Vol_data" ColumnA, and display in textbox1 the value that exist in the ame row, but column B.

Can anyone please help me.

mikerickson
12-24-2007, 12:27 PM
If this is in a userform, I'd add specificity. I'd also change the property being returned from the combo box.

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim RowNumm As Long
On Error Resume Next
With ThisWorkbook.Worksheets("Vol_data")
RowNumm = Application.Match(Me.ComboBox1.Value, .Column(3), 0)
Me.TextBox1.Text = .Cells(RowNumm, "B").Value
End With
On Error Goto 0
End Sub

one more possibility, comboBoxes return Strings, if Vol_Data! C:C has numbers, use the Val function on the first argument of the Match.

Bob Phillips
12-24-2007, 12:28 PM
Private Sub ComboBox1_Change()
Dim RowNumm As Variant
On Error Resume Next
With Worksheets("Vol_data")
RowNumm = Application.Match(ComboBox1.Value, .Columns(3), 0)
TextBox1.Text = .Cells(RowNumm, "B").Value
End With
End Sub

fadib
12-24-2007, 12:37 PM
I tried that...
RowNumm is not the right number, it is supposed to be 2 and I am getting 0.
So I tried a different Dim, I got empty for RowNumm.
I don't know what that means, but it might be a clue.

mikerickson
12-24-2007, 12:40 PM
You tried which? Did you try

RowNumm = Application.Match(Val(Me.ComboBox1.Value), .Column(3), 0)

Inside the On Error Resume Next, RowNumm will behave that way if the Match finds nothing and returns an error value.

fadib
12-24-2007, 12:40 PM
Hi Xld,
I run the code, while debuging, I have error 2042 on RowNumm.
:dunno

fadib
12-24-2007, 12:42 PM
Yep I tried that too. [Mikerickson]:(

mikerickson
12-24-2007, 12:43 PM
Eureka!

Columns(3)

fadib
12-24-2007, 12:49 PM
Nope!!

fadib
12-24-2007, 12:51 PM
I have aquestion. what i sthe difference between .columns(3) and .columns(2)?

mikerickson
12-24-2007, 12:55 PM
The code in the OP has "column" (no "s") which is not a property of a worksheet.
"columns" (with the "s") IS a property of a worksheet.

A simple typo hidden by the On Error Resume Next. And caught by XLD, but not noticed by me.

fadib
12-24-2007, 12:58 PM
Sorry Guys,
The reason was That I was using the sentence without even knowing the meaning of it.
Since I need the search to occur in the first column, I have to use .columns(1)

Thanks Mikerickson, it worked.

Xld, I am not sure why it is not working.
:dunno

mikerickson
12-24-2007, 01:02 PM
Merry Christmas

:xmas:

fadib
12-24-2007, 01:05 PM
Thank you.
Merry christmas to all of you.
May God always bless you guys.