Textbox Format as Currency

[mar050703]

Hi All,

Just a quick question, I have a userform with varying textboxes, some of which I need to cause the entry to format as Currency on the spreadsheet.

I am using the flowing code:

 ActiveCell.Offset(0, 22).Value = Format(TxtComAmt.Value, "£#,##0.00")

Which is fine, except that it formats it to the left of the column (as if it were text). Therefore the SUM function does not work.

Nay help would be appreciated.

Thanks

 

[SkipVought]

hi,

The Format function returns a STRING.

You want the NumberFormat property of the range object.

with ActiveCell.Offset(0, 22)
   .Value = TxtComAmt.Value
   .NumberFormat = "£#,##0.00"
end with

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Thanks

I actually thought I had solved it with a DIM option

Dim ComAmt As Integer 'Commission Value
ComAmt = Format(TxtComAmt, "£0.00")
ActiveCell.Offset(0, 22).Value = TxtComAmt.Value

The problem is I have other textboxes with similar formatting, but errors if there is nothing entered in the textbox.

I will try your solution.

Thanks

 

[SkipVought]

Therefore the SUM function does not work.

WHY do you persist in a method that returns a STRING, rather than a NUMBER that can be SUMMED, according to your original lament?

What IS your problem???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Skip

I have used your coding for all currency text boxes, and all is fine.

I have transposed your coding for date text boxes, therefore the code is:

 If ChkCom.Value = True Then
    With ActiveCell.Offset(0, 22)
    .Value = TxtComAmt.Value
    .NumberFormat = "£#,##0.00"
End With
    With ActiveCell.Offset(0, 23)
    .Value = TxtComDate.Value
    .NumberFormat = "dd-mmm-yy"
End With

However the date in the text box could be 11/12/13, but in the cell it is entered as 12-Nov-13. It appears to switch the day and month round.

Do you have any ideas?

Thanks

 

[SkipVought]

However the date in the text box could be 11/12/13

This date string is AMBIGUOUS! Bill Gates' company is in the USA (default MDY), so the default CONVERSION is MDY.

It is a poor design approch to use a TEXTBOX to enter a date!!!

If you use any professional web site that asks for a date, you'll find either a date picker or separate fields for day, month, year.

faq68-5827

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}