AfterUpdate event on Combo box.

[Darren Bartrup]

I have a form in Excel which contains a Multipage control.

On page 2 of this control I have a frame named 'frmTeams' which has a number of text boxes and combo boxes added at runtime.

The controls are filled with data taken from a database.
Everything works beautifully: everything is filled in, the scroll bars let me see all the teams (78 at present) and their specialism (displayed in the combo box).

What I'm having trouble with is recognising when one of the combo boxes has its value changed. With a permanent combo box I'd use:

Private Sub ComboBox1_AfterUpdate()

End Sub

Is there any way of doing this for controls that are added at runtime?

The code I have is:

This runs when the workbook is first opened.

Static Sub Connect()

    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & AdminDetails.txtRoot.Value & "\" & DBName & ";"
    
Exit Sub
       
End Sub

This code adds the controls to the frame:

Option Explicit

Public Sub Get_Teams()

    Set rs = New ADODB.Recordset
    
    '// Query database to return team names & allocated specialisms.
    With rs
        .Open "SELECT [tblTeamManagement].[team_name], " & _
              "[tblTeamManagement].[team_specialism] " & _
              "FROM [tblTeamManagement];", cn, adOpenKeyset, adLockReadOnly, adCmdText
    End With

End Sub

Public Sub Show_Teams()

    Dim rs1 As ADODB.Recordset
    Dim tmpCtrl   As Control
    Dim iCtr        As Integer
    
    Set rs1 = New ADODB.Recordset
    
    '// Query database to return available specialisms.
    With rs1
        .Open "SELECT [tblTeamSpecialism].[fldSpecialism] " & _
              "FROM [tblTeamSpecialism];", cn, adOpenKeyset, adLockReadOnly, adCmdText
    End With
    
    rs1.MoveFirst
    rs.MoveFirst
    Do While Not rs.EOF
        '// Add textbox to display team name.
        Set tmpCtrl = AdminDetails.frmTeams.Controls.Add("Forms.TextBox.1", _
            "Name" & rs.AbsolutePosition)
        '// Position textbox on form.
        tmpCtrl.Left = 6
        tmpCtrl.Height = 18
        tmpCtrl.Width = 150
        tmpCtrl.Top = (rs.AbsolutePosition - 2) * 18 + 24
        '// Fill textbox with value from database.
        tmpCtrl = rs.Fields(0).Value
        
        '// Add combobox to display team specialism.
        Set tmpCtrl = AdminDetails.frmTeams.Controls.Add("Forms.ComboBox.1", _
            "Spec" & rs.AbsolutePosition)
        '// Postion combobox on form.
        tmpCtrl.Left = 160
        tmpCtrl.Height = 18
        tmpCtrl.Width = 50
        tmpCtrl.Top = (rs.AbsolutePosition - 2) * 18 + 24
        '// Add options to combobox using values from database.
        Do While Not rs1.EOF
            With tmpCtrl
                .AddItem rs1.Fields(0).Value
            End With
            rs1.MoveNext
        Loop
        '// Move back to first record in specialism table.
        rs1.MoveFirst
        '// Set value of combobox from database.
        tmpCtrl = rs.Fields(1).Value
        
        rs.MoveNext
    Loop
    
    '// Set scroll height for vertical scroll bar.
    AdminDetails.frmTeams.ScrollHeight = (rs.RecordCount) * 18 + 24
    
End Sub

As always, many thanks in advance for any help.

 

[Andrew Poulsom]

You can use the CreateEventProc method of the CodeModule object to create an event procedure. See:

http://www.cpearson.com/excel/vbe.htm

 

[Darren Bartrup]

Hi Andrew.

Thanks for the reply - I've only just managed to find the time to try this out and I've run into a few problems.

The CreateEventProc kept failing on the StartLine = .... line. I altered the line to

StartLine = .CreateEventProc("Change", tmpCtrl.Name) + 1

and it really didn't like it.

So next I tried this code, added to my code in the post above after the combo box had been added:

        Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("frmSwitchboard").CodeModule
        With VBCodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, _
                "Private Sub " & tmpCtrl.Name & "_Change()" & Chr(13) & _
                "HandleEvent tmpCtrl.Name" & Chr(13) & _
                "End Sub"
        End With

This worked to a certain degree, in that the code on my userform appeared as I expected:

Private Sub Spec1_Change()
HandleEvent tmpCtrl.Name
End Sub

But before the form appears I get an automation error saying that the The object invoked has disconnected from its clients.

Any ideas please?

 

[Andrew Poulsom]

What is HandleEvent and where is it?

 

[Darren Bartrup]

HandleEvent is in a seperate module and isn't finished yet:

Public Sub HandleEvent(strValue)

    Debug.Print strValue

End Sub

At the moment I'm just trying to capture the event.

The number at the end of the combo box name (Spec1) is the absolute position in a database recordset. This is where the initial value of the combo box is taken from (the whole contents of the combobox are taken from another recordset).
My line of thinking was if I could pass the control name to the HandleEvent procedure then I can update the relevant record in the database with the new value in the combobox.

 

[Andrew Poulsom]

Where is the code that adds the Controls? If it's in the UserForm module I can imagine it may cause problems.

 

[Darren Bartrup]

Nope, the code that adds the controls is in a 'normal' module.

When the workbook opens this code is executed:

Private Sub Workbook_Open()

    '//Set the time stamp on the Admin worksheet.
    Worksheets("Admin").Range("B19") = Now
    
    '//Initialise the global variables.
    GlobalVariables
    
    '//Load the Switchboard form
    Load frmSwitchboard
    
    Populate_FileLocations
    
    If frmSwitchboard.txtStatus12 = "OK" Then
        Connect
    Else
        ' Do something else if not successful connection to DB
    End If
    
    Populate_TeamNames
    
    frmSwitchboard.Show
    
End Sub

The Populate_TeamNames procedure creates the comboboxes and is basically a renamed version of the Show_Teams procedure listed in my first post on this thread.

 

[Andrew Poulsom]

I don't have time to experiment with this now, and I won't be around for a few days.

If CreateEventProc is causing problems, would it be possible to add hidden controls at design time, then use the initialize event to populate them and make them visible, as necessary?

 

[Darren Bartrup]

AfterUpdate event on Combo box (Solved).

Managed to solve the problem now Andrew. Thanks for the help though.

I created a new class module called cComboEvents and entered this code:

Option Explicit

Public WithEvents Cmb As MSForms.ComboBox

Private Sub cmb_Change()

    EventProc

End Sub

Public Sub EventProc()

    Dim rs3     As ADODB.Recordset
    
    Set rs3 = New ADODB.Recordset
        
    cn.Execute "UPDATE tblTeamManagement " & _
               "SET tblTeamManagement.team_specialism = """ & Cmb.Value & """ " & _
               "WHERE (([tblTeamManagement]![team_name] = """ & Cmb.Tag & """));", , adExecuteNoRecords
    
End Sub

cn is the connection to my database and the idea is that whenever a combo box containing a specialism is changed then it is updated in the database.

I then have this code in a seperate module which runs when the userform is loaded (and before it is displayed):

Public Sub Populate_TeamNames()

    Dim rs1     As New ADODB.Recordset
    Dim rs2     As New ADODB.Recordset
    Dim tmpCtrl As Control
    Dim CmbEvnt As cComboEvents
    
    Get_TeamDetails rs1
    Get_TeamSpec rs2
    
    rs1.MoveFirst
    rs2.MoveFirst
    
    Do While Not rs1.EOF
        '//Add textbox to display team name.
        Set tmpCtrl = frmSwitchboard.frmTeams.Controls.Add("Forms.TextBox.1", _
                      "Name" & rs1.AbsolutePosition)
        '//Position textbox on form.
        tmpCtrl.Left = 6
        tmpCtrl.Height = 18
        tmpCtrl.Width = 150
        tmpCtrl.Top = (rs1.AbsolutePosition - 2) * 18 + 24
        '//Populate textbox with value from database.
        tmpCtrl = rs1.Fields(0).Value
        
        '//Add combobox to display team specialism.
        Set tmpCtrl = frmSwitchboard.frmTeams.Controls.Add("Forms.ComboBox.1", _
                      "Spec" & rs1.AbsolutePosition)
        '//Position combobox on form.
        tmpCtrl.Left = 160
        tmpCtrl.Height = 18
        tmpCtrl.Width = 50
        tmpCtrl.Top = (rs1.AbsolutePosition - 2) * 18 + 24
        '//Add the team name to the combobox tag.
        tmpCtrl.Tag = rs1.Fields(0).Value
        '//Populate combobox with values from database.
        Do While Not rs2.EOF
            With tmpCtrl
                .AddItem rs2.Fields(0).Value
            End With
            rs2.MoveNext
        Loop
        rs2.MoveFirst
        '//Set value of combobox with value from database.
        tmpCtrl = rs1.Fields(1).Value
        rs1.MoveNext
               
        '//Set the combobox event - cComboEvents class module.
        Set CmbEvnt = New cComboEvents
        Set CmbEvnt.Cmb = tmpCtrl
        CmbColct.Add CmbEvnt
                
    Loop
       
    '//Set scroll height for vertical scroll bar.
    frmSwitchboard.frmTeams.ScrollHeight = (rs1.RecordCount) * 18 + 24
    
End Sub

The team name is displayed in a textbox next to the combo box and is stored in the rs1 recordset. So I added the team name to the comboboxs tag property and added the last three lines of code before the loop which attaches the new event to each combobox.

I found a pointer to the solution on this post: http://www.mrexcel.com/board2/viewtopic.php?t=53597&highlight=control+form .