EXCEL: Making user input Date into cell

[ksgirl2]

Hello,

I was wondering how i can make a user input a date into a cell. I need excel to not let them continue until they do enter a date in. How can I go about doing this? does this question makes sense?...or do i need to explain more..
thanks in advance,


marie

 

[fneily]

VBA has a function called IsDate. Maybe you can figure out how to use it. I went into VBA (ALT+F11), click Insert, Module and type in this little function:

Function HASDATE(cell) As Boolean
HASDATE = IsDate(cell)
End Function

Then in cell B1, I put:
=IF(HASDATE(A1),,"No Date in cell A1&quot

Maybe this can be of use.

Neil

 

[xlbo]

Good response from fneily - should work but that'd be wholly dependant on the process involved. You say you don't want to let them continue until they have entered a date into a cell....
What were they doing before ??
What would they do after ??
Both of these variables need to be known before we can suggest a way to make it work

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[ksgirl2]

Ok. I want the user to go along entering data across the cells.... then when they get to the DATE colmn. i want it mandatory for them to enter a date and not go to the next cell or any cell for that matter. ( i know if i had it my way this file would be in access, but i have to work with it in Excel) Does this help? I see how to get it to check if it is a date but how can i write a macro or function to get a pop up to appear until they enter in a date?

does this help??

thanks.
marie

 

[xlbo]

Ok - this is a bit tricky 'cos of the number of permutations available like how do they get to that cell (after entering data - using tab or enter)
Anyways, if you copy and paste this into the WORKSHEET module of the appropriate sheet, it'll work for if the date needs to be in col H

Public DateIsNext As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If DateIsNext = True Then
If HASDATE(Range(Target.Address)) Then
DateIsNext = False
Else
Application.EnableEvents = False
MsgBox "You must enter a date to continue"
Target.Value = ""
Range(Target.Address).Select
Application.EnableEvents = False
End If
Else
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 8 Then 'this is the date column ref
DateIsNext = True
Else
DateIsNext = False
End If
End Sub

Function HASDATE(c As Range) As Boolean
HASDATE = IsDate(cl)
End Function


Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[ksgirl2]

THANKS! I changed the col. ref and it works for the 1st cell....But that only works for one iteration. The first time i go the cell and enter in incorrect data and hit enter it gives me the error. The 2nd time i enter incorrect data and hit enter it stays there and no error message appears.
ALSO. If i enter a date format **/**/**** and hit enter i STILL get the error message.....
Can you help me with this? Thank you in advance!!


marie

 

[ksgirl2]

Another quick additional question...How can I get it to skip the 1st row which has the title..???
Would it then be like
If Target.Column = 23 AND Target.Row = 2? but then how do i continue down for the rest of the rows? or do i just do an If Target.Row = 1 skip the rest kind of deal. And run the Date Checker for everthing else.

i'm so lost. lol.
thanks in advance.

 

[SkipVought]

Hi,

One way is to protect the sheet, unlocking only the cells that the user will enter data in before protecting the sheet. Then the TAB key will cycle only to the next unlocked cell and Excel will not let any changes occur to any locked cell.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ksgirl2]

Yeah i Got the locking of the sheet part down but i want them to enter a date instead of text. The issue is that they are entering TBA and i want them to set a DEADLINE! thus i want a date format in the delivery date and also i don't want them leaving it blank. Does that make more sense?

thanks
marie

 

[SkipVought]

Well try Data Validation, menu item...

Date/Validation

and choose a DATE range for validation.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ksgirl2]

Oh God why didn't i remember that. Thank you so much. That along with locking down the sheet should work correct?


Thanks!
marie