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).
P.S. This is my first submission on the site, so forgive me if this isn't enough points.