Userfrom, field formats

[phendrena]

Hi,

Is it possible to format a field in a userform, for example to currency?
If so, how?

Thanks,

[CreganTur]

As you've probably noticed there is no property for a textbox to change the formatting. That said, you can do your formatting via VBA. You can use the FormatCurrency function (see Help) to format your variable's value so it displays correctly in the UserForm textbox.

[Bob Phillips]

Or just Format which gives you access to any number of formats,

[phendrena]

As you've probably noticed there is no property for a textbox to change the formatting. That said, you can do your formatting via VBA. You can use the FormatCurrency function (see Help) to format your variable's value so it displays correctly in the UserForm textbox.

Hi Randy,

To be honest, i'm not too bothered about it looking right in the textbox, although it would be nice. I'm more concerned about it correctly adding the data into Access....

I've tried this :-

'Recordset("Original_Premium") = txtOPrem.Value = Format("Currency")'

The values show on the Access table as ?0.00 (Access table field is defined as currency).

Help!

Thanks,

[Bob Phillips]

If you format it correctly in the textbox, and use txtOPrem.Text, as a string it will be correct as passed to Access.

[phendrena]

If you format it correctly in the textbox, and use txtOPrem.Text, as a string it will be correct as passed to Access.

Sorry, i'm having a brain blank moment, can you elaborate a little more please.

Cheers,

[CreganTur]

I'd use the FormatCurrency function and explicitly declare the number of decimal places:

[vba]Recordset("Original_Premium") = txtOPrem.Value = FormatCurrency("Currency",2)'[/vba]

[phendrena]

I'd use the FormatCurrency function and explicitly declare the number of decimal places:

[vba]Recordset("Original_Premium") = txtOPrem.Value = FormatCurrency("Currency",2)'[/vba]

Hmmm.... I'm getting a compile error with that one, Sub or Function not defined.

Excel '97 problem?

[Bob Phillips]

Load the textbox and format

[vba]

txtOPrem.Text = Format(123,"?#,##0.00")
[/vba]

then load the Recordest from that

[vba]

Recordset("Original_Premium") = txtOPrem.Text
[/vba]

[phendrena]

Load the textbox and format

[vba]

txtOPrem.Text = Format(123,"?#,##0.00")
[/vba]

OK, I've added this into the initalise routine, i now shows ?123.00 in txtOPrem. Now, if i enter into this field and overwrite the entered data it looses the format.... am i missing something?

Thanks,

[CreganTur]

Now, if i enter into this field and overwrite the entered data it looses the format.... am i missing something?

The formatting belongs to the variable's value, not the textbox. The textbox has no formatting of its own. You can add in an AfterUpdate event for the textbox that will reformat data entered into it to FormatCurrency.

[phendrena]

I'm still a little confused about the afterupdate event....

[VBA]Private Sub txtIPrem_AfterUpdate()
txtIPrem.Text = Format(123, "?#,##0.00")
End Sub[/VBA]
OR
[VBA]Private Sub txtIPrem_AfterUpdate()
txtIPrem.Text = Format("?#,##0.00")
End Sub
[/VBA]

Both don't give the result i'm looking for.
In the first instance it enters ?#,##0.00 into the field
In the second I get ?123.00.

Any suggestions? (FormatCurrency doesn't appear to work on Excel '97)

Thanks,

[Bob Phillips]

[vba]

Private Sub txtIPrem_AfterUpdate()
txtIPrem.Text = Format(txtIPrem.Text, "?#,##0.00")
End Sub
[/vba]

[phendrena]

[vba]

Private Sub txtIPrem_AfterUpdate()
txtIPrem.Text = Format(txtIPrem.Text, "?#,##0.00")
End Sub
[/vba]

That works nicely thanks xld.

Once other quick question...

Formatting percentages.
If I use "##%" i get 1000% when the user enters 10 in the field.
How can i get it to show correctly as 10%?

Thanks,

[phendrena]

Hi Again,

The currency fields now format correctly, however when i try and send the data into Access i'm still getting a 'Type Mismatch Error'.

The fields in Access are set as a Data Type of 'Currency'.
If I change them back to 'Text' I don't get an error.

Any suggestions?

Thanks,

[Bob Phillips]

Just use 0%

[Bob Phillips]

Hi Again,

The currency fields now format correctly, however when i try and send the data into Access i'm still getting a 'Type Mismatch Error'.

The fields in Access are set as a Data Type of 'Currency'.
If I change them back to 'Text' I don't get an error.

Any suggestions?

Thanks,

You are probably trying to load a string value, complete with ? and , symbols into a numeric field.

You could strip the garbage before loading it, like so

[vba]
Function ValidAmount(ByVal amt As String) As Double
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "[?,]"
ValidAmount = RegEx.Replace(amt, "")
Set RegEx = Nothing
End Function
[/vba]