Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Delete spaces after "000" when incrementing in cell by form

0

Hi

I have this code when run the form will increment in textbox1 and populate in E2 every time but will show space after "000" , I don't want it .

for instance when ru the form I would like this AA 00032343, not this AA 000 32343

Private Sub UserForm_Initialize()
Dim ss As String
Dim r As Integer


ss = "AA 000"

If Range("E2") = "" Then 'Check if this is the first user of the form
    r = 32338
  Else
  r = Right(Range("e2").Value, 5) + 1 ' not, add 1 to last value
End If
Me.TextBox1.Text = ss & Str(r)
Range("E2").Value = Me.TextBox1.Text 'write this value to sheet(and later write other default values)
End Sub
Answer
Discuss

Answers

0
Selected Answer

Leap

In your line:

Me.TextBox1.Text = ss & Str(r)
the function Str keeps the space ( see the Microsoft guidance on Str function) so just use CStr instead to convert the number r to a string, hence:
Me.TextBox1.Text = ss & CStr(r)
 
Discuss

Discussion


thanks very much, John.
leap (rep: 46) Jan 26, '24 at 12:59 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login