Solved

VBA/Word Dynamic button events help

Posted on 2006-11-02
14
336 Views
Last Modified: 2008-02-01
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
Comment
Question by:stevenlmas
  • 5
  • 5
  • 4
14 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17858871
Is this a Word document or a VBA user form?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17858887
Perhaps you could post the relevant bit of your code.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17858896
I mean the bit where you add the controls.
0
 

Author Comment

by:stevenlmas
ID: 17858989

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
 
LVL 35

Accepted Solution

by:
mvidas earned 400 total points
ID: 17859325
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
 
LVL 35

Expert Comment

by:mvidas
ID: 17859334
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
 

Author Comment

by:stevenlmas
ID: 17859484
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:stevenlmas
ID: 17860113
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
 
LVL 35

Expert Comment

by:mvidas
ID: 17860195
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
 

Author Comment

by:stevenlmas
ID: 17860246
Matt's Da Man
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17860326
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
 

Author Comment

by:stevenlmas
ID: 17860725
Thamks guys!!
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17861887
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17862113
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now