Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to do edit checks when user moves out of a cell
I have a spreadsheet that the user inputs 4 dates (into 4 different cells)
where those dates are used in an SQL query against a database. Is there a way to perform edit checks once the dates are input so as not to have the SQL query execute until the dates are known to be valid ? Base Period Start Date Base Period End Date Current Period Start Date Current Period End Date Obviously the start dates need to be before the respective end dates, but also the Base Period needs to be prior to the Current Period. Also, no dates can be blank, and none can be greater than yesterday. Is there a way to execute a macro when each cell where these numbers are input loses focus ... or am I better off with performing the total edit checks once all 4 cells are filled in ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to do edit checks when user moves out of a cell
I'd probably do it with a worksheet change event. SOmething like this
Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range Dim ValidDate As Boolean If Target.Count 1 Then Exit Sub ' The 4 ranges can be changed and represent the cells with dates Set myRange = Union(Range("A1"), Range("B2"), Range("C3"), Range("D4")) ValidDate = False If Intersect(Target, myRange) Is Nothing Then Exit Sub 'Code Check for valid dates. When date is valid set ValidDate = True If Not ValidDate Then Target.Select MsgBox ("Date is not a valid date") End If End Sub You'll have to put the code together to determine if the date is valid. -- HTH, Barb Reinhardt "Eric @ BP-EVV" wrote: I have a spreadsheet that the user inputs 4 dates (into 4 different cells) where those dates are used in an SQL query against a database. Is there a way to perform edit checks once the dates are input so as not to have the SQL query execute until the dates are known to be valid ? Base Period Start Date Base Period End Date Current Period Start Date Current Period End Date Obviously the start dates need to be before the respective end dates, but also the Base Period needs to be prior to the Current Period. Also, no dates can be blank, and none can be greater than yesterday. Is there a way to execute a macro when each cell where these numbers are input loses focus ... or am I better off with performing the total edit checks once all 4 cells are filled in ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Attempted edit of protected cell jumps user to another cell | Excel Discussion (Misc queries) | |||
Tweak to code that checks colors of fonts and moves to proper shee | Excel Programming | |||
when exiting combobox, this code moves the selected cell down two rows instead of one | Excel Programming | |||
cell edit by user | Excel Programming | |||
Help with code that checks if a sheet exists | Excel Programming |