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

Bruce Dautrich 0
Viewed 48

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.

More detailed question:

My application has 15 user forms and a workbook with 19 sheets in it. The first 5 sheets are excel worksheets that are used as databases. There are 2 worksheets that are inventory databases (account for 2 different types of inventory), there is a worksheet that tracks orders, there is a work sheet that tracks test results for products in inventory, and there is a worksheet to track the label information that must go on order. When the order is generated the user enters a package tag which is likely to be a number with leading zeros. The entry with leading zeros is stored in the orders database correctly. A different user from generates the label information that must go on the product. To do this the application displays orders that need labels and then when the user selects the order they want to generate the label the application searches the order database to get info to put on label and places this in a variable within the module associated with the generate label user form. It gets data in this fashion from each of the other databases to have all of the label information together. It then writes these variables to the database that has the label info in it. When it does this the leading zero get stripped off. I done several searches to find ways to do this and I have tried many of them and cannot seem to get any to work. I was hoping to fix this with the format method because I have to use it with other things I pull from the database like %s. The stripping of the leading zeros occurs when I store the value in the worksheet that has the label info. It does not matter if I set the cell in the label worksheet from a variable or directly from the orders workbook the leading zeros get stripped off.

Thanks!

format user-input text vba excel
2 Answers

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
M.Sqrl0

All

Thanks for your help. I ended up prepending a "'" to the text string every time I set my internal variable and that kept the leading zeros in place. This worked so I dropped the format idea.

Thanks again!

Bruce

Bruce Dautrich0
Related