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 [email protected] 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: [email protected]" 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: [email protected], 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: [email protected], 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, Commented Apr 13, 2020 at 23:45
@teylyn , Please find my update now with screenshot & codes. thanks — Pardhu
– Pardhu, Commented Apr 14, 2020 at 8:05

Variatus · Accepted Answer · 2020-04-14 09:16:53Z

1

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", "[email protected]", "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

answered Apr 14, 2020 at 9:16

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pardhu Over a year ago

thanks for your reply.. your logic for CustDetail is working for textboxs into script textbox but how to call MyScript to pull the scripts directly from Sheet2 Since I have 200 different scripts on Sheet2 column which will change once I select from combobox.. Since I already wrote dependent Combobox to textbox in "Script Window" Frame plz check above this code ComboBox1_Change() & ComboBox1_DropButtonClick() can you please give me code which will call directly from Sheet2 column. thanks

Pardhu Over a year ago

for exa: in sheet2 if I add ` "|having new||. " & vbCr & _ "His mobile number is||. " & vbCr & _` like this to all those different script splitting "|" manually by me for one time, then will it work when it display on useform textbox script or can we make in such a way so i can able to use those all scripts..? by the way I rectified Exit For thanks..

Variatus Over a year ago

It's exactly like you say. Therefore you found the problem but then you stopped thinking. All your 200 plus scripts must have 6 insertions each, not mor, not less, as prescribed by the number of textboxes in your form plus time. Therefore they can all be written on the pattern of the Const MyScript I introduced above. Therefore they can all be assembled with the contents of the TextBoxes using the same code which, in essence, I have already supplied. All you need to do is to re-write your 200 scripts. It's the lesser evil.

Collectives™ on Stack Overflow

Excel VBA userform textbox having dynamic scripts using different textboxs

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related