Macro to paste value from userform label

Hi and thanks for any help.

I have a userform with a Label called Label1. This label contains a time value formatted like this (00:00:00.00) I need a macro that takes certain portions of this time value (hours, minutes, seconds, etc) and puts the value in a specific cell. I'm not sure how to do this. I have successfully been able to put the whole value in a cell, but I need to be able to separate this by putting the hours, minutes, and seconds in different cells. Here is the code I currently have.

Range("J7").Value = Label1

What I want to do is put the hours in J7, the minutes in K7 and the seconds in L7. I know this is probably really simple, but I'm not very experienced with VBE.

Thanks for any help.

Hi, you'll have to amend this code to however your form is set up ie whether it's called with a button but for my testing purposes I used the form initialization event.

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim rng As Range
    Dim lblStr As String
    Me.Label1.Caption = "04:32:56"
    lblStr = Me.Label1.Caption
    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("J7")
    With rng
        .Value = Left(lblStr, 2)
        lblStr = Replace(lblStr, Left(lblStr, 3), "")
        .Offset(0, 1).Value = Left(lblStr, 2)
        lblStr = Replace(lblStr, Left(lblStr, 3), "")
        .Offset(0, 2).Value = Left(lblStr, 2)
        lblStr = ""
    End With
    
End Sub

There's no doubt other ways to do this but I inserted just string values into the cells, not time formatted. Give it a try and let me know if they have to maintain the time formats.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi CJ-22, try this

Private Sub CommandButton1_Click()
    
    Dim ws As Worksheet
    Dim rng As Range
    Dim lblStr As String
    
    lblStr = Me.Label1.Caption
    Set ws = Worksheets("Sheet 1")
    Set rng = ws.Range("J" & ws.Cells(ws.Rows.Count, 10).End(xlUp).Offset(1, 0).Row)
    With rng
        .ClearFormats
        .Value = Left(lblStr, 2)
        lblStr = Replace(lblStr, Left(lblStr, 3), "")
        .Offset(0, 1).Value = Left(lblStr, 2)
        lblStr = Replace(lblStr, Left(lblStr, 3), "")
        .Offset(0, 2).Value = lblStr
        .Borders.LineStyle = xlContinuous
    End With

End Sub

Originally Posted by CJ-22

1. If the value in Label1 is less than 1 minute then the minutes are placed where the hours go, the seconds are placed where the minutes go, and the decimals are placed where the seconds go. If the value is more than one minute it works great. I have no idea how to fix this and was hoping you could help.

I really don't understand this, you want to change the data output from say 00:00:58.12 to 00:58:12? Under what circumstance would this be required? How exactly is the label being populated? Is it originally a date format?

Sorry, I thought this was solved. I don't understand your requirements now compared to the before and after in the workbook you provided. Instead of going down rows, like in the workbook, you want to go across the columns? Why provide a sample that shows the opposite of what you require. At any rate, I'll work on this and try to resolve it for you.

Maybe this format for the cell(s) would help:
(Not sure what version of excel you are using...)
Format Cells, Number, Custom, edit the format to:
00:00:00.00
This will show leading zeroes for Hours:Minutes:Seconds if they are ZERO, (which is what I understand that you are wanting?)

Edit-
OR
hh:mm:ss.00
as my first suggestion seems to fail

Thank You for posting you're solution !
It is much appreciated, even if it did not come from us
The information is helpful to anyone with a similar problem