How to display a hh:mm:ss cell value in a forms text control

[Parvaiz]

Hi I have an Excel spreadsheet that contains times in the format of hh:mm:ss
I have created a simple form and I would like it to display the value of eg:
cell B1 = 20:35:00 (8 35 pm)
I put B1 in the Forms Text Box Control Source, but instead of displaying
20:30:00
I get 0.857638888888889

How do I get the text control box on the form to display in the same way as
the cell in the spreadsheet?

I have a column containing several rows of times in the spreadsheet. I guess
that if I can get one to show correctly, I should hopefully then be able to
apply the same logic to the rest of the times.

Any help would be very much appreciated as I only have a day left to hand
his in!

 

[francis]

are you able to apply Custom format [hh:mm:ss] to it via
Format >> Cells >> Custom>> Type?

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Parvaiz]

Hi Francis,
Yes - I already did that to the cells within the spreadsheet itself. So in
the spreadsheet, theye are picking up the correct format. Its just when I
refer to a cell via the form text box control's 'Control Source', i put in
for example B1 to refer to cell B1 but the form text box displays a long
decimal value which meens nothing.

All I want is say the cell B! value of 20:35:00 to be displayed as 20:35:00

are you able to apply Custom format [hh:mm:ss] to it via
Format >> Cells >> Custom>> Type?

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

Hi I have an Excel spreadsheet that contains times in the format of hh:mm:ss
I have created a simple form and I would like it to display the value of eg:
cell B1 = 20:35:00 (8 35 pm)
I put B1 in the Forms Text Box Control Source, but instead of displaying
20:30:00
I get 0.857638888888889

How do I get the text control box on the form to display in the same way as
the cell in the spreadsheet?

I have a column containing several rows of times in the spreadsheet. I guess
that if I can get one to show correctly, I should hopefully then be able to
apply the same logic to the rest of the times.

Any help would be very much appreciated as I only have a day left to hand
his in!

 

[Sheeloo]

Instead of assigning
Cells(1,2) to the text box
assign
Application.Text(Cells(1, 2).Value, "hh:mm AM/PM")

Hi Francis,
Yes - I already did that to the cells within the spreadsheet itself. So in
the spreadsheet, theye are picking up the correct format. Its just when I
refer to a cell via the form text box control's 'Control Source', i put in
for example B1 to refer to cell B1 but the form text box displays a long
decimal value which meens nothing.

All I want is say the cell B! value of 20:35:00 to be displayed as 20:35:00

are you able to apply Custom format [hh:mm:ss] to it via
Format >> Cells >> Custom>> Type?

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

Hi I have an Excel spreadsheet that contains times in the format of hh:mm:ss
I have created a simple form and I would like it to display the value of eg:
cell B1 = 20:35:00 (8 35 pm)
I put B1 in the Forms Text Box Control Source, but instead of displaying
20:30:00
I get 0.857638888888889

How do I get the text control box on the form to display in the same way as
the cell in the spreadsheet?

I have a column containing several rows of times in the spreadsheet. I guess
that if I can get one to show correctly, I should hopefully then be able to
apply the same logic to the rest of the times.

Any help would be very much appreciated as I only have a day left to hand
his in!

 

[francis]

the number 0.857638888888889 is the serial number for 20:35:00, Excel
convert the time to a number.

I think you need a helper column, you may place this column out of the way
from your dataset and you can hide this, assume that this will be at col Z1,
place this formula
=TEXT(B1,"h:mm:ss"), this will change the time into a text
and link your text box to Z1, you shpuld now see 20:35:00

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

Hi Francis,
Yes - I already did that to the cells within the spreadsheet itself. So in
the spreadsheet, theye are picking up the correct format. Its just when I
refer to a cell via the form text box control's 'Control Source', i put in
for example B1 to refer to cell B1 but the form text box displays a long
decimal value which meens nothing.

All I want is say the cell B! value of 20:35:00 to be displayed as 20:35:00

are you able to apply Custom format [hh:mm:ss] to it via
Format >> Cells >> Custom>> Type?

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

Hi I have an Excel spreadsheet that contains times in the format of hh:mm:ss
I have created a simple form and I would like it to display the value of eg:
cell B1 = 20:35:00 (8 35 pm)
I put B1 in the Forms Text Box Control Source, but instead of displaying
20:30:00
I get 0.857638888888889

How do I get the text control box on the form to display in the same way as
the cell in the spreadsheet?

I have a column containing several rows of times in the spreadsheet. I guess
that if I can get one to show correctly, I should hopefully then be able to
apply the same logic to the rest of the times.

Any help would be very much appreciated as I only have a day left to hand
his in!

 

[Shane Devenshire]

Hi,

Are you talking about the Form toolbar's Label control, because there is no
textbox control on the Form's toolbar? Or maybe you are talking about a
UserForm in the Visual Basic Editor? Or are you talking about the Control
Toolbox's textbox control, unfortunately there is not Control Source option
for this control.

If you are trying to do this on a user form then forget the Control Source
property you need to use code, I would put it in the Forms Initialize
procedure as follows:

Private Sub UserForm_Initialize()
Me.TextBox1 = Format(Sheets("Sheet1").Range("B1"), "hh:mm:ss")
End Sub

Where Sheet1!B1 is the control source.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Hi Francis,
Yes - I already did that to the cells within the spreadsheet itself. So in
the spreadsheet, theye are picking up the correct format. Its just when I
refer to a cell via the form text box control's 'Control Source', i put in
for example B1 to refer to cell B1 but the form text box displays a long
decimal value which meens nothing.

All I want is say the cell B! value of 20:35:00 to be displayed as 20:35:00

are you able to apply Custom format [hh:mm:ss] to it via
Format >> Cells >> Custom>> Type?

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

Hi I have an Excel spreadsheet that contains times in the format of hh:mm:ss
I have created a simple form and I would like it to display the value of eg:
cell B1 = 20:35:00 (8 35 pm)
I put B1 in the Forms Text Box Control Source, but instead of displaying
20:30:00
I get 0.857638888888889

How do I get the text control box on the form to display in the same way as
the cell in the spreadsheet?

I have a column containing several rows of times in the spreadsheet. I guess
that if I can get one to show correctly, I should hopefully then be able to
apply the same logic to the rest of the times.

Any help would be very much appreciated as I only have a day left to hand
his in!