Formatting entries in userform textbox

I'm using a userform for enter dates and times into a
worksheet. is there a way of formatting the data as it's
input like in an Access form? or is there a way to use an
Access form to enter the data into Excel?

thanks.

I don't use access, but you could validate it when the user is leaving the
textbox:

Option Explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(Me.TextBox1.Value) Then
Me.TextBox1.Value = Format(CDate(Me.TextBox1.Value), "mm/dd/yyyy")
Else
Cancel = True
End If
End Sub

Or maybe you can present the user with a calendar.

http://www.rondebruin.nl/calendar.htm
(From Ron de Bruin's site.)


RobG wrote:

I'm using a userform for enter dates and times into a
worksheet. is there a way of formatting the data as it's
input like in an Access form? or is there a way to use an
Access form to enter the data into Excel?

thanks.

--

Dave Peterson

Hi!

I don't know how complex your requirements are, but if they are fairly
simple, the built-in Data Form... input facility will do what you
appear to need on dates and times.

Enter a date as 12/5/03 and format the column it goes to as you wish.
12/5 would transcribe as current year.

Similarly for time, but use 11:55 etc.

Alf


---
Message posted from http://www.ExcelForum.com/

"RobG" wrote ...
I'm using a userform for enter dates and times into a
worksheet. is there a way of formatting the data as it's
input

Use the Microsoft Masked Edit Control (MSMASK32.OCX)

Jamie.

--

This was on my pc (win98), but don't you need to worry about licensing and
distribution rights if you use this?

Jamie Collins wrote:

"RobG" wrote ...
I'm using a userform for enter dates and times into a
worksheet. is there a way of formatting the data as it's
input

Use the Microsoft Masked Edit Control (MSMASK32.OCX)

Jamie.

--

--

Dave Peterson

Dave Peterson wrote ...

This was on my pc (win98), but don't you need to worry about licensing and
distribution rights if you use this?

Dave,
Worry, with my MSDN subscription? :-)

Seriously, it's a good and valid point, of course. I guess it depends
on the circumstances. If the app in question is for the OP's personal
use on their machine, then there's no issue. If they are developing
for other personnel within their organization, the chances are they
will have the correct licensing in place e.g. each user machine may
have the same build. If they developing the app for a client, a great
way to obtain a license to distribute is to charge it to the first
client that needs it g. If they are developing a third party app
then chances they should have a 'developer' edition or a software
development package such as Visual Studio anyhow.

It would be useful to know which controls ship with each version of
Excel, Office and Windows. With such a reference one could say, if
someone has version x then they will have component y already on their
machine and distribution is no issue. Anyone know of such a resource?

Jamie.

--

I searched (cursory, though) the KB looking for "activex excel included" and
found this article for Access:

http://support.microsoft.com/default...b;en-us;825796

And for xl97:
http://support.microsoft.com/default...b;EN-US;169813
which had a link to:
http://support.microsoft.com/default...b;EN-US;163535


My bet is that there are lots of restrictions that I couldn't find. (But it
really isn't much fun looking! vbg)



Jamie Collins wrote:

Dave Peterson wrote ...

This was on my pc (win98), but don't you need to worry about licensing and
distribution rights if you use this?

Dave,
Worry, with my MSDN subscription? :-)

Seriously, it's a good and valid point, of course. I guess it depends
on the circumstances. If the app in question is for the OP's personal
use on their machine, then there's no issue. If they are developing
for other personnel within their organization, the chances are they
will have the correct licensing in place e.g. each user machine may
have the same build. If they developing the app for a client, a great
way to obtain a license to distribute is to charge it to the first
client that needs it g. If they are developing a third party app
then chances they should have a 'developer' edition or a software
development package such as Visual Studio anyhow.

It would be useful to know which controls ship with each version of
Excel, Office and Windows. With such a reference one could say, if
someone has version x then they will have component y already on their
machine and distribution is no issue. Anyone know of such a resource?

Jamie.

--

--

Dave Peterson

Dave Peterson wrote ...

I searched (cursory, though) the KB looking for "activex excel included" and
found this article for Access:

http://support.microsoft.com/default...b;en-us;825796

And for xl97:
http://support.microsoft.com/default...b;EN-US;169813
which had a link to:
http://support.microsoft.com/default...b;EN-US;163535


My bet is that there are lots of restrictions that I couldn't find. (But it
really isn't much fun looking! vbg)

Dave, Thanks for that. Yeah, I've looked before but without much luck.

Jamie.

--