J
JB
Hi Folks,
I can do this in VB6 but can't find a way to do it using VBA userforms! Any
pointers?
J
I can do this in VB6 but can't find a way to do it using VBA userforms! Any
pointers?
J
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
E
eowen
The exact approach to this depends a bit on whether you know ahead of time how many controls you might want to add at runtime, or whether you need to be completely open ended
In any case, you can add controls at run-time in VBA using the Add method of the form's Controls collection
dim newControl as Contro
set newControl = UserForm1.Controls.Add(some syntax here specifying the type of control
You can then manipulate newControl just like you can any other control, including processing events
If, like I did once, you need to be able to handle any arbitrary number of controls at runtime, and you need to be able to process events for them, it gets a little trickier but not much. You basically just need to create a "wrapper" class for the controls to get at their events
There's pretty good documentation for adding a finite number of known controls at runtime in the Microsoft VB(A) knowledge base stuff on MSDN. Search for "runtime" and "controls"
If you need to handle any arbitrary number of controls, let me know here and I can dig up the details on how I did it
eowen
In any case, you can add controls at run-time in VBA using the Add method of the form's Controls collection
dim newControl as Contro
set newControl = UserForm1.Controls.Add(some syntax here specifying the type of control
You can then manipulate newControl just like you can any other control, including processing events
If, like I did once, you need to be able to handle any arbitrary number of controls at runtime, and you need to be able to process events for them, it gets a little trickier but not much. You basically just need to create a "wrapper" class for the controls to get at their events
There's pretty good documentation for adding a finite number of known controls at runtime in the Microsoft VB(A) knowledge base stuff on MSDN. Search for "runtime" and "controls"
If you need to handle any arbitrary number of controls, let me know here and I can dig up the details on how I did it
eowen
P
Peter Hewett
Hi eowen
I think getting the layout right using VBA is more difficult than the code. Creating a
helper class to handle the events the controls generate is easy compared to the layout.
The controls you add take on the Forms font size, but try creating 6 or so Label controls,
all with the same caption text and I'll bet at least 1 displays using a slightly different
apparent font size to the others. That's why when you manually layout VBA forms you end
up using a Top property of something like 36.2 or 36.3 instead of 36!
Good luck - Peter
HTH + Cheers - Peter
I think getting the layout right using VBA is more difficult than the code. Creating a
helper class to handle the events the controls generate is easy compared to the layout.
The controls you add take on the Forms font size, but try creating 6 or so Label controls,
all with the same caption text and I'll bet at least 1 displays using a slightly different
apparent font size to the others. That's why when you manually layout VBA forms you end
up using a Top property of something like 36.2 or 36.3 instead of 36!
Good luck - Peter
J
The exact approach to this depends a bit on whether you know ahead of time how many controls you might want to add at runtime, or whether you need to be completely open ended.
In any case, you can add controls at run-time in VBA using the Add method of the form's Controls collection:
dim newControl as Control
set newControl = UserForm1.Controls.Add(some syntax here specifying the type of control)
You can then manipulate newControl just like you can any other control, including processing events.
If, like I did once, you need to be able to handle any arbitrary number of controls at runtime, and you need to be able to process events for them, it gets a little trickier but not much. You basically just need to create a "wrapper" class for the controls to get at their events.
There's pretty good documentation for adding a finite number of known controls at runtime in the Microsoft VB(A) knowledge base stuff on MSDN. Search for "runtime" and "controls".
If you need to handle any arbitrary number of controls, let me know here and I can dig up the details on how I did it.
eowen
HTH + Cheers - Peter
J
Jerry Bodoff
Hi eowen,
If it is not too much trouble I would like to see how you
solved the problem of trapping an event for any one of
the controls. I have created 1-n controls (command
buttons) on a form and am now getting to the point of
being able to trap the click event for any one on them.
I assume that your term "wrapper" class refers to putting
the controls in a frame (which is what I have done) or is
it something else?
Thanks in advance for any help.
Jerry Bodoff
add at runtime, or whether you need to be completely open
ended.
be able to process events for them, it gets a little
trickier but not much. You basically just need to create
a "wrapper" class for the controls to get at their events.
(A) knowledge base stuff on MSDN. Search for "runtime"
and "controls".
did it.
If it is not too much trouble I would like to see how you
solved the problem of trapping an event for any one of
the controls. I have created 1-n controls (command
buttons) on a form and am now getting to the point of
being able to trap the click event for any one on them.
I assume that your term "wrapper" class refers to putting
the controls in a frame (which is what I have done) or is
it something else?
Thanks in advance for any help.
Jerry Bodoff
know ahead of time how many controls you might want to-----Original Message-----
J
The exact approach to this depends a bit on whether you
add at runtime, or whether you need to be completely open
ended.
using the Add method of the form's Controls collection:In any case, you can add controls at run-time in VBA
specifying the type of control)dim newControl as Control
set newControl = UserForm1.Controls.Add(some syntax here
other control, including processing events.You can then manipulate newControl just like you can any
arbitrary number of controls at runtime, and you need toIf, like I did once, you need to be able to handle any
be able to process events for them, it gets a little
trickier but not much. You basically just need to create
a "wrapper" class for the controls to get at their events.
number of known controls at runtime in the Microsoft VBThere's pretty good documentation for adding a finite
(A) knowledge base stuff on MSDN. Search for "runtime"
and "controls".
let me know here and I can dig up the details on how IIf you need to handle any arbitrary number of controls,
did it.
P
Peter Hewett
Hi Jerry Bodoff
What you do is put the event handling procedures in a Class module and then instantiate
the class and link the control to the class. Here's how:
<--------Code to go in your Form Module----------->
' Event handlers must Persist outside the procedure that creates them
Private colControls As Collection
Private Sub UserForm_Initialize()
Dim ctlTB As MSForms.TextBox
Dim ctlCB As MSForms.CheckBox
Dim ctbNew As clsTextBox
Dim ccbNew As clsCheckBox
Dim lngTop As Long
Dim lngIndex As Long
' Locate first control
lngTop = 12
Set colControls = New Collection
For lngIndex = 1 To 3
' New TextBox control
Set ctlTB = Me.Controls.Add("Forms.TextBox.1", "txtTest" & lngIndex)
ctlTB.Top = lngTop
ctlTB.Left = 50
lngTop = lngTop + 30
' Create event handler wrapper and hook new TextBox control to wrapper
Set ctbNew = New clsTextBox
Set ctbNew.mtxtNew = ctlTB
' Keep the event handler alive
colControls.Add ctbNew, CStr(lngIndex)
Next
For lngIndex = 1 To 3
' New CheckBox control
Set ctlCB = Me.Controls.Add("Forms.CheckBox.1", "chkTest" & lngIndex)
ctlCB.Top = lngTop
ctlCB.Left = 50
lngTop = lngTop + 24
' Create event handler wrapper and hook new CheckBox control to wrapper
Set ccbNew = New clsCheckBox
Set ccbNew.mchkNew = ctlCB
' Keep the event handler alive
colControls.Add ccbNew, CStr(lngIndex + 3)
Next
End Sub
<------------Code to go in the "clsTextBox" Class Module----------->
Public WithEvents mtxtNew As MSForms.TextBox
Private Sub mtxtNew_Change()
MsgBox "clsTextBox_Change"
End Sub
<------------Code to go in the "clsCheckBox" Class Module----------->
Public WithEvents mchkNew As MSForms.CheckBox
Private Sub mchkNew_Click()
MsgBox "clsCheckBox_Click"
End Sub
HTH + Cheers - Peter
What you do is put the event handling procedures in a Class module and then instantiate
the class and link the control to the class. Here's how:
<--------Code to go in your Form Module----------->
' Event handlers must Persist outside the procedure that creates them
Private colControls As Collection
Private Sub UserForm_Initialize()
Dim ctlTB As MSForms.TextBox
Dim ctlCB As MSForms.CheckBox
Dim ctbNew As clsTextBox
Dim ccbNew As clsCheckBox
Dim lngTop As Long
Dim lngIndex As Long
' Locate first control
lngTop = 12
Set colControls = New Collection
For lngIndex = 1 To 3
' New TextBox control
Set ctlTB = Me.Controls.Add("Forms.TextBox.1", "txtTest" & lngIndex)
ctlTB.Top = lngTop
ctlTB.Left = 50
lngTop = lngTop + 30
' Create event handler wrapper and hook new TextBox control to wrapper
Set ctbNew = New clsTextBox
Set ctbNew.mtxtNew = ctlTB
' Keep the event handler alive
colControls.Add ctbNew, CStr(lngIndex)
Next
For lngIndex = 1 To 3
' New CheckBox control
Set ctlCB = Me.Controls.Add("Forms.CheckBox.1", "chkTest" & lngIndex)
ctlCB.Top = lngTop
ctlCB.Left = 50
lngTop = lngTop + 24
' Create event handler wrapper and hook new CheckBox control to wrapper
Set ccbNew = New clsCheckBox
Set ccbNew.mchkNew = ctlCB
' Keep the event handler alive
colControls.Add ccbNew, CStr(lngIndex + 3)
Next
End Sub
<------------Code to go in the "clsTextBox" Class Module----------->
Public WithEvents mtxtNew As MSForms.TextBox
Private Sub mtxtNew_Change()
MsgBox "clsTextBox_Change"
End Sub
<------------Code to go in the "clsCheckBox" Class Module----------->
Public WithEvents mchkNew As MSForms.CheckBox
Private Sub mchkNew_Click()
MsgBox "clsCheckBox_Click"
End Sub
HTH + Cheers - Peter
J
Jerry Bodoff
Hi Peter,
Thanks for your reply and solution.
After having done some more reading I realized that a
class module is what was meant by a "wrapper". I am just
learning about class modules and how to implement them
and up to now I have been struggling to get one to work.
Your solution goes a long way in this learning process.
Everything I have been reading has involved EXCEL. I am
getting the impression that EXCEL VBA is more common than
WORD VBA; it seems that most VBA documents, how to's,
etc. reference EXCEL.
Once again thanks a lot.
Jerry B.
Thanks for your reply and solution.
After having done some more reading I realized that a
class module is what was meant by a "wrapper". I am just
learning about class modules and how to implement them
and up to now I have been struggling to get one to work.
Your solution goes a long way in this learning process.
Everything I have been reading has involved EXCEL. I am
getting the impression that EXCEL VBA is more common than
WORD VBA; it seems that most VBA documents, how to's,
etc. reference EXCEL.
Once again thanks a lot.
Jerry B.
Class module and then instantiate-----Original Message-----
Hi Jerry Bodoff
What you do is put the event handling procedures in a
("Forms.TextBox.1", "txtTest" & lngIndex)the class and link the control to the class. Here's how:
<--------Code to go in your Form Module----------->
' Event handlers must Persist outside the procedure that creates them
Private colControls As Collection
Private Sub UserForm_Initialize()
Dim ctlTB As MSForms.TextBox
Dim ctlCB As MSForms.CheckBox
Dim ctbNew As clsTextBox
Dim ccbNew As clsCheckBox
Dim lngTop As Long
Dim lngIndex As Long
' Locate first control
lngTop = 12
Set colControls = New Collection
For lngIndex = 1 To 3
' New TextBox control
Set ctlTB = Me.Controls.Add
("Forms.CheckBox.1", "chkTest" & lngIndex)ctlTB.Top = lngTop
ctlTB.Left = 50
lngTop = lngTop + 30
' Create event handler wrapper and hook new TextBox control to wrapper
Set ctbNew = New clsTextBox
Set ctbNew.mtxtNew = ctlTB
' Keep the event handler alive
colControls.Add ctbNew, CStr(lngIndex)
Next
For lngIndex = 1 To 3
' New CheckBox control
Set ctlCB = Me.Controls.Add
J
Jerry Bodoff
Hi Peter,
Me Again. It works like a charm. I was almost there but
I missed a few concepts and I was making it more compli-
cated than it really had to be. Thanks.
I have one more question regarding this. Using this
process do I have to re-create the form each time I use
it or can I create it once and just use it thereafter?
Jerry B.
Me Again. It works like a charm. I was almost there but
I missed a few concepts and I was making it more compli-
cated than it really had to be. Thanks.
I have one more question regarding this. Using this
process do I have to re-create the form each time I use
it or can I create it once and just use it thereafter?
Jerry B.
Class module and then instantiate-----Original Message-----
Hi Jerry Bodoff
What you do is put the event handling procedures in a
("Forms.TextBox.1", "txtTest" & lngIndex)the class and link the control to the class. Here's how:
<--------Code to go in your Form Module----------->
' Event handlers must Persist outside the procedure that creates them
Private colControls As Collection
Private Sub UserForm_Initialize()
Dim ctlTB As MSForms.TextBox
Dim ctlCB As MSForms.CheckBox
Dim ctbNew As clsTextBox
Dim ccbNew As clsCheckBox
Dim lngTop As Long
Dim lngIndex As Long
' Locate first control
lngTop = 12
Set colControls = New Collection
For lngIndex = 1 To 3
' New TextBox control
Set ctlTB = Me.Controls.Add
("Forms.CheckBox.1", "chkTest" & lngIndex)ctlTB.Top = lngTop
ctlTB.Left = 50
lngTop = lngTop + 30
' Create event handler wrapper and hook new TextBox control to wrapper
Set ctbNew = New clsTextBox
Set ctbNew.mtxtNew = ctlTB
' Keep the event handler alive
colControls.Add ctbNew, CStr(lngIndex)
Next
For lngIndex = 1 To 3
' New CheckBox control
Set ctlCB = Me.Controls.Add
J
Jerry Bodoff
Hi Peter,
I forgot to ask these questions along with the previous
one about the form. I am able to trap my Click Event
without any problem. In my code I wish to have the Name
and the Caption of the CommandButton returned. I tried
to create PROPERTY GET Procedures in the class module as:
Property Get Name() As String
Set Name = mcmdButton.Name
End Property
Property Get Caption() As String
Set Caption = mcmdButton.Caption
End Property
I try to use the properties as:
Dim ButtonName as String
Dim ButtonIndex as Long
Dim ButtonClass as clsCmdButton
Set ButtonClass = New clsCmdButton
ButtonName = ButtonClass.Name
ButtonIndex = ButtonClass.Item()
When I do this I get a Block Not Set error. What am I
missing? From reading the Class Modules article
referenced in the MVP FAQ's I thought that this is the
way to return a value from the Class Module. Also I am
not sure how to create an Index Property for the controls
collection. How do I pass the controls Collection to the
class module? Do I just make it Public?
Thanks for all your help and patience in answering these
questions. It is greatly appreciated.
Jerry B.
I forgot to ask these questions along with the previous
one about the form. I am able to trap my Click Event
without any problem. In my code I wish to have the Name
and the Caption of the CommandButton returned. I tried
to create PROPERTY GET Procedures in the class module as:
Property Get Name() As String
Set Name = mcmdButton.Name
End Property
Property Get Caption() As String
Set Caption = mcmdButton.Caption
End Property
I try to use the properties as:
Dim ButtonName as String
Dim ButtonIndex as Long
Dim ButtonClass as clsCmdButton
Set ButtonClass = New clsCmdButton
ButtonName = ButtonClass.Name
ButtonIndex = ButtonClass.Item()
When I do this I get a Block Not Set error. What am I
missing? From reading the Class Modules article
referenced in the MVP FAQ's I thought that this is the
way to return a value from the Class Module. Also I am
not sure how to create an Index Property for the controls
collection. How do I pass the controls Collection to the
class module? Do I just make it Public?
Thanks for all your help and patience in answering these
questions. It is greatly appreciated.
Jerry B.
Class module and then instantiate-----Original Message-----
Hi Jerry Bodoff
What you do is put the event handling procedures in a
("Forms.TextBox.1", "txtTest" & lngIndex)the class and link the control to the class. Here's how:
<--------Code to go in your Form Module----------->
' Event handlers must Persist outside the procedure that creates them
Private colControls As Collection
Private Sub UserForm_Initialize()
Dim ctlTB As MSForms.TextBox
Dim ctlCB As MSForms.CheckBox
Dim ctbNew As clsTextBox
Dim ccbNew As clsCheckBox
Dim lngTop As Long
Dim lngIndex As Long
' Locate first control
lngTop = 12
Set colControls = New Collection
For lngIndex = 1 To 3
' New TextBox control
Set ctlTB = Me.Controls.Add
("Forms.CheckBox.1", "chkTest" & lngIndex)ctlTB.Top = lngTop
ctlTB.Left = 50
lngTop = lngTop + 30
' Create event handler wrapper and hook new TextBox control to wrapper
Set ctbNew = New clsTextBox
Set ctbNew.mtxtNew = ctlTB
' Keep the event handler alive
colControls.Add ctbNew, CStr(lngIndex)
Next
For lngIndex = 1 To 3
' New CheckBox control
Set ctlCB = Me.Controls.Add
P
Peter Hewett
Hi Jerry Bodoff
I think your actually missing a couple of bits. Firstly you're trying to get the class to
return the control name before you hook the class up! So internally the classes control
pointer (the variable you declare WithEvents) is null. Secondly you're trying to use the
controls event handler class (your ButtonClass) when you have no reference to it. This
leads on to this problem you declare:
Dim ButtonClass as clsCmdButton
as a pointer to a class module, you then try to use it as a collection object:
ButtonIndex = ButtonClass.Item()
Even if ButtonClass was a collection, you can't use that syntax. The only way of finding
the numeric index value of an item in a collection is to iterate the collection. Which is
normally rather slow and pointless is you have it's key value (the control name in this
case).
You need to try to do more within the event handler an less outside it. Or use the
controls name in the Form rather than trying to access it indirectly via it's event
handler hook.
If you don't understand the code step through it line-by-line, it's pretty short.
HTH + Cheers - Peter
I think your actually missing a couple of bits. Firstly you're trying to get the class to
return the control name before you hook the class up! So internally the classes control
pointer (the variable you declare WithEvents) is null. Secondly you're trying to use the
controls event handler class (your ButtonClass) when you have no reference to it. This
leads on to this problem you declare:
Dim ButtonClass as clsCmdButton
as a pointer to a class module, you then try to use it as a collection object:
ButtonIndex = ButtonClass.Item()
Even if ButtonClass was a collection, you can't use that syntax. The only way of finding
the numeric index value of an item in a collection is to iterate the collection. Which is
normally rather slow and pointless is you have it's key value (the control name in this
case).
You need to try to do more within the event handler an less outside it. Or use the
controls name in the Form rather than trying to access it indirectly via it's event
handler hook.
If you don't understand the code step through it line-by-line, it's pretty short.
HTH + Cheers - Peter
J
Jerry Bodoff
Hi Peter,
Thanks again. I am beginning to understand what is going
on. I will do as you suggest and step through it and see
what is happening.
Once again thanks for all your help.
Jerry Bodoff
Thanks again. I am beginning to understand what is going
on. I will do as you suggest and step through it and see
what is happening.
Once again thanks for all your help.
Jerry Bodoff
you're trying to get the class to-----Original Message-----
Hi Jerry Bodoff
I think your actually missing a couple of bits. Firstly
So internally the classes controlreturn the control name before you hook the class up!
Secondly you're trying to use thepointer (the variable you declare WithEvents) is null.
have no reference to it. Thiscontrols event handler class (your ButtonClass) when you
syntax. The only way of findingleads on to this problem you declare:
Dim ButtonClass as clsCmdButton
as a pointer to a class module, you then try to use it as a collection object:
ButtonIndex = ButtonClass.Item()
Even if ButtonClass was a collection, you can't use that
iterate the collection. Which isthe numeric index value of an item in a collection is to
value (the control name in thisnormally rather slow and pointless is you have it's key