Data validation/Formatting input

[Wescotte]

Is it possible to force the user to enter text in a very specific
format like for entering dates or similar syntax specific input?

Also, I setup a custom format of 0000"."0000"."00

When the user enters 123 it it switched to
0000.0001.23

Is it possible to reverse it so it would become 1230.0000.00 instead?

Thanks
Eric

 

[JE McGimpsey]

XL's parser runs before both Validation and any event macros, so one
can't force an entry in any particular format.

If you use a userform, you can check each keystroke as it's entered into
a textbox, for example.

OTOH, why do you care what format the date is entered as, as long as
it's a date (which you can test, either by formula or event macro)? Just
format the cell the way you want to display it.

As for the 0000\.0000\.00 format, AFAIK, there's no way to fill from the
left without using an event macro to manipulate the entry. Perhaps
something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target(1)
If Not Intersect(.Cells, Range("A1")) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(.Value) Then
.ClearFormats
.Value = Left(Replace(.Text, ".", "") & _
String(10, "0"), 10)
.NumberFormat = "0000\.0000\.00"
Else
.Clear
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[Guest]

I am trying to replace a value within a macro. I don't know how to write the
syntax to do this.

a line of code from the macro
ActiveCell.FormulaR1C1 = "1/1/2004"

There are 31 lines of code for all the dates (starting date, ending date)
Now I want to replace the "1" with a "2" to do February. How can this be
done?

 

[JE McGimpsey]

Your question is, at best, ambiguous. Which "1" are you looking to
replace? How is your data laid out? Do you have 31 sets of start and end
dates?

Perhaps something like???

With Range("A1")
.Value = #1/1/2004#
.AutoFill Destination:=.Resize(31, 1), Type:=xlFillMonths
End With

 

[Guest]

I apologize for the confusion. At the time I wrote that I was feeling
desperate as I needed to do a rush job and my mind was blank. I found the
answer by using the replace option off the menu and only putting what I
wanted replaced without any other syntax. That did the trick. Thank you for
replying.