Format -Time Error on Excel 97

[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.

 

[Tom Ogilvy]

? 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?

 

[Pete T]

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?

 

[Tom Ogilvy]

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.

 

[Pete T]

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?

 

[Tom Ogilvy]

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.