Data validation/Formatting input

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

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








In article <[email protected]>,
"Wescotte" <[email protected]> wrote:

> 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?

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" wrote:

> 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
>
>
>
>
>
>
>
>
> In article <[email protected]>,
> "Wescotte" <[email protected]> wrote:
>
> > 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?
>

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



In article <[email protected]>,
lschuh <[email protected]> wrote:

> 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?

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.

"JE McGimpsey" wrote:

> 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
>
>
>
> In article <[email protected]>,
> lschuh <[email protected]> wrote:
>
> > 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?
>