TheSwamp

Code Red => VB(A) => Topic started by: Kheilmann on August 15, 2005, 09:55:36 AM

Title: Event procedures for Controls created at Run-time
Post by: Kheilmann on August 15, 2005, 09:55:36 AM
I have checkboxes created from Field Values in a DB.  When these items are checked or unchecked I need another form to Activate.  I tried adding a Checkbox_Click sub for each checkbox, but that does not work, at least for me.  It would not be a good idea anyway, but it was all I knew to do.  
I think my solution may have to do with CLasses or class modules, but I need to read up on those since I am not to familiar with anything about them...

So my first question is can you have event procedures with a run-time created control?
then, how do you do it?
Title: Event procedures for Controls created at Run-time
Post by: Keith™ on August 15, 2005, 10:43:34 AM
first answer ... yes you can have events with runtime created controls ....

second answer ... by subclassing the control

I'll dig up an example and post here, showing you how to do it ...
Title: Event procedures for Controls created at Run-time
Post by: Draftek on August 15, 2005, 11:10:12 AM
Since you are creating the control at run-time, you will have a variable for the control. You can simply use the "WithEvents" qualifier when you declare the control as:

Private WithEvents oCheck As CheckBox

You should then have the events show up in your pull-down menu in the vb ide.

Note: WithEvents must be used in a class module - Which is what your form code exists in anyway..
Title: Event procedures for Controls created at Run-time
Post by: Kheilmann on August 15, 2005, 04:00:17 PM
Thanks for everything....

I still have one problem, and I hope there is an easy way through it....

Dim Withevents cbox as msforms.checkbox  is what I had to use to get it to work.
If I used cbox as checkbox I received a Compile Error.  

Now Cbox shows up in my General Pulldown and all my events show up on the Events Pulldown.  The problem is that I use the cbox for all my checkboxes.

The Db where the names are for the checkboxes can be editted by the end user, so I can not use a var for each since I don't know how many will be there.

I really don't know what my options are, especially since I can not create an array with WithEvents.

I guess I could just create a var for each known checkbox, then whenever someone adds one, I can just modify the code to read that var also.

Surely...hopefully..there is a better way..
Title: Event procedures for Controls created at Run-time
Post by: Draftek on August 15, 2005, 04:18:29 PM
An array, oh that is a little different, but easier.

In order to dynamically use control arrays, you need to have the first one defined in your form in design mode and then use the Load statement to add as you need. Make sure you unload when finished.

Then you can do all your coding in the click event of the control using the Index passed to test which one was clicked.
Title: Event procedures for Controls created at Run-time
Post by: Kheilmann on August 15, 2005, 05:39:14 PM
I'm sorry, but...huh?

I apologize if I am missing something, but...
I was under the impression you could not use withevents with arrays...

are you proposing another way?  if so, I am lost.  

I have never used the load statement, and why do I need to have a checkbox already created?  I can do it I am just wondering what the purpose of it is...??

This is all new to me, so I apologize for the ignorance..
Title: Event procedures for Controls created at Run-time
Post by: Draftek on August 15, 2005, 07:42:21 PM
You don't NEED WithEvents or a variable to manipulate control arrays:

In order to use control arrays at run-time you need to do the following:
1. create a base control and give it an index of 0. You can turn visible off if there is an instance where it will not be needed on the form.
2. Using the event of your choice for that control write your code using the Index passed so you know which one was clicked or whatever.
3. In your code that adds the controls use the Load statement as:
Load MyControl(0 + n)
Make sure it's visible and in the right location.
 
You already have the code you need because of the original (Index of zero) control's event.

Here is a function I have used to add some controls and the size of the form depending on how many records I have:

Code: [Select]

Private Sub FlushRecordsetToForm()
    Dim I As Long
    Dim J As Long
    If Not rsEngAdds.BOF Then rsEngAdds.MoveFirst
    For I = 0 To rsEngAdds.Fields.count - 1
        If I <> 0 Then
            Load txtPercentage(I)
            txtPercentage(I).Visible = True
            txtPercentage(I).Top = txtPercentage(I - 1).Top + txtPercentage(I).Height + 150
            Load lblRange(I)
            lblRange(I).Visible = True
            lblRange(I).Top = txtPercentage(I).Top + 50
        End If
        lblRange(I).Caption = rsEngAdds.Fields(I).Name
        txtPercentage(I).Text = fCurrency(CStr(rsEngAdds.Fields(I).Value))
    Next I
    Me.Height = txtPercentage(txtPercentage.count - 1).Top + txtPercentage(0).Height + 1000
End Sub


Hope this helps.
Title: Event procedures for Controls created at Run-time
Post by: Kheilmann on August 16, 2005, 09:28:48 AM
Thanks for your help, but I still have no idea what I am doing.  
I really don't want to waste your time, so .....

I'll keep toying around with your posts until I eventually figure something out.

I think in the mean time I will just create a load of
Dim WithEvents Cbox## as msforms.checkbox
and hope there are enough variables to cover any additonal controls  someone may add to the DB tables.

Thanks again, I really do appreciate the help you have given to me.
Title: Event procedures for Controls created at Run-time
Post by: Draftek on August 16, 2005, 09:53:52 AM
I really wish you wouldn't do that.. Take a deep breath.

This is frustrating to you because it's a advanced for a beginnner but you can learn something useful for later with some patience.

What exactly are you trying to accomplish? I can't quite discern from your first post.

What does "I have checkboxes created from Field Values in a DB" actually mean? You have a form that you've added check boxes mimicking a row in a database table?
Title: Event procedures for Controls created at Run-time
Post by: Kheilmann on August 16, 2005, 11:09:23 AM
My "adding a load of variables" idea seems to be not such a great idea, man what was I thinking....

OK...
I have RoomNames that need values like CFMS, Grills, and Boots.
I have one Form with these 3 boxes (text and combo) to input the information from each room.  So whether I am entering data for Kitchen or Bedroom1 the same form loads to input the data.  That form loads whenever a Checkbox is clicked TRUE.
On my main UserForm there are no checkboxes.  They are all created at runtime.  
In the Activate Event of my UserForm VBA is creating Checkboxes for each roomname in one of my 5 Tables(Bed, Bath, Living, Recreation, and OPEN).  Whenever the Checkboxes are clicked TRUE, then a form to input data such as CFM, Grills, and Boots Activates and waits for input.

So every room in all five Tables is used to create a Checkbox on a particular Page.  But only the rooms clicked TRUE will activate the Room Data Userform.

I need each newly created CheckBox to have it's own Variable.
Title: Event procedures for Controls created at Run-time
Post by: Kheilmann on August 16, 2005, 11:12:04 AM
Here is what I have so far:
Code: [Select]

Option Explicit
Dim HVACdb As DAO.Database
Dim HVACrec As DAO.Recordset
Dim SQLquery As String
Dim SQLstr(0 To 4) As String
Dim Room As String
Dim WithEvents cbox As MSForms.CheckBox


Private Sub cbox_click()

If cbox.Value = True Then
    RoomData_UserForm.Show
End If


End Sub

Private Sub OK_CmdBtn_Click()

Me.Hide

End Sub

Private Sub UserForm_Activate()

Dim Tctrl As Control
Dim intTop As Integer
Dim X As Integer

SQLstr(0) = "Select Bed.RoomName From Bed;"
SQLstr(1) = "Select Bath_Utility.RoomName From Bath_Utility;"
SQLstr(2) = "Select Living.RoomName From Living;"
SQLstr(3) = "Select Recreation.RoomName From Recreation;"
SQLstr(4) = "Select Open.RoomName From Open;"

If Start.StrUser = "KEVIN" Then
    Set HVACdb = DAO.OpenDatabase("C:\akadsolution\Programs\AKS HVAC Design Package\Qbid.mdb")
ElseIf Start.StrUser = "JENNIFER" Then
    Set HVACdb = DAO.OpenDatabase("F:\akadsolution\Programs\AKS HVAC Design Package\Qbid.mdb")
Else:
    'Set HVACdb = DAO.OpenDatabase("\customerpath\Qbid.mdb")
End If

For X = 0 To MultiPage1.Pages.Count - 1
    intTop = 22
    SQLquery = SQLstr(X)
    Set HVACrec = HVACdb.OpenRecordset(SQLquery, dbOpenDynaset)
   
    If HVACrec.RecordCount > 0 Then
        HVACrec.MoveFirst
            Do Until HVACrec.EOF
                Room = HVACrec.Fields("RoomName").Value
                Set cbox = MultiPage1.Pages(X).Controls.Add("Forms.Checkbox.1", Room & "_ChkBox", Visible)
                    With cbox
                        .Left = 6
                        .Height = 15
                        .Top = intTop
                        .Width = 75
                        .Caption = Room
                        .Enabled = True
                        .Value = False
                        .WordWrap = False
                    End With
                intTop = intTop + 22
                HVACrec.MoveNext
            Loop
    End If
Next X
           
HVACrec.Close
Set HVACrec = Nothing
HVACdb.Close
Set HVACdb = Nothing
SQLquery = ""
Room = ""
   
End Sub


[/code]
Title: Event procedures for Controls created at Run-time
Post by: Keith™ on August 16, 2005, 11:35:02 AM
One of the issues you may be experiencing is that with MSForms2 there isn't an index option in VBA, however with a Check control in VB there is.
Title: Event procedures for Controls created at Run-time
Post by: Draftek on August 16, 2005, 11:51:39 AM
ahh! I see. As Keith pointed out, vba does not allow control arrays.

Sorry, I should have asked that first, I just assumed you were using VB.

Hang on, I think I have something that may work..
Title: Event procedures for Controls created at Run-time
Post by: Keith™ on August 16, 2005, 01:20:47 PM
Quote from: Draftek
... vba does not allow control arrays.


Actually VBA does allow control arrays by way of groups, and you must specify the group to put your CheckBox control into for it to be part of a group.

In runtime, it is a subclass that is required for VBA
Title: Event procedures for Controls created at Run-time
Post by: Kheilmann on August 16, 2005, 01:35:21 PM
Does anyone have any examples for putting controls into groups
and
anything about subclasses?

That would probably help me out a lot....
Title: Event procedures for Controls created at Run-time
Post by: Keith™ on August 16, 2005, 01:49:48 PM
Am I to presume you are creating this in AutoCAD or is it in some other environment
Title: Event procedures for Controls created at Run-time
Post by: Kheilmann on August 16, 2005, 02:04:27 PM
It is in Excel, although I am not using any Excel functions.
Everything is 100% VBA (except for my *.mdb files)  But since you don't need Access to access them, I don't count Access as being part of this either.
Title: Event procedures for Controls created at Run-time
Post by: Draftek on August 16, 2005, 02:12:45 PM
I'd like to see a group example myself, Keith. I didn't know you could use that for a control array.

In the meantime here is how I would do it without getting too complex:
1. Create a class that uses the checkbox variable using withevents.
2. push the click event inside the class
3. Create a collection class to manipulate (add, delete) your controls as they are added, etc.
4. In your form code, add as you need.
5. If you need specific control (say to sync the database with the particular row your clicking) add a property to the class to keep track of which one is being clicked. I like the tab property myself.

I threw together a quick demo;

The cCheck class:
Code: [Select]

' Class cCheck
' Class to encapsulate the check box events
Option Explicit

Private WithEvents mvarmCheck As CheckBox 'local copy
Public Property Set mCheck(ByVal vData As CheckBox)
'used when assigning an Object to the property, on the left side of a Set statement.
'Syntax: Set x.mCheck = Form1
    Set mvarmCheck = vData
End Property

Public Property Get mCheck() As CheckBox
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.mCheck
    Set mCheck = mvarmCheck
End Property

' Here is where you do your stuff
Private Sub mvarmCheck_Click()
    MsgBox "The Click Event Inside the Class Module"
End Sub


The collection class (just a class):
cChecks
Code: [Select]

' Collection Class: cChecks
' used to hold the cCheck objects
Option Explicit

'local variable to hold collection
Private mCol As Collection

Public Function Add(mCheck As CheckBox, Optional sKey As String) As cCheck
    'create a new object
    Dim objNewMember As cCheck
    Set objNewMember = New cCheck
    'set the properties passed into the method
    If IsObject(mCheck) Then
        Set objNewMember.mCheck = mCheck
    Else
        objNewMember.mCheck = mCheck
    End If
    If Len(sKey) = 0 Then
        mCol.Add objNewMember
    Else
        mCol.Add objNewMember, sKey
    End If
    'return the object created
    Set Add = objNewMember
    Set objNewMember = Nothing
End Function

Public Property Get Item(vntIndexKey As Variant) As cCheck
    'used when referencing an element in the collection
    'vntIndexKey contains either the Index or Key to the collection,
    'this is why it is declared as a Variant
    'Syntax: Set foo = x.Item(xyz) or Set foo = x.Item(5)
  Set Item = mCol(vntIndexKey)
End Property

Public Property Get Count() As Long
    'used when retrieving the number of elements in the
    'collection. Syntax: Debug.Print x.Count
    Count = mCol.Count
End Property

Public Sub Remove(vntIndexKey As Variant)
    'used when removing an element from the collection
    'vntIndexKey contains either the Index or Key, which is why
    'it is declared as a Variant
    'Syntax: x.Remove(xyz)
    mCol.Remove vntIndexKey
End Sub


Public Property Get NewEnum() As IUnknown
    'this property allows you to enumerate
    'this collection with the For...Each syntax
    Set NewEnum = mCol.[_NewEnum]
End Property


Private Sub Class_Initialize()
    'creates the collection when this class is created
    Set mCol = New Collection
End Sub


Private Sub Class_Terminate()
    'destroys collection when this class is terminated
    Set mCol = Nothing
End Sub


The user form code:
Code: [Select]

' UserForm1
Option Explicit
' NOTE: Scope is important
Private ochecks As cChecks
Private oCheck As cCheck

' add the checkboxes and push to the collection class
Private Sub UserForm_Activate()
    Set ochecks = New cChecks
    Dim oChk As CheckBox
    Set oChk = Me.Controls.Add("Forms.Checkbox.1", "MyTestCheck", True)
    Call ochecks.Add(oChk, "1")
    Set oChk = Me.Controls.Add("Forms.Checkbox.1", "MyTestCheck1", True)
    oChk.top = 50
    Call ochecks.Add(oChk, "2")
End Sub


Lotta code, but not rocket science and if you have visual basic, it will code most of the collection class for you.
Hope that helps..
Title: Event procedures for Controls created at Run-time
Post by: Keith™ on August 16, 2005, 02:20:48 PM
In VBA, you can set the GroupName property of a control to make it part of a group.
Title: Event procedures for Controls created at Run-time
Post by: Draftek on August 16, 2005, 02:28:11 PM
hmm. I see.

I've used that to seperate different groups of option buttons in the same container.

But I've never seen it used to create a control array.
Title: Event procedures for Controls created at Run-time
Post by: Keith™ on August 16, 2005, 02:43:14 PM
Well, it doesn't really create an array per'se but it allows you to segregate the controls so you can more easily identify them as being part of a specific group, particularly if you have multiple controls where the name is unknown at runtime ..

When the control is created, you can assign it to a group via groupname (although it does not act like a group as does an option button) it will allow you to verify the group (or array, if you will) that it belongs to ...

For example in a bonefide array, you have controls named such as CheckBox1(0) CheckBox1(1) ... etc ...

In a group you have different names but the group will identify which collection of controls a specific controls belongs to.
I.e. all CheckBox controls could be in Group1 Group2 etc .. thus you could enumerate all controls and act upon the remaining in that group based upon the group name.
Title: Event procedures for Controls created at Run-time
Post by: Draftek on August 16, 2005, 03:04:40 PM
Keith, if you have an example of how that would work to create a single function for an event - like the purpose for a control array, I'd like to see it. That mignt come in handy.

kheilmann: It just dawned on me, you don't have to create a collection class. To make it easier just add a property (Index) to the cCheck class and manipulate it with either an array of cCheck or a simple collection. That would cut down quite a bit of code. You won't need cChecks at all.

Remember to use Redim Preserve when adding to the array.

If you need some help with that just post.