Saving result from userform textbox into worksheet column

    I have made userform (Userform2) to calculate overtime on the attached worksheet. By clicking on any row number on the left the userform is generated with the required fields for each employee filled in. I then click on the calculate button to show the amount of payment due. I then have a save button with which I would like to save the result of the calculation into column Q in the relevant row for the employee selected.


    However I can't get the save button to work and cannot figure out where I am going wrong. Any help appreciatedforum.ozgrid.com/index.php?attachment/69760/

    Re: Saving result from userform textbox into worksheet column


    You have a space at the beginning of your "daysworked" sheet name.
    So either remove the space or change your code so it's " daysworked" with the space before the d.

    Bruce :cool:

    Re: Saving result from userform textbox into worksheet column


    Further to this, I want to save the calculated amount as a currency. The result in the sheet is coming up with a comment box saying its formatted as text and I would like it as a currency amount.

    Re: Saving result from userform textbox into worksheet column


    Code
    Private Sub CommandButton2_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(" daysworked")
x = Me.Lblrow
With ws.Cells(x, 20)
  .Value = CDbl(UserForm2.Payment)
  .NumberFormat = "$#,##0.00"
End With
UserForm2.Hide
End Sub

    Re: Saving result from userform textbox into worksheet column


    Try this


    Code
    Option Explicit


Private Sub CommandButton2_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(" daysworked")
    x = Me.Lblrow
    ws.Cells(x, 20).Value = FormatCurrency(UserForm2.Payment)
    UserForm2.Hide
End Sub

    Re: Saving result from userform textbox into worksheet column


    Roy,
    prompted by your last post I tried it and noticed a few things here (UK locale); first is that it produced a string (more on this later), second was that when Excel reported that it was a number formatted as text with its little green triangle, and I used the on-sheet dropdown (little exclamation point next to error) to Convert to Number, it did so but retained the GBP sign and formatted the cell as Currency (with negative values in red too). It was this second operation that I was trying to replicate with a one-liner in vba. I got nowherere.
    I asked a couple of people to send me their files after they had run following the single line on a virgin sheet in their locales (Euro and US).
    Range("A1") = FormatCurrency(-33.33)


    The results were a bit of a surprise:
    In the UK it produces a pound sign string which on convert-to-number with the on-screen dropdown produces a number in a GBpound formatted cell.
    In a Euro locale (Patrick's) it produces a plain number in the cell, no formatting, no currency symbol (what's the blinking point?)
    In the US (?) (Brian's) it produces a full currency-formatted cell with a number (no strings here!) from the off.


    So with that very variable behaviour I doubt I'll ever use FormatCurrency!
    There's a thread at the Excel-L group at http://peach.ease.lsoft.com July 2016 Week 4 called Convert to Number

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login