Format -Time Error on Excel 97

  • Thread starter Thread starter Pete T
  • Start date Start date
P

Pete T

As soon as I thought I was done with my VBA programming, I get pulled
back in. I have UserForms which pull data from a selected row, the
staff then can update and edit the data to move to a Database....

However, now I find that when the data is pulled, all data is fine,
even the Data textbox (EDate), but the (ETime)textbox shows time as a
decimal (.375, rtc.) I have tried various forms of formatting such as:

ETime.Text = Format (Time, "hh:mm"), but I always get the same Compile
Error, Wrong number of arguments .....

This is very frustrating as I am very near finished. Thanks for any
help.
 
? Format (Time, "hh:mm")
15:46


No reason it shouldn't work. Where is the code. The textbox isn't linked
to a cell is it?
 
Yes it is, I'm passing the value of a cell with an entry of time in
the cell, formatted hh:mm AMPM. The Textbox on the UserForm shows
..xxxxxxx (decimals for the time). Is that the problem? Is there
another format to use in that case?
 
That is the way it behaves with a link and a time or date. I suggest you
break the link and update with code.

Private Sub Worksheet_Change(Target as Excel.Range)
if Target.Count > 1 then exit sub
if Target.Address = "$B$5" then
ActiveSheet.OleObjects("TextBox1").Object.Value = Target.Text
end if
End if
End Sub

An easier approach might be to use a textbox from the drawing toolbar.

Place in on the sheet, select it and go to the formatting toolbar

put in

=B5 and enter

Replace B5 with the cell where the time value will be placed.
 
Thanks Tom I'll try that out. Thanks Again

PS: While in the UserForm -staff have requested comboboxes, I don't
know how to set those up. Do you have a short process for coding the
Comboboxes?
 
I guess too much time had passed - you did say you were working on a
userform - so the drawing toolbar textbox is out. But as I suggested, break
the link and set the value with code.

You can do it in the userform initialize event

Private Sub Userform_Initialize()
Textbox1.Text = Worksheets("Sheet1").Range("B5").Text
End sub

the text value of the range will be formatted as it appears on the sheet.

For a combobox, set the rowsource to the range that contains the choices.
After that, the user makes a selection. If the selection includes dates,
you get the same problem with it not showing as a formatted date in the
textbox portion of the combobox - the list part will look fine.

You can use the click event to clean it up

Private Sub Combobox1_click()
Combobox1.Value = Format(combobox1.Value,"mm/dd/yyyy")
End sub

as an example.
 
Back
Top