Formatting entries in userform textbox

R

RobG

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.
 
D

Dave Peterson

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.)
 
A

AlfD

Hi!

I don't know how complex your requirements are, but if they are fairl
simple, the built-in Data > Form... input facility will do what yo
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.

Al
 
J

Jamie Collins

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
Click to expand...

Use the Microsoft Masked Edit Control (MSMASK32.OCX)

Jamie.

--
 
D

Dave Peterson

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

Jamie Collins

Dave Peterson said:
This was on my pc (win98), but don't you need to worry about licensing and
distribution rights if you use this?
Click to expand...

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.

--
 
J

Jamie Collins

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.aspx?scid=kb;en-us;825796

And for xl97:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;169813
which had a link to:
http://support.microsoft.com/default.aspx?scid=kb;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>)
Click to expand...

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

Jamie.

--
 
Top