How do I preserve the leading zeros in a user entered field with excel vba-CodePudding

I am a newbie working on my first excel vba application. One of my user forms has a text box that the user enters data into. The data is likely to be a number that has leading zeros. I am placing the input in a string and trying to format it as text but both things I tried to not work. Any help would be appreciated.

Here are the two things I tried after search on line for how to format text in VBA code

txtString.NumberFormat = "@"

txtString.Value = Format(txtString.Value,"'0")

Thanks for any help.

CodePudding user response：

Assuming your input is a string. Converts string to value you can work with. Calculates how many zeros to precede with in case it is not consistent.

Sub PrecedingZeros()
    Dim strng As String
    Dim lng As Integer
    Dim fmt As String
    Dim i As Integer
    
    With Selection
        strng = .Value
        lng = Len(strng)
        .NumberFormat = "@"
        fmt = "0"
        If lng >= 2 Then
            For i = 2 To lng
                fmt = fmt   "0"
            Next i
        End If
        .NumberFormat = fmt
        .Value = CSng(strng)
    End With
End Sub

CodePudding user response：

Option1

Sub test1()
    Text = InputBox("Enter a number with leading zeros:")
    With ActiveSheet.Range("B2")
        .NumberFormat = "@"
        .Value = Text
    End With
End Sub

Option2

Sub test2()
    Text = InputBox("Enter a number:")
    ftext = Format(Val(Text), "00000000") ' for example, 8 positions
    Debug.Print "Formatted number is: " & ftext
End Sub

Prints:

Formatted number is: 00012564