Macro wont run from dynamically added command button

[lokiluke]

Hello,

i have a form that counts the lines in a spreadsheet then adds that amount of textboxes and command buttons during Userform_Initialize(). the textboxes cant be referenced with their name but can be with .Controls(). my buttons on the otherhand are not generating Click events, so i cant get them to run any macros. anyone know how i can get them to run a macro? the code i am using is below. AddRow() is called by the userform_inialize()

Private Sub AddRow()

Dim tbArea As Object 'the textbox for area
Dim tbJumbo As Object 'the textbox for the jumbo
Dim tbHeading As Object 'the textbox for heading
Dim tbStatus As Object 'the textbox for headings status
Dim tbDesc As Object 'the textbox for headings description
Dim tbMS As Object 'the textbox for mapped and sampled field
Dim btnMSbutton As Object 'the mapped sample button
Dim cbCheckRemove As Object 'the check remove checkbox

intcontrolcount = intcontrolcount + 1 'used to increment the controls number

Set tbArea = Me.frHeadings.Controls.Add("forms.textbox.1", "txtArea" & intcontrolcount, True)
Set tbJumbo = Me.frHeadings.Controls.Add("forms.textbox.1", "txtJumbo" & intcontrolcount, True)
Set tbHeading = Me.frHeadings.Controls.Add("forms.textbox.1", "txtHeading" & intcontrolcount, True)
Set tbStatus = Me.frHeadings.Controls.Add("forms.textbox.1", "txtStatus" & intcontrolcount, True)
Set tbDesc = Me.frHeadings.Controls.Add("forms.textbox.1", "txtDesc" & intcontrolcount, True)
Set tbMS = Me.frHeadings.Controls.Add("forms.textbox.1", "txtMS" & intcontrolcount, True)
Set btnMSbutton = Me.frHeadings.Controls.Add("forms.commandbutton.1", "btnMS" & intcontrolcount, True)
Set cbCheckRemove = Me.frHeadings.Controls.Add("forms.checkbox.1", "chkCheckRemove" & intcontrolcount, True)

'omitted properties of other controls normally sits here

With btnMSbutton
    .BackColor = &H8000000F
    .Cancel = False
    .Height = 13
    If (intcontrolcount Mod 2) = 0 Then 'offsets the buttons to make it more readable
        .Left = 600
        Else: .Left = 570
    End If
    .Top = 13 * (intcontrolcount - 1)
    .Width = 25
    '.tabindex=
End With
End Sub

when i want the button named btnMS1 to run a macro i would normally use:

Private Sub btnMS1_Click()

    intControlNumber = 1 '
    
    Application.Run "subRoutines.ButtonMapped"
    
    
End Sub

any help would be appreciated as i cant seem to find any info on google.

-luke

 

[RoryA]

Typically, on the assumption that your buttons all do pretty much the same thing, you would use a class module to handle the click events of all your buttons (you can use the Tag property of each button to store any button-specific information).

Sample of a class for textboxes can be found here - should be fairly obvious to adapt for commandbuttons, but post back if you have problems.

 

[lokiluke]

cheers, time to learn something new will let you know how it goes

 

[lokiluke]

ok after learning a little about using the class module this is what i have tried

Dim aButtons() As MappedSampled '(MappedSampled is the class i created

Private Sub AddRow()

Dim btnMSbutton As Object 'the mapped sample button

intcontrolcount = intcontrolcount + 1

Set btnMSbutton = Me.frHeadings.Controls.Add("forms.commandbutton.1", "btnMS" & intcontrolcount, True)

With btnMSbutton
    .BackColor = &H8000000F
    .Cancel = False
    .Height = 13
    If (intcontrolcount Mod 2) = 0 Then 'offsets the buttons to make it more readable
        .Left = 600
        Else: .Left = 570
    End If
    .Top = 13 * (intcontrolcount - 1)
    .Width = 25
    '.tabindex=
    .Tag = intcontrolcount
    ReDim Preserve aButtons(1 To intcontrolcount)
    Set aButtons(intcontrolcount) = btnMSbutton
End With

end sub

the class module i created is:

Public WithEvents ButtonGroup As CommandButton


Private Sub ButtonGroup_Click()
MsgBox ButtonGroup.Tag
End Sub

i thought this would store the command button control in the aButtons() array which was declared as the MappedSampled class. then when the button is pressed the MappedSampled class's ButtonGroup_Click() sub would rund my code.

unfortunatly when i step thru the code it fails when i run the 'Set aButtons(intcontrolcount) = btnMSbutton'. it seems to jump to a random section of code in the userform that calls the userform that the above code is in and loops indefinitely.

the lines it jumps to after stepping past the 'Set aButtons()' are the 3 below, it just cycles between those 3:

frmJobReq2.Controls("txtStatus" & intNextLine).Value = Workbooks(strStatusSheetFilename).Sheets("Status Sheet").Cells(c.Row, 7).Value

If InStr(UCase(Workbooks(strStatusSheetFilename).Sheets("Status Sheet").Cells(c.Row, 3).Value), "STRIP") > 0 Then

End If

can you spot why?

thanks,

luke

 

[RoryA]

You need to create a new instance of your Mappedsampled class, assign the created button to its ButtonGroup member, then add the class instance (not the button) to the array.

 

[lokiluke]

beautiful! there is a good tip for young players there "dont try to write macros at 3am".
i instantiated it then assigned the button to the .buttongroup as you said. now to start on the code for what to do when the button is pressed.

the code (changes in bold) for anyone else looking is:

Dim aButtons() As [B]New[/B] MappedSampled 

Private Sub AddRow()

Dim btnMSbutton As Object 'the mapped sample button

Set btnMSbutton = Me.frHeadings.Controls.Add("forms.commandbutton.1", "btnMS" & intcontrolcount, True)

With btnMSbutton
    .BackColor = &H8000000F
    .Cancel = False
    .Height = 13
    If (intcontrolcount Mod 2) = 0 Then 'offsets the buttons to make it more readable
        .Left = 600
        Else: .Left = 570
    End If
    .Top = 13 * (intcontrolcount - 1)
    .Width = 25
    '.tabindex=
    .Tag = intcontrolcount
    ReDim Preserve aButtons(1 To intcontrolcount)
    Set aButtons(intcontrolcount)[B].ButtonGroup[/B] = btnMSbutton
End With

thanks for your help

 

[Norie]

Just out if curiosity, how many buttons/textboxes are you adding and what will the click event do?

 

[lokiluke]

for every line on the spreadsheet (varies around the 50 mark) it will add 6 textbox's in a line followed by a button and then a checkbox. the info in the text box is info about underground headings in a mine. when the button is pressed it shifts its corresponding array variable from 1 to 2 to 3 then back to 1. 1 indicates a neutral status, 2 indicates it has been 'mapped' and colours the button and textbox green, 3 indicates it requires 'mapping' and colours the button and textbox yellow. the checkbox marks the entire row of textboxs on the form red and wont import that line into the output spreadsheet.

i have decided to dynamically add the controls as the previous version was preset with 50 lines, if there are more rows it just pops up a msgbox telling the user they have to manually adjust. but i dont trust them to do it correctly.

funny enough this rewrite came about from someone inserting a single line into the source workbook. it was supposed to be a constant layout and i directly referenced some cells and threw it all out of wack. lesson learnt... dont assume