Display full text of selected dynamic combobox in textbox

[keaden]

Hello again folks,
I have a quick question.

I currently have a multipage userform that populates dynamic comboboxes within a frame on a button click.

I was thinking about having a textbox on the same page that will display the full text of the active combobox in the textbox when it is clicked on.

Is this sort of thing possible and if so could anyone point me in the right direction on how to impliment this?

I had thought about creating a class and using WithEvent with a collection of comboboxes but is that possible or am I over complicating things?

 

[My Aswer Is This]

Your wanting all the values in a Combobox to be placed into a textbox. Is that what you want?
I do not believe that is possible. A Combobox may have a list of 20 values and to put all that in one textbox would be hard unless some how you inserted a new line after every value.

Now you could put all the values in a Combobox into a Listbox
Using a script like this:

ListBox1.List = ComboBox1.List

 

[keaden]

Hello My Aswer Is This,

The comboboxes display text (Sorry I should have made that clear) So what I'm looking for is a display of the full text of the selected combobox to be shown in the textbox.

Unfortunately since the comboboxes are added at runtime the names are assigned to them dynamically.

Thanks,
Keaden

 

[My Aswer Is This]

Yes I understood the combobox would have text.
So if the combobox had a list like this:
Dog
Cat
Mouse

How would you want to see it in a text box?

Like this:
DogCatMouse

Or Like this
Dog
Cat
Mouse

And I'm not sure how you do it either way.

 

[keaden]

Ideally the active combobox would just display the selected item from the list. So if you had 3 separate comboboxes working from the list and you had the first combobox with the word dog, the second combobox with cat the third with mouse when you clicked on the first combobox to make it active the text box would display dog, when you clicked on the second combobox to make it active the text box would display cat and mouse would be displayed when the third combobox was clicked on.

Thanks anyway for the input, much obliged.
K

 

[mikerickson]

Why are you adding combo boxes at run-time?
How are you getting event code to drive those combo boxes?

One way to do this would be for you to add a class module and put this code in it.

' in Class1 class module

Public WithEvents AddedComboBox As MSForms.ComboBox

Private Sub AddedComboBox_Change()
    UFParent.TextBox1.Text = AddedComboBox.Text
End Sub

Private Sub AddedComboBox_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    UFParent.TextBox1.Text = AddedComboBox.Text
End Sub

Private Sub AddedComboBox_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    UFParent.TextBox1.Text = AddedComboBox.Text
End Sub

Private Function UFParent() As Object
    Set UFParent = AddedComboBox
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Function

And then put this code in the user form's code module. If you have more that one MultiPage control, you need to duplicate that code for each MP's AddControl event.

' in userform code module

Dim AddedComboBoxes As Collection

Private Sub MultiPage1_AddControl(ByVal Index As Long, ByVal Control As MSForms.Control)
    Dim NewAddedBoxObject As Class1
    If TypeName(Control) = "ComboBox" Then
        Set NewAddedBoxObject = New Class1
        Set NewAddedBoxObject.AddedComboBox = Control
        AddedComboBoxes.Add NewAddedBoxObject
    End If
    Set NewAddedBoxObject = Nothing
End Sub

Private Sub UserForm_Initialize()
    Set AddedComboBoxes = New Collection
End Sub

 

[keaden]

Hello Mike,
I have a userform that generates a letter.

The letter consists of a template and a varying number of articles (anywhere from 1 or 2 to 32).

I have the userform set up to generate a combobox and 2 textboxes in a frame on the multipage userform.

The user clicks a button to add a row and then uses the dynamically created combobox to select the text article to be inserted into the word document.

Private Sub CommandButton10_Click()
    iAddedCount = iAddedCount + 1
    Dim theCombo As Control
    Dim theTextBox800 As Control
    Dim theTextBox801 As Control
    Dim objLabel1 As Control
    Dim objLabel2 As Control
    Dim objLabel3 As Control
           
        Set theCombo = OSRData.MultiPage1.Pages(7).Frame17.Controls.Add("Forms.Combobox.1", True)
        Set theTextBox800 = OSRData.MultiPage1.Pages(7).Frame17.Controls.Add("Forms.Textbox.1", True)
        Set theTextBox801 = OSRData.MultiPage1.Pages(7).Frame17.Controls.Add("Forms.Textbox.1", True)
        Set objLabel1 = OSRData.MultiPage1.Pages(7).Frame17.Controls.Add("Forms.Label.1", True)
        Set objLabel2 = OSRData.MultiPage1.Pages(7).Frame17.Controls.Add("Forms.Label.1", True)
        Set objLabel3 = OSRData.MultiPage1.Pages(7).Frame17.Controls.Add("Forms.Label.1", True)
            
            With theCombo
                    .Name = "Combo" & iAddedCount ' generates the name of the combobox
                    .Font.Size = 8
                    .Height = 16
                    .Left = 10
                    .Width = 300
                    .Top = 27.5 * iAddedCount
                    .SpecialEffect = 3
                    .BorderStyle = 1
                    .RowSource = "Article"
                    .ListIndex = 0
            End With
            
            With theTextBox800
                    .Name = "Text1" & iAddedCount
                    .Font.Size = 8
                    .Height = 16
                    .Left = 330
                    .Width = 180
                    .Top = theCombo.Top
                    .SpecialEffect = 3
                    .BorderStyle = 1
                    .MultiLine = True
                    .EnterKeyBehavior = True
                    .ScrollBars = fmScrollBarsVertical
                    .WordWrap = True
            End With
            
            With theTextBox801
                    .Name = "Text2" & iAddedCount
                    .Font.Size = 8
                    .Height = 16
                    .Left = 520
                    .Width = 180
                    .Top = theCombo.Top
                    .SpecialEffect = 3
                    .BorderStyle = 1
                    .MultiLine = True
                    .EnterKeyBehavior = True
                    .ScrollBars = fmScrollBarsVertical
                    .WordWrap = True
            End With
            
            With objLabel1
                    .Name = "Label100" & iAddedCount
                    .Font.Size = 8
                    .BackColor = Me.BackColor
                    .Caption = "Article"
                    .Height = 10
                    .Left = 10
                    .Width = 60
                    .Top = theCombo.Top - 11
            End With
            
            With objLabel2
                    .Name = "Label200" & iAddedCount
                    .Font.Size = 8
                    .BackColor = Me.BackColor
                    .Caption = "Observation(s)"
                    .Height = 10
                    .Left = 330
                    .Width = 70
                    .Top = theCombo.Top - 11
            End With
            
            With objLabel3
                    .Name = "Label300" & iAddedCount
                    .Font.Size = 8
                    .BackColor = Me.BackColor
                    .Caption = "Action(s)"
                    .Height = 10
                    .Left = 520
                    .Width = 50
                    .Top = theCombo.Top - 11
            End With

End Sub

The user can do this as many times as is needed.

They then click a button to open the template word document, loop through all of the selected articles in each combobox and the comments in each textbox and insert them sequentially into the template word document to generate the letter.

Private Sub CommandButton4_Click()
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim i As Integer
    Dim theCombo As Control
    Dim theTextBox800 As Control
    Dim theTextBox801 As Control
    Dim x As Integer
    Dim y As Integer
    Dim Txt1 As String
    Dim Txt2 As String
    Dim Txt3 As String
    Dim Txt1Rplce As String
    Dim Txt2Rplce As String
    Dim Txt3Rplce As String
    

    x = iAddedCount
    y = 1

        
    Set objWord = New Word.Application
    objWord.DisplayAlerts = False
    objWord.Visible = True
    objWord.Documents.Open ActiveWorkbook.Path & "\OSR Advisory  Letter3.dotm", ReadOnly:=True 
    objWord.Activate
    objWord.Selection.Start = 1445
    
                With objWord.ActiveDocument
                        
                     .FormFields("nameTitle1").Result = titleCombo1
                     
                     .FormFields("firstName1").Result = firstNameText
                    
                     .FormFields("lastName1").Result = lastNameText
                     
                     .FormFields("companyName1").Result = siteName
                     
                     .FormFields("addressName1").Result = addressLine1
                                         
                     .FormFields("postCode1").Result = postCode
                     
                     .FormFields("PPref1").Result = ppReference
                     
                     .FormFields("nameTitle2").Result = titleCombo1
                     
                     .FormFields("lastName2").Result = lastNameText
                     
                     .FormFields("inspDate1").Result = DTPicker1

                End With
                
        For Each theCombo In OSRData.MultiPage1.Pages(7).Controls

                If x > 0 Then
                    
                    Txt1 = Me("Combo" & y).Text
        
                    Txt2 = Me("Text1" & y).Text

                    Txt3 = Me("Text2" & y).Text

                    Txt1Rplce = Replace(Txt1, vbLf, Chr(11))

                    Txt2Rplce = Replace(Txt2, vbLf, "")

                    Txt3Rplce = Replace(Txt3, vbLf, "")
                    
                    
                    
                    
                 objWord.Selection = Me("Combo" & y).Value 'Copies and pastes text  from combobox to selection in word document
                    With objWord.Selection
                        .ParagraphFormat.Alignment = wdAlignParagraphJustify
                        .Font.Name = "Arial"
                        .Font.Size = 11
                        .ParagraphFormat.LeftIndent = 30 'working
                        .Range.ListFormat.ApplyNumberDefault
                        .MoveRight
                        .TypeParagraph
                        .ParagraphFormat.Alignment = wdAlignParagraphLeft
                        .Range.ListFormat.ApplyNumberDefault
                        .TypeParagraph
                    End With
                
                 objWord.Selection = "Observations:" & vbCr & Txt2Rplce 'Copies  and pastes text from first textbox to selection in word document
                    With objWord.Selection
                        .Font.Name = "Arial"
                        .Font.Size = 11
                        .Font.Bold = True
                        .ParagraphFormat.LeftIndent = 30 'working
                        .MoveRight
                        .TypeParagraph
                        .TypeParagraph
                    End With
                    
                 objWord.Selection = "Action(s):" & vbCr & Txt3Rplce 'Copies and  pastes text from second textbox to selection in word document
                    With objWord.Selection
                        .Font.Name = "Arial"
                        .Font.Size = 11
                        .Font.Bold = True
                        .ParagraphFormat.LeftIndent = 30 'working
                        .MoveRight
                        .TypeParagraph
                    End With
                                        
                End If
                
                    y = y + 1
                    x = x - 1
                    
       Next theCombo

End Sub

So since the text is too large for the combobox I wanted to add a textbox to show the entire article text of the active combobox so that users could make sure the article they were selecting was the right one (The articles are pieces of legislation that are worded similarly hence the need to check the correct one is selected)

The Userform will function without it I was just looking to see if I could build on what I have already put together to make it more user friendly.

Thanks again for everyone who's read through this and contributed, Mike I shall give your collections suggestion a go when I get the chance, most likely at the weekend.
K

 

[mikerickson]

Since the Textbox is also created at run-time, what I posted won't work, but a minor change will work.

Looking at your code, add this line

           With theCombo
                   [COLOR="#FF0000"] .Tag = theTextBox800.Name[/COLOR]
                    .Name = "Combo" & iAddedCount ' generates the name of the combobox
                    .Font.Size = 8
                    .Height = 16
                    .Left = 10
                    .Width = 300
                    .Top = 27.5 * iAddedCount
                    .SpecialEffect = 3
                    .BorderStyle = 1
                    .RowSource = "Article"
                    .ListIndex = 0
            End With

Then in the Class module code I posted, change these lines.

Private Sub AddedComboBox_Change()
    [COLOR="#FF0000"]If AddedComboBox.Tag = vbNullString Then Exit Sub
    UFParent.Controls(AddedComboBox.Tag).Text = AddedComboBox.Text[/COLOR]
End Sub

Private Sub AddedComboBox_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    [COLOR="#FF0000"]If AddedComboBox.Tag = vbNullString Then Exit Sub
    UFParent.Controls(AddedComboBox.Tag).Text = AddedComboBox.Text[/COLOR]
End Sub

Private Sub AddedComboBox_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    [COLOR="#FF0000"]If AddedComboBox.Tag = vbNullString Then Exit Sub
    UFParent.Controls(AddedComboBox.Tag).Text = AddedComboBox.Text[/COLOR]
End Sub

The name of the associated text box is put in each added combo box's .Tag property

 

[keaden]

Hello again Mike,
I changed the code as you said, unfortunately there was no output into the textbox,
Does it matter if the dynamically created controls are within a frame?

 

[mikerickson]

No.
Did you put the Class1 code in a class module?

EDIT:
Figured it out, you have to duplicate the Mulipage1_AddControl routine for every Multipage or Frame that has a control added to it.

If you are adding the Frames as well as the controls, it would be best to create the Class1 objects and the put them into the AdddedComboboxes collection in the code where they are made.

Dim newAddedCombo as Class1

With theCombo
    ' code
End With

Set newAddedCombo = New Class1
Set newAddedCombo.AddedComboBox = theCombo
AddedComboBoxes.Add newAddedCombo

' code

Set newAddedCombo = Nothing