CODEWITHSUNDEEP
excelobjectcomboboxuserform
Yodelayheewho
Yodelayheewho

Reputation: 59

VBA concatenate multiple textboxes into one based on combobox selection

I have a userform and want to move repeating code into standard modules and call the code when needed. The following code I pieced together. I have a combobox with the following values: System, System IC, Service, or Repair. Based on the value I select in the combobox, I need to concatenate the values from 7 textboxes, separated by a comma and a space, into one textbox.

Option Explicit
Sub EmailSubjectLine()
    Dim i As Long
    Dim b As Variant
    Dim c As Variant
    Dim ws As Worksheet
    
    Set ws = Sheets("Master")
    
    Dim strCon As String 'concatenate in txtEmailSubLine
     b = Array(txtSO, txtPO, txtPOAmt, txtProposal, txtNickname, txtEUNickname, txtSys)
     c = Array(txtSO, txtPO, txtPOAmt, txtProposal, txtNickname, txtEUNickname, cboOrderType)
     
    strCon = txtShopOrdNum 'textbox that contains the concatenated textboxes b or c
   
    Select Case cboOrderType.Value 'Run-time error 424. Object required.
        Case "System" Or "System IC"
                For i = 1 To 7
                    If b(i) <> vbNullString Then
                        strCon = strCon & ", " & b(i)
                End If
                Next
                txtEmailSubLine.Value = strCon
        Case "Service" Or "Repair"
                For i = 1 To 7
                    If c(i) <> vbNullString Then
                        strCon = strCon & ", " & c(i)
                End If
                Next
                txtEmailSubLine.Value = strCon
    End Select
End Sub

Upon getting the error, I added declaring the worksheet, but that didn't help.

EDITED ORIGINAL CODE, PER @ MICHAL However, the concatenated textboxes would not save to the worksheet.

Sub EmailSL()
    Dim i As Long
    Dim b As Variant
    Dim c As Variant
    Dim strCon As String
    
    b = Array(frmMaster.txtSO, frmMaster.txtPO, frmMaster.txtPOAmt, frmMaster.txtProposal, frmMaster.txtNickname, frmMaster.txtEUNickname, frmMaster.txtSys)
    c = Array(frmMaster.txtSO, frmMaster.txtPO, frmMaster.txtPOAmt, frmMaster.txtProposal, frmMaster.txtNickname, frmMaster.txtEUNickname, frmMaster.cboOrderType)
     
    strCon = frmMaster.txtShopOrdNum 'textbox that contains the concatenated textboxes b or c
   
    Select Case frmMaster.cboOrderType.Value
        Case "System", "System IC"
                For i = 1 To 7
                    If b(i) <> vbNullString Then
                        strCon = strCon & ", " & b(i)
                End If
                Next
                frmMaster.txtEmailSubLine.Value = strCon
        Case "Service", "Repair"
                For i = 1 To 7
                    If c(i) <> vbNullString Then
                        strCon = strCon & ", " & c(i)
                End If
                Next
                frmMaster.txtEmailSubLine.Value = strCon
    End Select

End Sub

So, I did some research and added the following code to the worksheet. However, that did not work to save the concatenated textboxes to the worksheet. "EMAILSUBLINE" is a named range on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("EMAILSUBLINE")) Is Nothing Then 
    Application.EnableEvents = False
        Call EmailSL
    Application.EnableEvents = True
    End If
End Sub

EDITED CODE WITH CORRECT INDEXES, PER @ MICHAL AND FOUND ANOTHER ERROR THAT I FIXED. However, I still can't get the concatenated textboxes to save to the worksheet.

Sub EmailSL()
    Dim i As Long
    Dim b As Variant
    Dim c As Variant
    Dim strCon As String
    
    b = Array(frmMaster.txtSO, frmMaster.txtPO, frmMaster.txtPOAmt, frmMaster.txtProposal, frmMaster.txtNickname, frmMaster.txtEUNickname, frmMaster.txtSys)
    c = Array(frmMaster.txtSO, frmMaster.txtPO, frmMaster.txtPOAmt, frmMaster.txtProposal, frmMaster.txtNickname, frmMaster.txtEUNickname, frmMaster.cboOrderType)
     
    strCon = frmMaster.txtShopOrdNum.Value 'This is the first value of the concatenated textboxes.
   
    Select Case frmMaster.cboOrderType.Value
        Case "System", "System IC"
                For i = 0 to 6
                    If b(i) <> vbNullString Then
                        strCon = strCon & ", " & b(i)
                End If
                Next
                frmMaster.txtEmailSubLine.Value = strCon
        Case "Service", "Repair"
                For i = 0 to 6
                    If c(i) <> vbNullString Then
                        strCon = strCon & ", " & c(i)
                End If
                Next
                frmMaster.txtEmailSubLine.Value = strCon
    End Select

End Sub

So, I did some research and added the following code to the worksheet. However, that did not work to save the concatenated textboxes to the worksheet. "EMAILSUBLINE" is a named range on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("EMAILSUBLINE")) Is Nothing Then 
    Application.EnableEvents = False
        Call EmailSL
    Application.EnableEvents = True
    End If
End Sub

Upvotes: 1

Views: 35

Answers (1)

Michal
Michal

Reputation: 6064

Make sure to reference the textboxes and combobox correctly with UserForm1 and array indexing should start from 0 not from 1:

Sub EmailSubjectLine()
    Dim i As Long
    Dim b As Variant
    Dim c As Variant
    Dim strCon As String
    
    ' Assuming these textboxes are on a userform named UserForm1
    b = Array(UserForm1.txtSO, UserForm1.txtPO, UserForm1.txtPOAmt...)
    c = Array(UserForm1.txtSO, UserForm1.txtPO, UserForm1.txtPOAmt,... UserForm1.cboOrderType)
    
    strCon = UserForm1.txtShopOrdNum.Value
    
    Select Case UserForm1.cboOrderType.Value
        Case "System", "System IC"
            For i = 0 To 6
                If b(i).Value <> vbNullString Then
                    strCon = strCon & ", " & b(i).Value
                End If
            Next
            UserForm1.txtEmailSubLine.Value = strCon
        Case "Service", "Repair"
            For i = 0 To 6
                If c(i).Value <> vbNullString Then
                    strCon = strCon & ", " & c(i).Value
                End If
            Next
            UserForm1.txtEmailSubLine.Value = strCon
    End Select
End Sub

Upvotes: 0

Related Questions