Macro to paste value from userform label

**Mordred** · 06-07-2013, 07:42 PM

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

**CJ-22** · 06-08-2013, 05:48 PM

Thanks so much for the help. This is working really well. There are two issues that I was hoping you can help me address.

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.

2. Is there any way to have this move to the right instead of down. For example, once the input button is clicked it places the values in J7:L7. The next time input is clicked the values go to J8:L8. I would rather it be placed in M7:O7.

Thanks again for all the help.

**CJ-22** · 06-11-2013, 03:08 PM

Bump. Any ideas?

**Mordred** · 06-11-2013, 03:25 PM

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?