stevenlmas
asked on
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)
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)
GrahamSkan
Is this a Word document or a VBA user form?
Perhaps you could post the relevant bit of your code.
I mean the bit where you add the controls.
ASKER
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.FrameJob
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
(especially when you don't know)
should be
(especially when you don't know how many controls/etc you'll be using)
ASKER
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
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
ASKER
Thanks, Matt
I see where you're going... I'm getting an invalid Redim on
ReDim Preserve ctrlArray(UBound(ctrlArray
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.FrameJob
"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
Set ctrlArray(UBound(ctrlArray
BtnTop = BtnTop + 75
Set NewDesc = JobMacrosMainForm.FrameJob
With NewDesc
.Top = DescTop
.Left = DescLeft
.Height = DescHeight
.Width = DescWidth
.Caption = JobMacros(i, 1)
End With
DescTop = DescTop + 75
Set NewCmt = JobMacrosMainForm.FrameJob
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.FrameJob
Next i
JobMacrosMainForm.Show
'*************************
'JobMacrosMainForm
Private Sub UserForm_Initialize()
ReDim ctrlArray(0)
Call JobMacros.JobMacrosMain 'I dont know when you call this
End Sub
I see where you're going... I'm getting an invalid Redim on
ReDim Preserve ctrlArray(UBound(ctrlArray
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.FrameJob
"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
Set ctrlArray(UBound(ctrlArray
BtnTop = BtnTop + 75
Set NewDesc = JobMacrosMainForm.FrameJob
With NewDesc
.Top = DescTop
.Left = DescLeft
.Height = DescHeight
.Width = DescWidth
.Caption = JobMacros(i, 1)
End With
DescTop = DescTop + 75
Set NewCmt = JobMacrosMainForm.FrameJob
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.FrameJob
Next i
JobMacrosMainForm.Show
'*************************
'JobMacrosMainForm
Private Sub UserForm_Initialize()
ReDim ctrlArray(0)
Call JobMacros.JobMacrosMain 'I dont know when you call this
End Sub
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
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
ASKER
Matt's Da Man
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
Let us know if you need anything else!
Matt
ASKER
Thamks guys!!
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.
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.
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.
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.