?
Solved

Can't display the Time in a TextBox on a UserForm.

Posted on 2003-02-25
9
Medium Priority
?
318 Views
Last Modified: 2010-04-07
I've created a series of UserForms using ActiveX controls. There are a number of scripts that run in the background based on user input in the Form. One of those scripts allows the user to click on the word "Time:" and it will fill in the current time in a textbox... The problem I am having is this... the Time will not display in a "hh/mm/ss AM/PM" format. It only displays as the decimal value that the time represents.

Now,.. here are the details.

I have tried a number of workarounds, to no avail. Basically, I'm in a Catch22. I need to save the current Time for future use. I need it to be retained in the TextBox itself (i.e. visually in the form) and I need it to be retained in the background (saved to a Cell reference.) But if I bind the TextBox to a cell, the time displays as a decimal value, if I don't bind it to a cell, the time will display properly in the TextBox, but then there's no way to retain the value after you close the form.

Things I have tried:

1) Leave the TextBox unbound, write a script that changes the TextBox Value to the current time, and then change the appropriate cell to the same value. This doesn't work because the value gets saved in the background (in a cell), but as soon as you close the form, the value that is displayed in the TextBox is lost. I have tried using both the .Value and .Text TextBox Properties to set the time in the TextBox, and neither method gets retained once the form is closed.

2) I have tried using "Format" while setting the Value, to display the proper format. (i.e. TextBox1.Value = Format(Now(), "h:mm:ss AM/PM")). But once again, if it's bound to a cell, it just changes to a decimal value.

3) The Cell that the TextBox is linking to has been formatted to the proper Time display (hh/mm/ss AM/PM)


The cell reference is what's causing the problem (obviously). When I click on the "Time:" label (which generates the current time in the textbox) I can see the time properly at first. But as soon as I set the focus on another TextBox, the value gets written to the cell and that's when it turns into a decimal value. So I'm very stuck here. I need to save the value both in the cell (in the background) and in the TextBox (for visual display), but it appears that it won't let me do both in the proper format (hh/mm/ss). The odd thing is, it doesn't do this to the Date. I have the same exact code dumping the current Date into a different text box, and that displays and saves to a cell in the proper format (mm/dd/yy).


Any suggestions?


WATYF



P.S. This is my first submission on the site, so forgive me if this isn't enough points.
0
Comment
Question by:WATYF
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

by:bob_online
ID: 8018558
Can you use two text boxes?  One with visible = false and bound to the cell, and one with visible = true, not bound.  When you click time, load time into the bound box and load Format(time) into the not bound box.  When you load the form, set the value (or text) property of the unbound to format(the value in bound)?

I think this should work.
0
 
LVL 5

Expert Comment

by:jayeshshah
ID: 8018739
try displaying the time by the statement shown below.

Text1.Text = Time


K'Regards

Jayesh
0
 

Accepted Solution

by:
naleon earned 400 total points
ID: 8018790
Check for the format property of the cell, try setting this to string type. Maybe it's works.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Author Comment

by:WATYF
ID: 8018967
Oh yeah... should have been more specific. I'm aware that I could change a number of things and gets similar results. For example, I could combine the Date and Time into one box...

but what I'm really looking for is a reason/solution to why this accursed textbox won't display the Time properly. If I can't find one then I'll just go with the best "alternate route" suggestion.


Bob... I can't do that because the visible textbox will not retain its value. So even though, at first, it will show the Time (and the hidden Textbox behind it will store the value), as soon as I close the form, the value in the visible textbox will be erased.

Jay... that's one of the methods I am currently using to assign the Time to the TextBox. As I mentioned, it will not retain that value when the form closes, unless I bind it to a Cell, in which case it does not display properly.

Naleon... I will try that.


WATYF
0
 
LVL 11

Author Comment

by:WATYF
ID: 8019095
Oh yeah... should have been more specific. I'm aware that I could change a number of things and gets similar results. For example, I could combine the Date and Time into one box...

but what I'm really looking for is a reason/solution to why this accursed textbox won't display the Time properly. If I can't find one then I'll just go with the best "alternate route" suggestion.


Bob... I can't do that because the visible textbox will not retain its value. So even though, at first, it will show the Time (and the hidden Textbox behind it will store the value), as soon as I close the form, the value in the visible textbox will be erased.

Jay... that's one of the methods I am currently using to assign the Time to the TextBox. As I mentioned, it will not retain that value when the form closes, unless I bind it to a Cell, in which case it does not display properly.

Naleon... I will try that.


WATYF
0
 
LVL 11

Author Comment

by:WATYF
ID: 8019115
Oh yeah... should have been more specific. I'm aware that I could change a number of things and gets similar results. For example, I could combine the Date and Time into one box...

but what I'm really looking for is a reason/solution to why this accursed textbox won't display the Time properly. If I can't find one then I'll just go with the best "alternate route" suggestion.


Bob... I can't do that because the visible textbox will not retain its value. So even though, at first, it will show the Time (and the hidden Textbox behind it will store the value), as soon as I close the form, the value in the visible textbox will be erased.

Jay... that's one of the methods I am currently using to assign the Time to the TextBox. As I mentioned, it will not retain that value when the form closes, unless I bind it to a Cell, in which case it does not display properly.

Naleon... I will try that.


WATYF
0
 
LVL 11

Author Comment

by:WATYF
ID: 8019308
Well I feel stupid. :op

Not only was my last reply somehow duplicated three times... but on top of that, I overlooked an incredibly simplistic answer. I can't believe I didn't think of that.... but I guess that's what this place is for.

Well,... I changed the source Cell to "Text" format, and then ran the code, and Excel crashed. (which had me worried that the TextBox was hell-bent on not letting me solve this problem.)

But I re-opened Excel and changed the Cell to a Text format again, then I got rid of the code that drops the value directly into the cell, bound the TextBox to the cell, and ran it, and it worked. The Data was saved to the cell, and it was being displayed properly in the TextBox, and the value in the TextBox was being retained permantenly.


Thanks Naleon.


WATYF
0
 

Expert Comment

by:niv3k
ID: 8019376
You may want to try the .NumberFormat property of the cell, also.  You can set that programmatically with a string, and you ensure that the value stored is the actualy date, which in memory is stored as a number, not text.

.Cells(X,Y).NumberFormat = "hh/mm/ss AM/PM"

Kevin
0
 
LVL 11

Author Comment

by:WATYF
ID: 8019445
Thanks for the follow-up... but actually, that's what was causing the problem the whole time. I had the cell set to "hh/mm/ss AM/PM" format, and it would store the number (behind the scenes) as a decimal value. In the Cell it would display it as the "hh/mm/ss" format, but in the TextBox on the form, it would show up as the decimal value (that was being stored in the background). So changing the Cell to a text format kept that from happening.


The one thing I still don't understand is why the Date cell didn't have the same problem. Dates are also stored in the background as numbers, but for some reason, the Date (even when the cell was formatted as mm/dd/yy) displayed just fine in the TextBox.

go figger.


WATYF
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question