Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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)

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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)

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •