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

using array or loop to make the code is short

0

hi  experts  

need  from somebody  provide  me  hand  of  help 

the  code  works  so  well  when  write  th  digits or  letters  in textbox2,3,4 .  it  populate   in textbox1  ,  but  I  added  multiple  textboxes  , then  I  have  to  rename  the  function and  change  the  number  in textbox  which populate  the  value  in CODE . as  you  see  when populate  in textbox5,9   I  have  to  rename  function (toFill,toFill1,toFill2) to  implement for  textbox5,9  . then userform  becomes  contains  many  codes  . so  I  search  way  to  make  short  the  code  by  using array  or  loop .

actually  I  try like  this   

Sub toFill(a As String, b As String, c As String)
Dim n As String, arr As Variant
rr = Array(TextBox1, TextBox5, TextBox9)
    a1 = Split(a)(0)

For i = 1 To Len(a)
    If Mid(a, i, 1) Like "#" Then n = n & Mid(a, i, 1)
Next

    c = Left(c, 1)
    rr = a1 & c & b & n

End Sub
 

but  this  does not succeed

thanks

excel
MS Office Version: 2016
posted by: MAKLIL (rep: 34) on Sun Oct 3, 2021 at 5:43 am
Answer
Discuss

Answers

0
Selected Answer

Maklil

Not sure what you're trying to do here but I have assumed your original sub toFill in your file populates TextBox 1 correctly (seems like full Brand input & Type & Origin & numeric part of Brand).

If so, instead of repeating the sub, I have deleted toFill1 and toFill2 and made it into a single function (with changes in bold below):

Function toFill(a As String, b As String, c As String) As String
Dim n As String
    a1 = Split(a)(0)

For i = 1 To Len(a)
    If Mid(a, i, 1) Like "#" Then n = n & Mid(a, i, 1)
Next

    c = Left(c, 1)
    toFill = a1 & c & b & n

End Function
Where the bold "as String" at the end of the first line means it will return a string when it is called.

To use it in your TextBox_Change codes, you use (for example):

Private Sub TextBox2_Change()

    If TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Then
        TextBox1.Value = ""
    Else
         TextBox1 = toFill(TextBox2.Value, TextBox3.Value, TextBox4.Value)

    End If

End Sub
and that populates TextBox1.

Your other subs already pass the right parameters to the Function and you just change the lines which populate the left-hand TextBox like this:

TextBox5 = toFill(TextBox6.Value, TextBox7.Value, TextBox8.Value)
and 
TextBox9 = toFill(TextBox10.Value, TextBox11.Value, TextBox12.Value)
See the revised file attached.

Hope this helps.

posted by: John_Ru (rep: 6142) on Sun Oct 3, 2021 at 9:13 am
Discuss

Discussion

actually you completely understand me . this is better from previous
thanks so much for your solution .  have a good weekend !
MAKLIL (rep: 34) Oct 3, '21 at 10:53 am
Great. Thanks for selecting my Answer Maklil. Have a good weekend too
John_Ru (rep: 6142) Oct 3, '21 at 10:54 am
Add to Discussion


Answer the Question

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