Search code examples
excelvbauserformnumber-formatting

Custom Number Format from UserForm

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.

Solution

  • 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?