join two test boxes as time 1

[2ks]

Hi everyone

Please could someone help me with this.

I have two textboxes
txthr and txtmin
and want to join them when prcd btoon is pressed. I want to join them as time in format hh:mm. I think I am supposed to get the to join as a string but dont know how. They will then transfer to an excel sheet.

Then I want to be able to reverse the hh:mm back to txthr an txtmin if the user form is selected for that particular line so users can amend if necessary

Many thanks

 

[SkipVought]

Hi,

Check out the TimeSerial function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2ks]

I have looked up time serialfuntion and I dont understand it and I am not sure it is what I need unless it creates a time input box in the user form.

The hours textbox is not restricted to 23 hrs. It could be 245 hours as it represents a total number of hours worked by a team in a day. The minute text box obviously is maxed at 59.

I need to then combine "txtbox1:txtbox2" and insert that in eg Range(HRS_Wkd & irow).Value = frmhrs.combination of textbox1:textbox2.

However if frmhrs is opened again for a date that already has populated info I want to look for hrs_wkd & irow and split the "txtbox1:txtbox2" back to separate txtbox1:txtbox2 so users can amend

I really do appreciate any help

 

[SkipVought]

I have looked up time serialfuntion and I dont understand it

Please explain what you do not understand.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2ks]

Skip

I dont understand how to use it?

I am a bit clunky with my knowledge and most things I can do are populating cells, if and etc.

Sorry
Nikk

 

[SkipVought]

Debug.Print TimeSerial(txthr.Text,txtmin.Text,0)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Golom]

Given that your hours field (txthr.text) can exceed 24 hours, you probably don't want to use TimeSerial. The reason being that TimeSerial returns a DateTime value that incorporates a date and a time with the hours portion restricted to the range 0 - 23.

Perhaps you want to just concatenate the two fields into a character string.

Dim CombinedTime As String
CombinedTime = Txthr.text & ":" & txtMin.text

To split that back into separate hours and minutes fields

txtHr.text = Split(CombinedTime, ":")(0)
txtmin.text = Split(CombinedTime, ":")(1)

 

[SkipVought]

A STRING gets you nothing except a DISPLAY. You cannot do arithmetic on a string.

Yes, TimeSerial returns a Date/Time value, that can be DISPLAYED as Date Time OR anything else you want.

Bottom line: a STRING is virtually useless except for LOOKING AT!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Golom]

SkipVought

Well yes ... a string is a display only representation.

I was just trying to give the OP a way to display his (or her) > 24 hours fields without getting into the inner mysteries of DateDiff and DateTime representations.

As I understood the issue, (s)he is trying to represent an elapsed time and not a point in time. As we are aware, that can be done with the artful use of DateTime fields but the mechanism is not obvious and is prone to error.

I may have missed it but I didn't see a specific reference to needing to do calculations on the combined fields. If such a calculation is required then the string suggestion is inappropriate.

 

[SkipVought]

I think I am supposed to get the to join as a string but dont know how. They will then transfer to an excel sheet.

    With Cells(1, 1)
       .Value = TimeSerial(txthr.Text, txtmin.Text, 0)
       .NumberFormat = "[hh]:mm"
    End With
    'to reverse
    With Cells(1, 1)
       txthr.Value = Hour(.Value) + Int(.Value) * 24
       txtmin.Value = Minute(.Value)
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2ks]

You two are awesome and I have got the first art of Golom coding working and writing to sheet with ease and although text as such the excel sheet is counting nicely. using this
Sorry been working away couple of days

Working - updating excel sheet nicely

Private Sub CommandButton1_Click()

Dim CombinedTime As String
irow = ActiveCell.Row

CombinedTime = texta.Text & ":" & textb.Text

Range(Timerow & irow).Value = CombinedTime
Range(Timerow2 & irow).Value = CombinedTime

End Sub


now the coding to bring that entry back when the userform is initialized I cannot get working so I am obviously missing a trick yet so close. If this can be recoded then I should be done here

Not working - user form still loading blank

Private Sub UserForm1_initialize()

Dim CombinedTime As String

txthr.Text = Split(CombinedTime, ":")(0)
txtmin.Text = Split(CombinedTime, ":")(1)

End Sub

 

[2ks]

sorry wong code

You two are awesome and I have got the first art of Golom coding working and writing to sheet with ease and although text as such the excel sheet is counting nicely. using this.

Sorry been working away couple of days

Working - updating excel sheet nicely

Private Sub CommandButton1_Click()
Dim CombinedTime As String
irow = ActiveCell.Row
CombinedTime = txthr.Text & ":" & txtmin.Text
Range(Timerow & irow).Value = CombinedTime
End Sub

now the coding to bring that entry back when the userform is initialized I cannot get working so I am obviously missing a trick yet so close. If this can be recoded then I should be done here

Not working - user form still loading blank

Private Sub UserForm1_initialize()
Dim CombinedTime As String
txthr.Text = Split(CombinedTime, ":")(0)
txtmin.Text = Split(CombinedTime, ":")(1)
End Sub

Final thanks - hopefully

 

[SkipVought]

Did you at least TRY my approch?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2ks]

Skip

I did try it an nothing happened. I dont think I got the With Cells (1,1) bit right at all. I guess I dont know a whole routine for it

 

[Golom]

The problem with this

Private Sub UserForm1_initialize()
Dim CombinedTime As String

txthr.Text = Split(CombinedTime, ":")(0)
txtmin.Text = Split(CombinedTime, ":")(1)
End Sub

is that "CombinedTime" is a local variable in the Sub and, as such, is initialized to an empty string. You need to load it with the concatenated hh:nn string from the cell where it appears.

 

[2ks]

So are you saying this

Private Sub UserForm1_initialize()
Dim CombinedTime As String

CombinedTime.Value = Range(Timerow & irow).Value

txthr.Text = Split(CombinedTime, ":")(0)
txtmin.Text = Split(CombinedTime, ":")(1)
End Sub

 

[Golom]

I guess I am ... depending on how "Timerow" and "irow" are defined.

Obviously they need to exist and have the correct values when the Sub is called.

BTW ... "CombinedTime" is defined as a String so it doesn't have a ".Value" property. Just use

CombinedTime = Range(Timerow & irow).Value

 

[2ks]

irow is defined as

irow = ActiveCell.Row

and timerow is a global const look up for a column so whatever date line it picks the active row.

Will try soon and hopefully put the saga to bed

 

[2ks]

AAArrrgghh

What Am I doing wrong.

I can write the time back to the main spreadsheet.

Then when I relaunch the the userform through CmdStaffData_Click() the StaffData.Show works fine showing the form.

The time did pull through but only the last entry I put in. It was expecting it to follow the date that is relevant hence the irow = ActiveCell.Row

This is doing my brain in!!