Remove leading zeros from textbox VBA

3
votes

User inputs only numerical values in textbox name tbID, in my file, that column value is saved as numerical. However, if user input 00120 instead of 120. After attempting to remove the leading zeros, there seems to be a space before 120.

When I input Criteria1:="120" it works

tbIDf = Cint(tbID) 'Debug.Print tbIDf gives " 120" and renders my filter criteria to a blank
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=IDN, Criteria1:="tbID"

How do I remove both the leading zeros and the " " in front of 120?

2
excelvba
Use Trim() to remove the space: LTrim, RTrim, and Trim functions - Pᴇʜ
I've tried LTrim(tbID) and the leading space is still there ): - Valwrie
what type of variable is tbID? - Damian
@Damian it was an input in the textbox that i forced to be IsNumeric - Valwrie

2 Answers

4
votes

Here is a thought, I would make sure data that makes it to your sheet is actually correct usable data. To do so you could use:

Trim(Val(tbID))

The use of Val would return a double value ignoring non-numeric values, 120. Alternatively CLng is also a possibility returning a long value. Just don't use CInt as there is no need to use integers (range only from -32,768 to 32,767)

The TRIM function will remove leading and trailing spaces.

You can/should also implement a keypress event described here to make sure only numerical values are entered.

enter image description here

1
votes

This should work:

Option Explicit

Sub test()

    'just for testing (this comes from your textbox)
    Dim tbID As String
    tbID = "00120"

    If IsNumeric(tbID) Then 'test if numeric to prevent errors
        Dim MyCriteria As String
        MyCriteria = CStr(CLng(tbID))

        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=IDN, Criteria1:=MyCriteria 
    End If
End Sub

If not use Trim(CStr(CLng(tbID)))