TheSwamp
Code Red => VB(A) => Topic started 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?
-
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 ...
-
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..
-
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..
-
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.
-
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..
-
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:
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.
-
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.
-
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?
-
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.
-
Here is what I have so far:
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]
-
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.
-
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..
-
... 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
-
Does anyone have any examples for putting controls into groups
and
anything about subclasses?
That would probably help me out a lot....
-
Am I to presume you are creating this in AutoCAD or is it in some other environment
-
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.
-
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:
' 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
' 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:
' 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..
-
In VBA, you can set the GroupName property of a control to make it part of a group.
-
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.
-
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.
-
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.