Compare Text/combobox values against cells in spreadsheet

I have tried this for days, and can't seem to get it to work.

How do I compare values that are in a user form with cell values on a
spreadsheet and if the same, stop the macro, and then msgbox to user "blah
blah".

Example: In userform2 I have several texboxes, comboboxes, and togglebuttons
(ie lastname, first, etc...) that are populated by selecting from a 'select a
record' combobox. The 'select a record' combobox draws the data via index and
match from another spreadsheet 'record data'. The spreadsheet 'record data'
holds by row, a copy of the data entered into userform1 (which is an entry
form), userform2 is used primarily to retrieve that data but it can also save
that data too. Each entry for the 'record data' spreadsheet is saved in its
own row using offset etc...when selecting 'add this data to the db'
commandbutton (available on both userforms). On userform2, in the 'add this
data..'commandbutton I am trying to get a msgbox to pop up and tell the user
that if they haven't changed the populated data in userform2, then it's not
necessary to 'add this record to this db'. Not sure what I am doing wrong.

Here's an example of the possible offensive code:
Dim cbx7a As Long
cbx7a = ComboBox7.Value

If TextBox1.Value =
Application.Index(Worksheets("Sheet5").Range("PerslData"), _
Application.Match(cbx7a, Worksheets("Sheet5").Range("Record_Number"), 0), 2)
Then

Elseif 'all the other references to the text/combo/toggle boxes buttons.

Else
MsgBox "It appears that you haven't changed...not necessary to save."

'where Texbox1 holds a date as text, combobox7 holds a record number but is
formatted to dislpay 'date entered' and 'name of applicant' when selected so
that the user can see which person to bring up. "Record_Number" is a named
cell range on sheet5, that holds the numbers 1 to 2000 in column 'A' (primary
key sort of)

There are also more procedures within that macro that check certain
text/combo boxes for entries. One is:
If TextBox1.Value = "" Then
MsgBox "Please enter a Date!"
Exit Sub
End If

End State: What I would like the procedure to do is compare all of the data
in userform2(retrieval), against the values it retrieved from spreadsheet 5,
in the event the user changed any of them, which is allowable. The reason for
that is so that the user doesn't save a 'record' if he or she didn't make any
changes to it.

Please help.

All values in controls like textboxes and comboboxes are text strings. You
may be running afoul of comparing a string to a date serial number for
example.

Also, you don't need to use application.Index with the results of match

set rng = Worksheets("Sheet5").Range("PerslData").Columns(1).Cells
set rng1 = Worksheets("Sheet5").Range("Record_Number")
res = Application.Match(cbx7a, Worksheets("Sheet5").Range("Record_Number"), 0)

if not iserror(res) then
if rng(res,2).Value = Textbox1.Text or rng(res,2).Text = Textbox1.text then
' match
else

end if
else
' Match not made
End sub

using a variable as a reference to your range will be more efficient than
index and if you use the range multiple times will be more efficient again.

also with dates, the .text attribute of a range gives how it is formatted in
the cell. In the textbox, it could be the same date, but in a different
format, so that would failt to match unless you use a cdate(textbox1.text)
= rng(res).value type test.



--
Regards,
Tom Ogilvy

If TextBox1.Value =
Worksheets("Sheet5").Range("PerslData"), _
Application.Match(cbx7a, Worksheets("Sheet5").Range("Record_Number"), 0), 2)


"BigPig" wrote:

> I have tried this for days, and can't seem to get it to work.
>
> How do I compare values that are in a user form with cell values on a
> spreadsheet and if the same, stop the macro, and then msgbox to user "blah
> blah".
>
> Example: In userform2 I have several texboxes, comboboxes, and togglebuttons
> (ie lastname, first, etc...) that are populated by selecting from a 'select a
> record' combobox. The 'select a record' combobox draws the data via index and
> match from another spreadsheet 'record data'. The spreadsheet 'record data'
> holds by row, a copy of the data entered into userform1 (which is an entry
> form), userform2 is used primarily to retrieve that data but it can also save
> that data too. Each entry for the 'record data' spreadsheet is saved in its
> own row using offset etc...when selecting 'add this data to the db'
> commandbutton (available on both userforms). On userform2, in the 'add this
> data..'commandbutton I am trying to get a msgbox to pop up and tell the user
> that if they haven't changed the populated data in userform2, then it's not
> necessary to 'add this record to this db'. Not sure what I am doing wrong.
>
> Here's an example of the possible offensive code:
> Dim cbx7a As Long
> cbx7a = ComboBox7.Value
>
> If TextBox1.Value =
> Application.Index(Worksheets("Sheet5").Range("PerslData"), _
> Application.Match(cbx7a, Worksheets("Sheet5").Range("Record_Number"), 0), 2)
> Then
>
> Elseif 'all the other references to the text/combo/toggle boxes buttons.
>
> Else
> MsgBox "It appears that you haven't changed...not necessary to save."
>
> 'where Texbox1 holds a date as text, combobox7 holds a record number but is
> formatted to dislpay 'date entered' and 'name of applicant' when selected so
> that the user can see which person to bring up. "Record_Number" is a named
> cell range on sheet5, that holds the numbers 1 to 2000 in column 'A' (primary
> key sort of)
>
> There are also more procedures within that macro that check certain
> text/combo boxes for entries. One is:
> If TextBox1.Value = "" Then
> MsgBox "Please enter a Date!"
> Exit Sub
> End If
>
> End State: What I would like the procedure to do is compare all of the data
> in userform2(retrieval), against the values it retrieved from spreadsheet 5,
> in the event the user changed any of them, which is allowable. The reason for
> that is so that the user doesn't save a 'record' if he or she didn't make any
> changes to it.
>
> Please help.