Custom form(s) (Excel 2K/SR-1)

Topic

New Reply

I have custom forms that I use for loaning laptops and LCD projectors. All is well except for a text box that I use to capture the time. On the spreadsheet, the cell is formatted to TIME, hh:mm:ss AMPM. On one of the forms the time displays correctly, on another form, using the same cell, the time displays as a decimal. Any suggestions would be greatly appreciated as always. TIA.

Reply | Quote

Replies

Instead of binding the text box to a cell through its ControlSource property, use code to set and read its value. For example in the UserForm_Inititialize event procedure:

Me.txtTime = Format(Range(“G4”), “hh:mm:ss AM/PM”)

and in the After Update event of the text box (or in the On Click event of a command button):

Range(“G4”) = TimeValue(Me.txtTime)

Reply | Quote

Thanks for responding, Hans.

More problem explanation needed: I create a record using a form. I set the time using the TIME function. The cell on the spreadsheet is formatted to display the time as time. When I save the record the spreadsheet is updated nicely. When I open another form to edit the record, the time displays as decimal on the form. I used your suggestion and it worked fine for the first record. When I added a 2nd record, the time from the first record displayed. I checked the properties for the text box and saw nothing that would bind the cell through its ControlSource property. I tried to zip the spreadsheet to attach, but it will not go smaller than 134kb. Any other suggestions? Thanks again.

Reply | Quote

When you move to a new record, your code will have to handle it, e.g.

Dim lngRow As Long
lngRow = … ‘ determine row for current record
Me.txtTime = Format(Range(“G” & lngRow), “hh:mm:ss AM/PM”)

If you still need help: make a copy of the workbook, and remove items not essential to the problem, and remove most of the data, then zip it.

Reply | Quote

Hans, The smallest I can possibly get the zip file is 122kb.

Reply | Quote

An Excel workbook on average compresses to about 1/4 of the original size, so that would mean that it is impossible to demonstrate the problem in less than 500 KB. I can hardly believe that.

But perhaps you can provide more details about your userform instead of posting the workbook.

Reply | Quote

Got it! Form Check Out assigns the laptop and the projector to the client. Form Edit makes changes as necessary. The time txtbox on the Edit form is the culprit. Thanks again.

Reply | Quote

You shouldn’t have code in the TxtChkOutTime_Change event – it makes no sense and it isn’t necessary.
Change the line in the UserForm_Initialize event that refers to this text box:

TxtChkOutTime.Value = Format(ActiveCell.Offset(0, 5), “hh:mm:ss AM/PM”)

and also in the cmdOK_Click event:

ActiveCell.Offset(0, 5) = TimeValue(TxtChkOutTime.Value)

Reply | Quote

Hans, Once again, thanks for all the help. Worked great.

Reply | Quote