Solved

VBA/Word Dynamic button events help

Posted on 2006-11-02
14
342 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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
 

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

773 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