Custom Number Format from UserForm

Question

I've got a text box in a user form where the user puts unit (for example "kg"). I am trying to use this unit to format number of a cell (to add the unit after the value in a cell)

Sub Custom_unit()

Dim Cust_un As String

Cust_un = IM_Form1.TextBox1.Value
Selection.NumberFormat = "0" & Cust_un

End Sub

This is my attempt but when I try to run it, I get a Run-time error '1004': Unable to set the NumberFormat property of the Range class.

score 0 · Accepted Answer · answered Feb 16 '20 at 19:46

How to add quotes to NumberFormat

Given an input from a textbox, you get a string value, e.g. "kg". In order to use that as a NumberFormat suffix you need to enclose the unit string with quotes.

You could do that by assigning

Selection.NumberFormat = "0" & """" & Cust_un & """"

or alternatively

Selection.NumberFormat = "0" & Chr(34) & Cust_un & Chr(34)

Note that VBA can neither interprete a single quote (") as string, nor a single quote enclosed by a start and end quote ("""). Yyou have to include a pair of quotes within the surrounding quotes instead, i.e. """" to make it understood. The alternative uses the Chr() function returning a string containing the character associated with the specified character code 34, i.e. the double quote character.

You might prefer to use a help function to return the correct NumberFormat, such as

Function getNumberFormat(ByVal unit As String, Optional NumFmt As String = "0 ") As String
    unit = Replace(unit, Chr(34), vbNullString)         ' remove existing double quotes
    getNumberFormat = NumFmt & Chr(34) & unit & Chr(34) ' build NumberFormat including quotes around unit
    ' or: getNumberFormat = NumFmt & """" & unit & """"
End Function

Example call

Assuming your Custom_unit() procedure resides in the UserForm code module use Me.TextBox1.Text to refer to the current UserForm instance instead of referring to the default instance of the userform. An example call could be as follows:

Sub Custom_unit()
    Selection.NumberFormat = getNumberFormat(Me.TextBox1.Text)
End Sub

BTW a hint: in most cases it's better to avoid selection references, c.f. How to avoid using Select in VBA?

Custom Number Format from UserForm

1 Answers1