• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

VBA/Word Dynamic button events help

Hi,

I've created a form that dynamically adds up to 50 command buttons named CB01 - CB50. Since I have a max of fifty I created the click procedures in advance, rather than trying to create the event procedures on the fly, as that was giving me a hard time. So I have 50 on click procedures stored in the form E.G.:

Private Sub CB01_Click()
   MsgBox "Hi There"
End Sub

Private Sub CB02_Click()
   MsgBox "Hi There"
End Sub

...etc

I think it has to do with the fact that I am creating the buttons on the fly, when I click the buttons the Click events never fire.

Any ideas on how to get this working?

Thanks!
Steven Lee (stevenlmas at comcast net)
0
stevenlmas
Asked:
stevenlmas
  • 5
  • 5
  • 4
1 Solution
 
GrahamSkanRetiredCommented:
Is this a Word document or a VBA user form?
0
 
GrahamSkanRetiredCommented:
Perhaps you could post the relevant bit of your code.
0
 
GrahamSkanRetiredCommented:
I mean the bit where you add the controls.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
stevenlmasAuthor Commented:

Here's the bit where I add the butttons

Sub Main

   For i = 0 To UBound(JobMacros)
        If JobMacros(i, 0) <> "" Then
           
            NewBtnName = "CB" & Format(i, "00")
            Set NewBtn = JobMacrosMainForm.FrameJobMacs.Controls.Add("Forms.CommandButton.1", NewBtnName, True)
            With NewBtn
                .Top = BtnTop
                .Left = BtnLeft
                .Width = BtnWidth
                .Height = BtnHeight
                .Caption = NewBtnName
                .Font.Size = 7
             End With
               
 Next i
End Sub
'***********HERE' S THE FORM**************

Public WithEvents NewBtn As CommandButton   'tried this
Public WithEvents CB01 As CommandButton      'and this both private and public
Public WithEvents NewDesc As Label
Public WithEvents NewCmt As TextBox

Private Sub cmdCancel_Click()
    Me.Tag = "Cancel"
    Me.Hide
End Sub

Private Sub CB00_Click()
    MsgBox "FOO"
End Sub
Private Sub CB01_Click()
    MsgBox "FOO"
End Sub

Thanks!
Steve
0
 
mvidasCommented:
Steve,

What I've done for this (especially when you don't know) is use a class module to catch the events.

Insert a new class module, and set the name to be "SteveCB"
Put the following code into that class module:

Public WithEvents btnCtrl As MSForms.CommandButton
Private Sub btnCtrl_Click()
 Select Case btnCtrl.Name
  Case "CB00"
   MsgBox "Hello!" & vbCrLf & btnCtrl.Caption
  Case "CB01"
   MsgBox "Hey" & vbCrLf & btnCtrl.Caption
 End Select
End Sub


Add this to the userform_initialize code:

Private Sub UserForm_Initialize()
 ReDim ctrlArray(0)
' Call Main 'I dont know when you call this
End Sub


Then use this for your main:

Sub Main()
 Dim NewBtn As MSForms.CommandButton, MyCtrl As SteveCB, i As Long
 For i = 0 To UBound(JobMacros)
  If JobMacros(i, 0) <> "" Then
   NewBtnName = "CB" & Format(i, "00")
   Set MyCtrl = New SteveCB
   Set NewBtn = JobMacrosMainForm.FrameJobMacs.Controls.Add( _
    "Forms.CommandButton.1", NewBtnName, True)
   With NewBtn
    .Top = BtnTop
    .Left = BtnLeft
    .Width = BtnWidth
    .Height = BtnHeight
    .Caption = NewBtnName
    .Font.Size = 7
   End With
   Set MyCtrl.btnCtrl = NewBtn
   ReDim Preserve ctrlArray(UBound(ctrlArray) + 1)
   Set ctrlArray(UBound(ctrlArray)) = MyCtrl
  End If
 Next i
End Sub


The btnCtrl_Click event in the class module is where you can set what each click does.

Matt
0
 
mvidasCommented:
Small unimportant typo
(especially when you don't know)
should be
(especially when you don't know how many controls/etc you'll be using)
0
 
stevenlmasAuthor Commented:
I have a 3D array with 50 items in it
JobMacros(0,0) = "001234_01"              'jobnumber / macro number
JobMacros(0,1) = "Macro Description"    'what the macro does
JobMacros(0,2) = "Comments"               'Info to user (when to run, gotchas etc)


so the number of controls won't exceed 50

0
 
stevenlmasAuthor Commented:
Thanks, Matt

I see where you're going... I'm getting an invalid Redim on

ReDim Preserve ctrlArray(UBound(ctrlArray) + 1)

I'm trying to figure out why I can't redim.

I have a form with a scrolling frame.  JobMacrosMain iterates thru the array and puts a button, a label, and a text field on the form for each element of the array that's not empty. (the idea being you can sort through all of the avail. macros and click the button to run the macro on the open doc)

Here's my code...


'***************************
'Class Module SteveCB
Public WithEvents btnCtrl As MSForms.CommandButton
Private Sub btnCtrl_Click()
 Select Case btnCtrl.Name
  Case "CB00"
   MsgBox "Hello!" & vbCrLf & btnCtrl.Caption
  Case "CB01"
   MsgBox "Hey" & vbCrLf & btnCtrl.Caption
 End Select
End Sub
'***************************

'***************************
'JOb Macros
Sub JobMacrosMain()
    Dim JobMacros(50, 2)
   
    JobMacros(0, 0) = "004042_01"
    JobMacros(0, 1) = "Convert author files"
    JobMacros(0, 2) = "This is the|comments section|and multi lines"
   
    JobMacros(1, 0) = "000000_02"
    JobMacros(1, 1) = "Test macro 2"
    JobMacros(1, 2) = "Test Description 2|This is the description"

    JobMacros(2, 0) = "000000_03"
    JobMacros(2, 1) = "Test Macro 3"
    JobMacros(2, 2) = "Test Description 3|This is the description"

    JobMacros(3, 0) = "000000_04"
    JobMacros(3, 1) = "Test Macro 4"
    JobMacros(3, 2) = "Test Description|This is the description"

    'Starting postions / sizes for the form elements
    'Lefts
    BtnLeft = 6
    DescLeft = 60
    CmtLeft = 6
   
    'Top
    BtnTop = 10
    DescTop = 10
    CmtTop = 30
   
    'Widths
    BtnWidth = 50
    DescWidth = 250
    CmtWidth = 300
   
    'Heights
    BtnHeight = 20
    DescHeight = 15
    CmtHeight = 45
   
    SpcBtw = 5
   
   ' Load JobMacrosMainForm
    ScrlHeight = 50
           
    Dim NewBtn As MSForms.CommandButton, MyCtrl As SteveCB, i As Long
        For i = 0 To UBound(JobMacros)
            If JobMacros(i, 0) <> "" Then
                NewBtnName = "CB" & Format(i, "00")
                Set MyCtrl = New SteveCB
                Set NewBtn = JobMacrosMainForm.FrameJobMacs.Controls.Add( _
                 "Forms.CommandButton.1", NewBtnName, True)
                With NewBtn
                    .Top = BtnTop
                    .Left = BtnLeft
                    .Width = BtnWidth
                    .Height = BtnHeight
                    .Caption = NewBtnName
                    .Font.Size = 7
                End With
                Set MyCtrl.btnCtrl = NewBtn
                ReDim Preserve ctrlArray(UBound(ctrlArray) + 1)
                Set ctrlArray(UBound(ctrlArray)) = MyCtrl

             BtnTop = BtnTop + 75
             

                             
            Set NewDesc = JobMacrosMainForm.FrameJobMacs.Controls.Add("Forms.Label.1", "LBL_" & JobMacros(i, 0), True)
            With NewDesc
                .Top = DescTop
                .Left = DescLeft
                .Height = DescHeight
                .Width = DescWidth
                .Caption = JobMacros(i, 1)
             End With
             DescTop = DescTop + 75
             
            Set NewCmt = JobMacrosMainForm.FrameJobMacs.Controls.Add("Forms.TextBox.1", "TXT_" & JobMacros(i, 0), True)
            With NewCmt
                .Top = CmtTop
                .Left = CmtLeft
                .Height = CmtHeight
                .Width = CmtWidth
                .MultiLine = True
                .Text = replace(JobMacros(i, 2), "|", Chr(11))
             End With
             CmtTop = CmtTop + 75
             
       End If
       
       ScrlHeight = ScrlHeight + 55
       JobMacrosMainForm.FrameJobMacs.ScrollHeight = ScrlHeight
       
    Next i

    JobMacrosMainForm.Show
'******************************

'JobMacrosMainForm
Private Sub UserForm_Initialize()
ReDim ctrlArray(0)
    Call JobMacros.JobMacrosMain     'I dont know when you call this
End Sub
0
 
mvidasCommented:
Oops.. forgot one key part in the copy/paste!
Put this above all procedures in a module:

Public ctrlArray() As SteveCB

You could probably put your JobMacrosMain sub inside the userform code, though if you do that you'll have to change "Public" in the above line to just "Dim" (or Private)

Matt
0
 
stevenlmasAuthor Commented:
Matt's Da Man
0
 
mvidasCommented:
Graham could have easily done the same thing :) I just assumed he was out of work / away from the computer, figured I'd pitch in
Let us know if you need anything else!
Matt
0
 
stevenlmasAuthor Commented:
Thamks guys!!
0
 
GrahamSkanRetiredCommented:
Thanks for your confidence, Matt.

I do have a problem whereby EE mail notifications seem to be taking more than an hour to reach me, but I really didn't have a ready answer, so your points are well deserved.
0
 
GrahamSkanRetiredCommented:
stevenlmas,

This is only your second question in over two years' membership, but you should be aware that you shouldn't put your email address, however disguised, in your question or comments.

The main reason is that, in the ethos of this site, it is unnecessary. Correspondence should be via comments, so that it is visible to, and hence shared by, all viewers.

The second is that it invites harvesting by spammers. You have disguised it a bit from such automation, as have I in my profile, so hopefully it shouldn't be a major problem for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now