+ Reply to Thread
Results 1 to 15 of 15

Macro to paste value from userform label

  1. #1
    Forum Contributor
    Join Date
    12-16-2004
    Posts
    125

    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.

    Please Login or Register  to view this content.
    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.
    Last edited by CJ-22; 06-20-2013 at 02:25 PM.

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Macro to paste value from userform label

    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.
    Please Login or Register  to view this content.
    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.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Forum Contributor
    Join Date
    12-16-2004
    Posts
    125

    Re: Macro to paste value from userform label

    Hi Mordred,

    Thanks for the reply. This almost does what I need it to do. It places the value 4 in J7, 32 in K7, and 56 in L7, which are the numbers in your code, but it does not use the value from the label. The label changes frequently like a clock and I need it to place these values from the label. I also noticed that it did not have the decimal values with the seconds. Can the decimals be added to the second value?
    Thanks again for the help.

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Macro to paste value from userform label

    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

  5. #5
    Forum Contributor
    Join Date
    12-16-2004
    Posts
    125

    Re: Macro to paste value from userform label

    Here is a sample of what I want to do.
    Attached Files Attached Files

  6. #6
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Macro to paste value from userform label

    Hi CJ-22, try this
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    12-16-2004
    Posts
    125

    Re: Macro to paste value from userform label

    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.

  8. #8
    Forum Contributor
    Join Date
    12-16-2004
    Posts
    125

    Re: Macro to paste value from userform label

    Bump. Any ideas?

  9. #9
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Macro to paste value from userform label

    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.

  10. #10
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Macro to paste value from userform label

    Quote Originally Posted by CJ-22 View Post
    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?

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Macro to paste value from userform label

    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
    Last edited by dredwolf; 06-11-2013 at 10:14 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  12. #12
    Forum Contributor
    Join Date
    12-16-2004
    Posts
    125

    Re: Macro to paste value from userform label

    I'm sorry for the confusion. I was trying to keep things simple and instead made it difficult. I intend to have a button on the userform for each row (not created yet). The button for row 7 will input the data from the label to J7:L7. The next time it is pressed it will input to M7:O7. The problem I'm having is when the hours and minutes show 00, everything shifts to the left one column. For example, if the label shows 01:15:22.17 then J7 shows 01, K7 shows 15, and L7 shows 22.17. If the label is 00:00:22.17, then J7 shows 00, K7 shows 22, and L7 shows 17. Instead J7 should show 00, K7 should show 00, and L7 should show 22.17. I hope this is clear and again I'm sorry for the confusion and the misleading workbook.

  13. #13
    Forum Contributor
    Join Date
    12-16-2004
    Posts
    125

    Re: Macro to paste value from userform label

    Hi Mordred,

    I'm sorry for all the confusion, but I really could use your help. I have reread my posts and realize how confusing they were. My first question was to only get the values from the label in the right cell. I wanted the hours in one cell, the minutes in the next cell, and the seconds and decimals together in the next cell. Your code worked great as long as the value in the label was more than 00:01:00.00 (one minute). If it was less than one minute the seconds and decimal values were placed in different cells. Once I knew how to get the values from the label in the right place I was going to use an If/Then arguement to place the values to the right, but your code anticipated this only down. If you or anyone is willing to help I would appreciate it.

    Thanks again.

  14. #14
    Forum Contributor
    Join Date
    12-16-2004
    Posts
    125

    Re: Macro to paste value from userform label

    I wanted to thank everyone who offered help. When this thread died out I posted on a different forum and found an answer. Here is a link for anyone who might be interested.

    http://www.ozgrid.com/forum/showthread.php?t=179744

    Thanks again for all your time.

  15. #15
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Macro to paste value from userform label

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1