Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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("Pers lData"), _ 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Text/combobox values against cells in spreadsheet
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("Pers lData"), _ 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare values of two cells and display the lowest. | Excel Worksheet Functions | |||
Entering Values into Combobox in Spreadsheet | Excel Discussion (Misc queries) | |||
Compare 2 cells values with one IF statement | Excel Worksheet Functions | |||
How do you compare text in cells | Excel Worksheet Functions | |||
Values from bold cells to combobox? | Excel Programming |