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.
[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 :)
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 :) ).
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.