We help IT Professionals succeed at work.

VBA/Word Dynamic button events help

stevenlmas
stevenlmas asked
on
Medium Priority
438 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)
Comment
Watch Question

GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Is this a Word document or a VBA user form?
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Perhaps you could post the relevant bit of your code.
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
I mean the bit where you add the controls.

Author

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
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
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)

Author

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

Author

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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Matt's Da Man
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Thamks guys!!
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.