PDA

View Full Version : Solved: Data Validation Question



bassfisher
09-30-2005, 01:21 PM
i am newbie to programing http://vbaexpress.com/forum/images/smilies/102.gif and attempting to have a macro run when the value of a cell changes. the value of the cell "FE11" is changed by a formula, from "0" to "1". i would like to dispaly a warning message and play a sound when this cell changes to a "1", to alert the user that a different course of action needs to be taken. any help in this matter would be appreciated http://vbaexpress.com/forum/images/smilies/045.gif !

thanks in advance

bassfisher

Norie
09-30-2005, 01:32 PM
Bassfisher

I suggest you change the title of your post, it gives no indication of what you want to do.

MWE
09-30-2005, 02:36 PM
[QUOTE=bassfisher]i am newbie to programing http://vbaexpress.com/forum/images/smilies/102.gif and attempting to have a macro run when the value of a cell changes. the value of the cell "FE11" is changed by a formula, from "0" to "1". i would like to dispaly a warning message and play a sound when this cell changes to a "1", to alert the user that a different course of action needs to be taken. any help in this matter would be appreciated http://vbaexpress.com/forum/images/smilies/045.gif !

thanks in advance

bassfisher
Welcome to the forum

This can be done using the WorksheetChange procedure. Any code in this procedure executes any time any change is made anywhere in the worksheet. I suggest that you do not "ring a bell" unless there is some possibility that the user is really not paying attention. The warning message will display and halt Excel until the user clicks on the OK button.

Put the following code in the code module for the worksheet where you want this to work Private Sub Worksheet_Change(ByVal Target As Range)

If Range("FE11") = 1 Then
MsgBox "Put appropriate text here", vbCritical + vbOKOnly
End If

End Sub
If the user clicks OK and does not change things so that FE11 is not equal to 1, the message will display again after any change.

BTW, if you are not familiar with the VB editor:


Open the workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject (name.xls) where name is the name of the spreadsheet]
Select the code module for the target worksheet
Copy the code above

Paste the code into the right-hand code window.
Close the VBE
Save the file if desired.

johnske
09-30-2005, 04:07 PM
Hi bassfisher, welcome to VBAX,

I've changed the title of this thread to reflect the question. This can also be fixed by using Data Validation, select FE11 and go to the Data toolbar, select Validation. Under 'Allow' select 'Whole Number' from the dropdown list, select Not Equal To as Data:, then Put 1 as the Value: and click the error alert tab. Choose from Stop | Warning | Information on the LH dropdown tab, then write your title and error message and click OK.

HTH,
John :)

MWE
09-30-2005, 04:25 PM
Hi bassfisher, welcome to VBAX,

I've changed the title of this thread to reflect the question. This can also be fixed by using Data Validation, select FE11 and go to the Data toolbar, select Validation. Under 'Allow' select 'Whole Number' from the dropdown list, select Not Equal To as Data:, then Put 1 as the Value: and click the error alert tab. Choose from Stop | Warning | Information on the LH dropdown tab, then write your title and error message and click OK.

HTH,
John :)
I recommend that you not use Data Validation. It is a "format" attribute that is easily eliminated via copy/paste. Further, validation is a manual input filter, not a value checker. Thus if in cell FE11 there is a formula referring to other cells and the values in those other cells generate a value in FE11 that violates the Validation criteria, the filter will not stop that and no error message will be displayed.

bassfisher
10-01-2005, 09:45 AM
thanks, it works like a charm

Zack Barresse
10-02-2005, 03:25 PM
If you are manually inputting in the cell in question, I recommend Data Validation. But, as MWE explained, you will be inept at checking any data entering this cell if it's a formula. VBA would work, but it's also an option to check the precedent cell(s) for their input.

Remember, it's always best to use native functions to their fullest potential before resulting to VBA (as wonderful as it is :) ).