space then date causes error

  • Thread starter Thread starter ss
  • Start date Start date
S

ss

On my spreadsheet they have to type in a date and other calculations are
taken from this point. Of the hundreds that have used it there have been
no problems, however as always there will always be one.......

When they fill in the date they type a space and then type the date,
this throws up errors in the other calculations.

Not altogether important but is there an easy fix for this, so that if
anyone types a space it is ignored, I dont want to get involved in
VBA/macro stuff.
 
Right click the sheet tab>view code>insert this>modify range to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1:z10")) Is Nothing Then
Target = Application.Trim(Target)
End If
End Sub
 
Right click the sheet tab>view code>insert this>modify range to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1:z10")) Is Nothing Then
Target = Application.Trim(Target)
End If
End Sub
Click to expand...

Don, thanks for the reply & solution. One question though, will this
throw up `contains macro` warning if emailed to others as this is likely
to cause other issues.
 
Don, thanks for the reply & solution. One question though, will this
throw up `contains macro` warning if emailed to others as this is likely
to cause other issues.
Click to expand...

Of course. So save as an .xlsx file which automatically strips all
macros or delete the macros and save as a different name before
sending.
 
Back
Top