Excel VBA userform textbox having dynamic scripts using different textboxs

Question

I'm new to Excel VBA Userform.. I want to achieve below 2 issues:

1) I have three textbox name customer name, mobile number & email ID --> I'm getting this data from Sheet1 which is like a database which has all those three column data stored and will change time to time. Next I have dependent combobox to another textbox --> This two act as a parent and child were combobox is parent and textbox is child any item selected in combobox that item script will display in textbox.

For exam: Combobox if I select item "car" then it's script will display in child textbox as "name having new car, his mobile number is: & email id is: " in the same way I have such scripts upto 200.

I want to fill those first three textboxs data which is displaying on this userform directly into this script like below example

If customer name is John, mobile number 132132132 and email id test@test.com When user select car from parent combobox then this textbox will fill data like "John having new car, his mobile number is: 132132132 & email id is: test@test.com" so that I can able to save data in Sheet2

And out of those 200 scripts customer details will change their position in script. means customer name in few scripts will appear at end of the script.

2) I have two more textbox in same form with update button only for them one textbox is to enter customer language spoke & another textbox for date and time (either current or specific date & time). Once I enter customer language spoke english & current date & time then i select update button this two textbox values need to save on few scripts which I explained above.

Examples:
"John having new car, his mobile number is: 132132132 & email id is: test@test.com, Language spoke was English, time of visit current date & time" "I meet Kiran on date & time, Then he asked for my contact number & email id. Language spoke was Germany"

on above two example I must use all textbox values in some script I need to use only customer name.

I'm calling those customer data using search button which will pull data from sheet1. Everything is working like pulling & saving data on Sheets but I couldn't able to find option which match with above two scenarios.

please if you can share exact code of this will be helpful since I tried with concatenate directly on sheet with trim option something which went completely wrong so i got struct with this on how to approach.

Many thanks in advance

Edit: Sure please find details below and thanks for your update

Below is a view of the userform. So, Customer Details Frame I used for sheet1 were my database is stored and will display on form when we click search button For search button code is

`Private Sub CommandButton3_Click()
    Dim Cust_Name As String
    Cust_Name = Trim(TextBox1.Text)
    LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To LastRow
    If Worksheets("Sheet1").Cells(i, 1).Value = Cust_Name Then
    TextBox1.Text = Worksheets("Sheet1").Cells(i, 2).Value
    TextBox2.Text = Worksheets("Sheet1").Cells(i, 3).Value
    TextBox3.Text = Worksheets("Sheet1").Cells(i, 4).Value
    End If
    Next
End Sub`

For Extra Details Frame I have below code for two text box where we need to enter manually for language spoke will enter English or any other language and date & time

So, for display time I used code Me.TextBox10.Value = Format(Now, "dd/mm/yyyy hh:mm:ss")

And for Script Windows frame for dependent combobox & script textbox below code used

`Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
    If Sheets("Sheet2").Cells(i, "F").Value = (Me.ComboBox1) Then
        Me.TextBox5 = Sheets("Sheet2").Cells(i, "G").Value
    End If
Next
End Sub`

`Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
    For i = 2 To LastRow
        Me.ComboBox1.AddItem Sheets("Sheet2").Cells(i, "F").Value
    Next i
End If
End Sub`

So, from "Customer Details" Frame I want those all details should fit inside the the script automate when I select the script from dropdown and also from "Extra Details" Frame once I enter details manually and click on update button those details also need to update in that Script textbox.

As per the image which I shared and above details below is what I need to get in final output. John having new car, his mobile number is: 123456789 & email id is: test@test.com, Language spoke was English, time of visit current 14-04-2020 12:46:33

This is what I need in my final modified script which I can able to save in sheet with below code.

`Private Sub CommandButton1_Click()     
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet4")
Dim n As Long
n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
sh.Unprotect "test"
sh.Range("A" & n + 1).Value = Me.TextBox1.Value
sh.Range("B" & n + 1).Value = Me.TextBox2.Value
sh.Range("C" & n + 1).Value = Me.TextBox3.Value
sh.Range("E" & n + 1).Value = Me.TextBox5.Value
sh.Protect "test"
End Sub`

Anything else needed let me know thanks.

Please try to break down your wall of text into a description of the problem you have. Post your code, post what you would like to happen, post what happens instead. Nobody will give you the EXACT code you need to run your scenario. But if you post your code and explain what does not work, then we can help you with that. — teylyn
@teylyn , Please find my update now with screenshot & codes. thanks — Pardhu

Variatus Variatus · Accepted Answer · 2020-04-14T09:16:53

The code below shows how you might approach a solution of your problem. Please study it.

Option Explicit


Private Sub Demo_Of_Method()

    Const MyScript = "|having new||. " & vbCr & _
                     "His mobile number is||. " & vbCr & _
                     "Email is||. " & vbCr & _
                     "Language spoken was||. " & vbCr & _
                     "Time of conversation:||"
    Dim Script() As String
    Dim CustDetail As Variant
    Dim i As Integer, n As Integer

    CustDetail = Array("John", "car", "123123123", "John@test.com", "German", Now())
    Script = Split(MyScript, "|")
    For i = 0 To UBound(CustDetail)
        Script(n) = CustDetail(i)
        n = n + 2           ' every other element of 'Script' is blank.
    Next i

    MsgBox Join(Script)     ' the default separator is a single space
End Sub

The Script array is constructed to have alternating filled and blank elements. In your project it probably is prepared ready in your database and brought in from there. It's processed by splitting on "|", a character that doesn't occur in the text. Therefore, where two || following each other a blank element is created, as does the leading "|".

In your project you would construct the CustDetail array from your text box values. Then join the two by a loop using the algorhythm on which they were built. Perhaps the above code can be converted to a function which returns the joined array ready for insertion in your form.

BTW, looking at your code, the Exit For is missing. You need it to stop the code from continuing to execute even after the customer name was found.

For i = 2 To LastRow
    If Worksheets("Sheet1").Cells(i, 1).Value = Cust_Name Then
        TextBox1.Text = Worksheets("Sheet1").Cells(i, 2).Value
        TextBox2.Text = Worksheets("Sheet1").Cells(i, 3).Value
        TextBox3.Text = Worksheets("Sheet1").Cells(i, 4).Value
        Exit For
    End If
Next

Excel VBA userform textbox having dynamic scripts using different textboxs

1 Answers