Results 1 to 4 of 4

Thread: Transfer Userform label value to Sheet as Currency
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2006
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Transfer Userform label value to Sheet as Currency

    I have a userform that have 5 inputs and 2 Label(Result Boxes)
    1. Part number
    2. Cost
    3. System Count
    4. Physical Count
    5. What to do with the variance.
    Labels as
    1. Total Cost
    2. Variance

    I am trying to copy/transfer the data to a spreadsheet. Everything goes ok till I try and save the Total Cost to the sheet. The spreadsheet for the other cells are formatted as Currency so that format works. But with the What to do with the variance. I let them put 1 of 4 option and then it pulls that option and the total cost to the spreadsheet. Since it is text and numbers it is formated on sheet as Text. The result that shows up on the sheet is: Adjust to GL5394 - -34.44 it should be: Adust to GL5394 - -$34.44 or Adjust to GL5394 - ($34.44)

    How do i get it to change it to the Currency part.

    Thanks for your time.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transfer Userform label value to Sheet as Currency

    Not sure if you're using a formula or entering the text in the cell directly. If the first, you need to use the TEXT() worksheet function to format the value (34.44) as currency. If you're doing it in VBA, then you need to use the Format() function.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular gsistek's Avatar
    Join Date
    Apr 2011
    Location
    In Your Mind
    Posts
    660
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transfer Userform label value to Sheet as Currency

    Can you copy your code?
    Work: Windows 7 / Excel 2013
    Home: Windows 7 / Excel 2007

  4. #4
    Board Regular
    Join Date
    Feb 2006
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transfer Userform label value to Sheet as Currency

    Public totalcost


    With Sheets("saveddata")
    .Cells(lrow, 1).Value = Date
    .Cells(lrow, 2).Value = partnumber
    .Cells(lrow, 3).Value = physicaltotal
    .Cells(lrow, 4).Value = m2ktotal
    .Cells(lrow, 5).Value = variance
    .Cells(lrow, 6).Value = unitcost
    .Cells(lrow, 7).Value = totalcost
    .Cells(lrow, 8).Value = recommend & totalcost
    .Cells(lrow, 9).Value = comment1 & tb5.Value
    .Cells(lrow, 10).Value = comment2 & tb6.Value
    End With

    That is my saved routine.

    Just figured it out I had to format the totalcost

    totalcost = Format(lb4.Caption, "($#,##0.00)")

    by placing that in i was able to get it to work.


    Thanks for your help

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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