MrExcel Publishing
Your One Stop for Excel Tips & Solutions

problem w/ code


Posted by Jim on October 07, 2001 5:37 AM

Hello all,
I need a dialog box that will warn the user if the value
they've entered in the textbox is not a match with the
value in the listbox.
This is what i'm working with:
A userform w/ a listbox, a textbox and a combobox dropdown
the listbox rowsource is sheet1!G66. When you enter a value
in the textbox and click on the combobox dropdown and
select a value 1 thru 10 it then multiplies the listbox value
by the selection. My problem is this works w/ any value
entered in the textbox, and the calculated result will
be the wrong sum if the value entered in the text box
doesn't match the value in the listbox so this is why i
need the dialog box to warn the user.
My code:
Private Sub cbx_1_Click()
If [textbox1].Value = [Sheet1!G66].Value Then
[G66].Value = (textbox1 * cbx_1)
rw = cbx_1.ListIndex
End If
If [textbox1].Value <> [Sheet1!G66].Value Then
MsgBox "the data entered is not a match",vbOKOnly
End If
End Sub
The problem i'm having is the warning box fires off
even if the value entered in the textbox is a match.
Any help would be appreciated
Jim


Posted by Mark O'Brien on October 08, 2001 7:34 AM

It looks to me that you're code is comparing the value of the textbox with the value of cell G66. If they are a match then it changes the value of G66 to be some multiple of textbox1 and cbx_1. Basically, this changes the value in G66. You then check again to see if the textbox value is the same as cell G66, which it is not. This is because you change the value of G66 in your first "if" statement.

My suggestion is that you use only one If...Else...End If statement.

Try this:

Private Sub cbx_1_Click()
If [textbox1].Value = [Sheet1!G66].Value Then
[G66].Value = (textbox1 * cbx_1)
rw = cbx_1.ListIndex
Else
MsgBox "the data entered is not a match",vbOKOnly
End If
End Sub