Create Userform on the Fly?

PA HS Teacher · Jul 18, 2006

Is it possible to create a userform on the fly?

I know how to create controls on the fly.
e.g.
Set ctl = MyForm.Controls.Add("forms.CommandButton.1")

But what if I wanted to create MyForm on the fly?

PA HS Teacher · Aug 14, 2006

My latest Atempts: http://www.box.net/public/32fnibnthk
Hi Tom,
I've been spending a lot of time going over your code, and reading through a Visual Basic Objects book. Each time I come back I find that I am understanding more. I am thoroughly enjoying this process, even if I a little slow.

The modification's I've made are mostly restricted to the 'piece' and 'use' classes. I've managed to make some properties implicitly/passively/privately? not sure the terminology. I've tried doing this with the ID property for the 'Piece' and 'Use' objects. (I'm pretty sure 'Use' will be the lowest object.) I concatenated the Piece.Section property (Not to be confused with the 'Section' object) with the Piece.Day property to form a unique Piece.ID.

I seem to be able to Add use object this way, but I have not had luck getting information back out of the use object. Perhaps you could take a look at my modification. I am receiving an error at the last line of Example1(). Thank you for sticking with this rather long post.

~ Dan

'longhanded method of creating your objects
Sub Example1()
 Set cps = New CoursePlanningSystem

 Dim c As Course, u As Unit, s As Section, p As Piece, us As Use

 If Not cps.CourseExists("Course 1") Then
 Set c = cps.AddCourse("Course 1", "Course 1 Description")
 End If

 'Course 1, Unit 1, Section 1, Piece 1, Use 1
 If Not c.UnitExists("Unit 1") Then
 Set u = c.AddUnit("Unit 1", "Unit 1 Description", "Comments", 1)
 End If

 If Not u.SectionExists("Section 1") Then
 Set s = u.AddSection("Section 1", "Line", True, True, True)
 End If

 If Not s.PieceExists("Piece 1") Then
 ' Set p = s.AddPiece("Text", True, Now, Now, #6/7/2006#, Date)
 Set p = s.AddPiece("Text", False)
 End If

 If Not p.UseExists("Use 1") Then
 ' Set us = p.AddUse("Use 1", #6/7/2006#, "Section", "Term", 1)
 Set us = p.AddUse(Int(Now), "First Period", 1)
 End If


 Debug.Print cps(1).ID
 Debug.Print cps(1)(1).ID
 Debug.Print cps(1)(1)(1).ID
 Debug.Print cps(1)(1)(1)(1).DateCreated
 Debug.Print cps(1)(1)(1)(1)(1)(1).ID ' Why does this line error out?
 ' Object doesn't support this property or method
 ' I think it is related to my attempt to combine date
 ' and section into a Unique ID


End Sub

Here is my modified 'Use' class

Option Explicit

Private pDay As Date ' Date of Lesson where this Piece was used
Private pSection As String ' Section or Period of class where this piece was used
Private pOrder As Integer ' Order of this piece within the Unit.Section (e.g. 1, 2, 3)
Private pID As String ' Unique ID for This Piece, specified by combining Use.Section & Use.Day
Private pUseComments As String ' Comments about this particular use of the Piece
Private pUseText As String ' Generally this is the same as Piece.Text, but can vary

Event RefreshUsesCollection(Instance As Use, OldTitle As String, NewTitle As String)
 ' ID
Private Property Let ID(vID As String)
 RaiseEvent RefreshUsesCollection(Me, pID, vID)
 pID = vID
 ' MsgBox "vID = " & vID
 ' MsgBox "ID = " & pID
End Property
Public Property Get ID() As String
 ' MsgBox "Getting Use ID"
 ' MsgBox ("pID = ") & pID
 ID = pID
End Property
 ' Day
Public Property Let Day(vDay As Date)
 pDay = vDay
 ID = UseID
End Property
Public Property Get Day() As Date
 Day = pDay
End Property

 ' Section
Public Property Let Section(vSection As String)
 pSection = vSection
 ID = UseID
End Property
Public Property Get Section() As String
 Section = pSection
End Property

 ' Order
Public Property Let Order(vOrder As Integer)
 pOrder = vOrder
End Property
Public Property Get Order() As Integer
 Day = pOrder
End Property

 ' UseComments
Public Property Let UseComments(vUseComments As Integer)
 pUseComments = vUseComments
End Property
Public Property Get UseComments() As Integer
 Day = pUseComments
End Property

 ' UseText
Public Property Let UseText(vUseText As Integer)
 pUseText = vUseText
End Property
Public Property Get UseText() As Integer
 Day = pUseText
End Property

Private Function UseID() As String
 UseID = pSection & Format(pDay, "0")
End Function

Function NewEnum() As IUnknown
 Set NewEnum = pUseCollection.[_NewEnum]
End Function


And my modifcations to the 'Piece' class
Option Explicit

Private pUseCollection As Collection
Private WithEvents pUse As Use

Private pID As String
Private pText As String
Private pRetired As Boolean
Private pDateOfLastUse As Date
Private pDateCreated As Date
Private pDateLastModified As Date

Event RefreshPiecesCollection(Instance As Piece, OldTitle As String, NewTitle As String)

 ' ID
Public Property Let ID(vID As String) 'I'd rather this be private, but not sure how to assign Unique ID at runtime?
 RaiseEvent RefreshPiecesCollection(Me, pID, vID)
 pID = vID
End Property
Public Property Get ID() As String
 ID = pID
End Property

 ' Txt
Public Property Let Text(vText As String)
 If pText <> vText Then DateLastModified = Now
 pText = vText
End Property
Public Property Get Text() As String
 Text = pText
End Property

 ' Retired
Public Property Let Retired(vRetired As Boolean)
 pRetired = vRetired
End Property
Public Property Get Retired() As Boolean
 Retired = pRetired
End Property

 ' LastUse
' I will come back to this later, Perhaps will set value of this property from use object?
' Not critical to the initial function of this project
Public Property Let DateOfLastUse(vDateOfLastUse As Date)
 pDateOfLastUse = vDateOfLastUse
End Property
Public Property Get DateOfLastUse() As Date
 DateOfLastUse = pDateOfLastUse
End Property

 ' Created
Private Property Let DateCreated(vDateCreated As Date)
 pDateCreated = vDateCreated
End Property
Public Property Get DateCreated() As Date
 DateCreated = pDateCreated
End Property

 ' LastModified
Private Property Let DateLastModified(vDateLastModified As Date)
 pDateLastModified = vDateLastModified
End Property
Public Property Get DateLastModified() As Date
 DateLastModified = pDateLastModified
End Property

Public Property Get Uses(Index As Variant) As Use
 'you will need to validate the index before using it

 Set Uses = pUseCollection(Index)
 Set pUse = Uses
 ' MsgBox "hi"
End Property

'read only count of Use objects
Public Property Get UsesCount() As Integer

 UsesCount = pUseCollection.Count

End Property
 ' Add Use
Public Function AddUse(Day As Date, Section As String, _
 Order As Integer) As Use

 If Not UseExists(ID) Then
 Dim c As New Use
 c.Day = Day
 c.Section = Section
 c.Order = Order
 pUseCollection.Add c, c.ID
 Set AddUse = c
 Set pUse = c
 Else
 Err.Raise 457, , "This key is already associated with an element of this collection" & vbCr & "The Use, """ & ID & """, already exists..."
 End If

End Function

Public Function RemoveUse(ID As String) As Boolean
 If UseExists(ID) Then
 pUseCollection.Remove ID
 End If

End Function

Public Function UseExists(Title As String) As Boolean
 On Error Resume Next

 Dim c As New Use
 Set c = pUseCollection(Title)
 If Err.Number <> 0 Then Exit Function
 UseExists = True

End Function

Private Sub Class_Initialize()
 Set pUseCollection = New Collection
 DateCreated = Int(Now)
End Sub

Private Sub pUse_RefreshUsesCollection(Instance As Use, OldTitle As String, NewTitle As String)

 Dim SaveIndex As Integer

 For SaveIndex = 1 To pUseCollection.Count
 If pUseCollection(SaveIndex).ID = OldTitle Then Exit For
 Next

 pUseCollection.Add Instance, NewTitle, SaveIndex
 pUseCollection.Remove SaveIndex

 Set pUse = Nothing

End Sub

Function NewEnum() As IUnknown
 Set NewEnum = pUseCollection.[_NewEnum]
End Function

Legacy 98055 · Aug 15, 2006

You should download the workbook and replace your current book or import the classes into your current workbook after exporting the classes that are there now. Some of the editing in these classes was done in notepad. Your code will not work if you simply copy and paste from this post.

Course_Planning_System_8-15-06.zip

I changed quite a bit in your piece object

'removed from your use object. Not needed.
Function NewEnum() As IUnknown
Set NewEnum = pUseCollection.[_NewEnum]
End Function

"Order" belongs to the "piece" object. Correct? My question is, can muliple/differing "use" objects have the same order number assigned and still belong to the same parent "piece" object? If not, we will need to enforce unique order assignments for each use object. Think this through and decide if order belongs to a piece object or a use object.

Using "Section" is really confusing in your add routine above and in your use objects. Too me anyway. It is the same name as one of your other classes. Can we rename it to "period"?

Your piece object. How do you want to id your piece objects? You have to keep in mind that there are only two ways for you to directly access an object within your code. An index number or an index string. The string would be the "friendly" id of each object. What's your thoughts on this?

' ID
Public Property Let ID(vID As String) 'I'd rather this be private, but not sure how to assign Unique ID at runtime?
RaiseEvent RefreshPiecesCollection(Me, pID, vID)
pID = vID
End Property
Public Property Get ID() As String
ID = pID
End Property

Add support for Day As Date, Period As String syntax. See example code...

Debug.Print cps(1)(1)(1)(1)(1)(1).ID ' Why does this line error out?

You were going six deep.
Should be five:

Debug.Print cps(1)(1)(1)(1)(1).ID

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Option Explicit

 Dim cps As CoursePlanningSystem

 'longhanded method of creating your objects
 Sub Example1()
 Set cps = New CoursePlanningSystem

 Dim c As Course, u As Unit, s As Section, p As Piece, us As Use

 If Not cps.CourseExists("Course 1") Then
 Set c = cps.AddCourse("Course 1", "Course 1 Description")
 End If

 If Not c.UnitExists("Unit 1") Then
 Set u = c.AddUnit("Unit 1", "Unit 1 Description", "Comments", 1)
 End If

 If Not u.SectionExists("Section 1") Then
 Set s = u.AddSection("Section 1", "Line", True, True, True)
 End If

 'i am unsure as to exactly how you wish to id your piece objects
 If Not s.PieceExists("Piece 1") Then
 ' Set p = s.AddPiece("Text", True, Now, Now, #6/7/2006#, Date)
 Set p = s.AddPiece("Some Text for Piece 1", False)
 End If

 'note that this is now different
 'adding four use objects
 If Not p.UseExists(#6/7/2006#, "Period 1") Then
 Set us = p.AddUse(#6/7/2006#, "Period 1", 1)
 End If

 If Not p.UseExists(#6/7/2006#, "Period 2") Then
 Set us = p.AddUse(#6/7/2006#, "Period 2", 1)
 End If

 If Not p.UseExists(#6/7/2006#, "Period 3") Then
 Set us = p.AddUse(#6/7/2006#, "Period 3", 1)
 End If

 If Not p.UseExists(#6/7/2006#, "Period 4") Then
 Set us = p.AddUse(#6/7/2006#, "Period 4", 1)
 End If



 Debug.Print cps(1).Id
 Debug.Print cps(1)(1).Id
 Debug.Print cps(1)(1)(1).Id
 Debug.Print cps(1)(1)(1)(1).DateCreated
 Debug.Print cps(1)(1)(1)(1)(1).Id
 Debug.Print cps(1)(1)(1)(1)(2).Id
 Debug.Print cps(1)(1)(1)(1)(3).Id
 Debug.Print cps(1)(1)(1)(1)(4).Id
 Debug.Print

 'For Each UseObject in "Piece 1"
 For Each us In p
 Debug.Print us.Id
 Next
 Debug.Print

 'also supports the Date, Period syntax as the default property
 'there are three (actually six) syntax variants to get a reference to a use object
 Set us = p(3)
 'or same thing as
 Set us = p(#6/7/2006#, "Period 3")
 'or same thing as
 Set us = p(CDbl(#6/7/2006#) & "Period 3")

 'the above are all equivalent to:
 Set us = p.Uses(3)
 'or same thing as
 Set us = p.Uses(#6/7/2006#, "Period 3")
 'or same thing as
 Set us = p.Uses(CDbl(#6/7/2006#) & "Period 3")

 'using the newly added "GetObject" shortcut function located in your top level object,
 'you could grab a reference to a distant use object like this
 'note that you will be responsible for concatenating when you use this function

 Set us = cps.GetObject(CDbl(#6/7/2006#) & "Period 1", "Use")
 'or (a bit slower)
 Set us = cps.GetObject(CDbl(#6/7/2006#) & "Period 1")

 'examples
 Debug.Print cps.GetObject(CDbl(#6/7/2006#) & "Period 1", "Use").Id
 Debug.Print cps.GetObject(CDbl(#6/7/2006#) & "Period 2", "Use").Period
 Debug.Print cps.GetObject(CDbl(#6/7/2006#) & "Period 3", "Use").Day
 Debug.Print cps.GetObject(CDbl(#6/7/2006#) & "Period 4", "Use").Id
 Debug.Print

 'to get a piece object named "Piece 1"
 Set p = cps.GetObject("Piece 1")
 'or (a little faster)
 Debug.Print cps.GetObject("Piece 1", "Piece").Text
 End Sub
</td></tr></table>

This is the only change I made in the CoursePlannigSystem class. The code is redundant but is faster than using recursion with a generic object type.

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Function GetObject(Id As String, Optional ClassName As String) As Object
 On Error Resume Next

 Dim c As Course, u As Unit, s As Section, p As Piece, Uses As Use

 Select Case LCase(ClassName)
 Case "course"
 Set GetObject = Me.Courses(Id)
 Exit Function
 Case "unit"
 For Each c In Me
 Set GetObject = c.Units(Id)
 Exit Function
 Next

 Case "section"
 For Each c In Me
 For Each u In c
 Set GetObject = u.Sections(Id)
 Exit Function
 Next
 Next

 Case "piece"
 For Each c In Me
 For Each u In c
 For Each s In u
 Set GetObject = s.Pieces(Id)
 Exit Function
 Next
 Next
 Next

 Case "use"
 For Each c In Me
 For Each u In c
 For Each s In u
 For Each p In s
 Set GetObject = p.Uses(Id)
 Exit Function
 Next
 Next
 Next
 Next

 Case Else
 For Each c In Me
 If c.Id = Id Then
 Set GetObject = c
 Exit Function
 End If
 For Each u In c
 If u.Id = Id Then
 Set GetObject = u
 Exit Function
 End If
 For Each s In u
 If s.Id = Id Then
 Set GetObject = s
 Exit Function
 End If
 For Each p In s
 If p.Id = Id Then
 Set GetObject = p
 Exit Function
 End If
 For Each Uses In p
 If Uses.Id = Id Then
 Set GetObject = Uses
 Exit Function
 End If
 Next
 Next
 Next
 Next
 Next

 End Select

 End Function
</td></tr></table>

PA HS Teacher · Aug 15, 2006

Hi Tom,
[Edit: I initially wrote this this morning, and I've re-written much of this response, including more examples. 3:07 PM EDT]

I always download the workbook. I find it much safter that way. You really do understand this project well.

"Order" belongs to the "piece" object. Correct?

(I believe it should be in the Use Object)
Order refers to the Order of the Piece within a Section of a Lesson Plan, but it differs for each use. That is I may choose to reorder the pieces for a different Period, or on a different day.

Here is an Example, please let me know if I am need to think about this differently.

Lesson Plan for Period 1 might contain:
Unit: Unit 1
Section: Homework
1. Read Page 5 <-- I still don't have a good ID for Piece Objects
2. Do Problem 1

Lesson Plan for Period 2 might contain:
Unit: Unit 1
Section: Homework
1. Do Problem 1 <-- I still don't have a good ID for Piece Objects
2. Read Page 5

Units("Unit 1").Sections("Homework").Pieces("Read Page 5").Uses("Period 1 August 15 2006").Order = 1

Where as:

Units("Unit 1").Sections("Homework").Pieces("Read Page 5").Uses("Period 2 August 15 2006").Order = 2

The Order Property of Use will tell the Lesson Plan Object (See 2 paragraphs below), What order to place the Parent Piece Object of the use within the Section for that particular use. When we get to the stage where I am viewing Lessons, For my First Period class above, I would like "Read Pg 5" to be the first Piece listed under my Homework Section. In my Second period class, I would like "Read Pg 5" to be the 2nd Piece Listed under my Homework Section.

can muliple/differing "use" objects have the same order

Yes. The order is the order that a particular Piece is used within a Section of a Lesson Plan. (I will worry about an object/ way to work with the existing object structure for creating and editing the plans after we finish hammering out this one.) Since there will be a separate Lesson Plan for each Period and Day, the order of each use of a piece is unrelated. However, EachPiece in the Section of the Lesson Plan must have a different number. The Order number for each use object is often not unique within the Piece Object. E.g. I will often Assign "Read Pg 5" as teh First Homework for all my Periods.

As I'm thinking about it, A Lesson Plan Object, would be equivalent to an instance of a Unit Object + some additional information. The instance of the Unit Would only contain (or at least only show) The Uses for particular Period, Day combination.) The object model we are creating is a way to break down a Lesson Plan into fundamental blocks, and I am thrilled about how well it is going. I'll have to think about whether a Lesson Plan Object will contain a new Instance of a Unit Object (including Sections and Pieces for which their is a use on the Period and Day of the Lesson, Or [And this seems intuitively better to me, if possible] The actual Instance of Unit, this way as a Lesson Plan is edited, the Section, Pieces, Uses are edited accordingly. The nonrelevant information could just be excluded from whatever Form we are using to view the Lesson Plan.

Can we rename it [Section of the Piece Object] to "period"?

Absolutely I was getting uncomfortable with the ambiguity. I'm glad you called it out. We should change it to Period

How do you want to id your piece objects

I'd actually like to simply name them with their Index number, or something closely related. When created, the first Piece ID would be 1, or Piece 1. When the second is created it would be named 2, or Piece 2 etc. Even though Pieces will have a particular order within a Section of a Lesson Plan, It's nice to be able to refer to them by an Absolute Number that does not change from Lesson Plan to Lesson Plan. I just wasn't sure how to go about this from within the Piece object itself, so we could keep ID private.

I can't thank you enough Tom. I feel like I'm solidifying a lot of Objects in my Head, and we're really making some progress. I have training at my new school today, so I will take a closer look at your recent code later this afternoon.

~ Dan

PA HS Teacher · Aug 15, 2006

That GetObject function is great!

Although I have to be careful about using it, because while the ID's are unique within a particular instance of an object, they are not unique between multiple instances of an object. For example, each Day, Period ID for uses of one Piece will be the same as the Day, Period ID for other another pieces, because I will use more than one Piece in a Lesson Plan.

That Said, a giant Lightbulb has gone off in my head. I can modify your GetObject Function slightly, so that it returns a collection of Uses (or any level object really) That contains only uses for a particular day, Period Combination.

Which brings me to a question I've been wondering for a couple weeks now. From the child object, is it possible to ascertain the parent object? e.g., If I used a modified GetObject Function and extracted all the relevant Uses, could I then pull the other information for the lesson plan in a fashion simlar to.

Set MyPiece = Uses.Parent
Set MySection = Uses.Parent.Parent
Set MyUnit = Uses.Parent.Parent.Parent

If it is possible, displaying and editing a Lesson Plan seems possible without the need for any additional objects, though I might add a collection of LessonInfo objects to the Unit object, each member of the collection would be unique by Day, Period combination. This would be where I could hold miscellaneous information about the Lesson such as my Predictions, General Comments about the Lesson, Length of the Period (it varies sometimes) etc.

Similarly, Generating Reports would be quite straighforward. These objects are powerful stuff! I'm not sure yet how to tie/bind The object model to a UserForm(s), or to the Data itself yet but I quite confident that you will have more good advice about that when we get there. Thank you for taking the time to read through all this, and for putting up with my steep learning curve. As always take your time. Your advice is always worth the wait.

Legacy 98055 · Aug 16, 2006

It would be no problem to create a parent property in your objects since they are all, except your top level object, created from within other objects. You would simply pass a reference within the Add method. See the attached which contains these changes and the example is module1 that proves that it's working.

Course_Planning_System_8-16-06.zip

Debug.Print TypeName(c.Parent)
Debug.Print TypeName(u.Parent)
Debug.Print TypeName(s.Parent)
Debug.Print TypeName(p.Parent)
Debug.Print TypeName(us.Parent)

In each add method
Set c.Parent = Me

and added this property and private variable in each applicable class.

Private pParentReference As ParentObjectType

Public Property Get Parent() As ParentObjectType
Set Parent = pParentReference
End Property

Public Property Set Parent(ByVal ParentReference As ParentObjectType)
If pParentReference Is Nothing Then Set pParentReference = ParentReference
End Property

We are at a point, and really well past the point, where it would be very helpful to see a more extensive example of the end user interface. The getobject function is proof that I am still not grasping everything here and I am afraid that I might be overcomplicating things by not really understanding thoroughly. You have posted some examples which have been very helpful, but when you get a chance, please post a more detailed picture of what this needs to do. The focus being on "what" not "how". Binding data. There are a multitude of ways to store, retrieve, and manipulate data. Before this is said and done, your data will likely end up in a simple mdb file. In the end, it will not matter because all of your data access will be wrapped within your objects and you, the end developer (and user), will not care where the data is. Such is the case when you "query" a range object.

MyVar = Range("A1").Value

You do not know where "Value" is actually located, how it got there, or how the software is going to go about retrieving it. A properly developed class is something that you should be able to "forget" about. That is the beauty of object based development though it might seem like a lot of work in the interim.

The getobject function. You need to decide if you will enforce referential integrity within your child objects. I see now that this is not feasible in your use objects. Remember that to return a collection of Use objects all we need to do is get a reference to it's parent piece object. If the id's will be unique down the chain to piece objects, we will still have a decent shortcut to access distant objects.

I really need a visual. I still do not know the best way of accomplishing "how" because i do not understand "what".

To add. Your use objects, though in and of themselves may contain duplicate id's, note that if you can have use objects with duplicate id's located within the same piece object, our design is faulty. If the current setup is correct and you will not have duplicate use objects within an piece parent object, then we can simply add an optional argument to the getobject function. It's redundant to return collections because the parent already contains a collection.

Also, we are not stuck in a class chain gang. Your lesson object can be created independantly and constructed using existing objects.

PA HS Teacher · Aug 16, 2006

http://www.box.net/public/pthnncf8eh
[Edit] Posted Wrong Version of File at first. Fixed Link Above 9:33 PM EDT

Tom,
Thanks for such a speedy response. I put together a file that I hope illustrates more clearly, and more concretely, how I want my end user to interact/interface the the CPS. Let me know if any of the Educational Jargon confuses the code.

Here is a file where I've "added" my Courses, Periods, and their Units. (At least I've put them into a menu) I've put in some submenus of the Units to illustrate some of the types of things I'd like the user to be able to do. The primary concern is to write/edit Lesson Plans and View Them. The file also conatins (for example purposes) hard coded user form for Editing Lesson Plans, and a hardcoded Userform for Viewing a Single lesson plan for a particular (Course, Unit, Period, Day) combination.

I've also put the following comments/instruction in a textbox in the file.

1. Press Button to Add CPS to your toolbar.
(I'm fairly new at menus, but I didn't want to force menus on you. The code to add the Menus is in Module 1)

2. Pick a course, pick a period (it didn't quite add periods the way I wanted, but I think you get the idea)

3. Pick a Unit

4. View/Edit Lesson Plans

5. Daily View
This is the only button that currently runs anything. It calls up the LessonPlanEditForm
It will pull up a Form for Editing a Lesson Plan.
I just hard coded this form to show you what it might look like. There is a multipage, one page for each Section of the Unit. On the page is a list of Pieces with checkboxes. If the Checkbox is true, you would be able to see Textboxes for editing the use object. If it is false, you would just see the Piece.Text and Piece.ID.
I've only hard coded the Objectives Tab, but the other would work exactly the same way.

The combobox contains the order property of the Use object. Changing this number should move the Piece, and its child Use.Text, Use.UseComments, and Use.Order controls up or down. Use.Order is used to sequence the presentation of Pieces on the Section Tab of the Multipage.

6. Press the Daily View button on the LessonPlanEditForm will pull up the DailyViewLPForm.
This form represents What an actual lesson Plan will look like. It contains exactly the same data as the LessonPlanEditForm, but would be all a label, or labels, formatted to present each Section in a particular Order defined by the user. I'll also add an export to word/ textfile/rtf button so it could be readily printed.

7. Press the Remove button to remove the CPS Menu.

The Adding/ Modifying of the Lesson Plans is the critical feature of this project. The Weekly/Monthly view, Reports, and any other features I can think of are secondary. (Though if we've developed our system properly, I think these other tasks will be relatively straightforward.

Does this help clarify my intentions?

if you will enforce referential integrity within your child objects

If I understand the concept, I don't think this is feasible. I could easily have two courses with the Same Unit ID, or two Sections with the same Piece ID etc. I'm hoping the menu structure above helps with this question.

note that if you can have use objects with duplicate id's located within the same piece object, our design is faulty.

All Uses of the same Piece object are unique by Period, Day. Put another way, each Piece can only be used once in a Lesson Plan, and since Each Lesson Plan is Unique by Period, Day, Use Objects can never have duplicate ID's within the same Piece Object.

I am amazed at how well you've grasped the project without something like this. If there is anything else I can do to help illustrate, explain, articulate, elaborate, describe in anyway help show the end goal, let me know.

As always, thank you so much for your time, and your efforts.

~ Dan

Legacy 98055 · Aug 27, 2006

Cool Dan! I love the way your menus are setup. Let me see if this moves me along with you by asking you a few questions...

The userform it'self represents a unit. Correct?
Each tab in the multipage represents a section. Correct?
If this is correct, another question... Does the section object possess the properties to include all of these different types that are represent by each tab?

Objective.
State Standard.
Ect...

Your custom objects are types or data containers. Will the section object be able to contain all of these different sections? If not, no big deal. you can create an Objective object, and a State Standard object, and on and on if you need to or add the necessary properties to accommodate your sections. Just wondering.

If I am grasping the above, the Unit object need to have a method such as "CreateGUI" which would populate all of your controls with child object's properties.

The hierarchy has appeared to change a bit as well. It seems that it is no longer Course>Unit>Section>ect but Course>Period>Unit>Section>ect. Is this correct? We need to decide if a Unit belongs to a period or a period is a property of the Unit object. I would make the period a property of the Unit object and then provide a method in the Course object such as...
Public Function UnitsByPeriod(Period as String) as Unit()
or
Public Function UnitsByPeriod(Period as String) as PeriodUnits
to return a collection or array of Unit objects by passing a period as an argument. I suppose you may need to pass additional information such as a date. Please explain this one too me as I could be missing it here. If there is anything else I have missed, please say on.

PA HS Teacher · Aug 28, 2006

(I just lost 30 minutes of my post because my thumb accidentally hit something on my wife's laptop, I hate when I do that. I should have saved it in word first. Let me Try again)

The userform it'self represents a unit. Correct?

Yes. Though I tried to show how I might Edit a Lesson Plan with this userform. (I evision a Lesson Plan being the subset of a 'Unit' that represents all of the 'Pieces' or 'Uses' that are unique by day, Period.)

Each tab in the multipage represents a section. Correct?

Exactly!

Does the section object possess the properties to include all of these different types that are represent by each tab?

For 90% of them yes. I thought it better to use a generic 'Section' object. Which could permit me to add 'sections' quickly/easily, and even at runtime. Though I thought I might end up adding some more specific objects later and adding these objects as a property of the 'Unit' object. (Does this seem reasonable?)

If I am grasping the above, the Unit object need to have a method such as "CreateGUI" which would populate all of your controls with child object's properties.

Sounds like exactly what I'm after.

The hierarchy has appeared to change a bit as well. It seems that it is no longer Course>Unit>Section>ect but Course>Period>Unit>Section>ect.

I don't think so. Since Each 'Use' object is unique by Period, Day combination.

You sparked some ideas in my head. (I think they may be good ones, but I was hoping you could tell me)

Could the Viewing of a Lesson Plan simply be a "CreateGUI" method of the 'Unit' object that accepts Period and Day arguments, so that it populates a form with only 'Use' Objects with the correct Period, Day Combination? Using the parent property of the objects to get the any necessary information on the parent Pieces, and Sections to display the lesson plan. That is a Lesson Plan is simply a way of displaying only the relevant parts of a 'Unit' object that are relevant to a lesson.

Aside:
I thought we might use the parent property of the 'Use' object to get the 'Piece' object, and the parent property of the 'Piece' object to get the section object. In fact, perhaps we could add a (sLevel as string) argument to the parent method. e.g. use.Parent("Piece") would return the parent piece object, use.Parent("Section") would return the section object etc.

BTW,
For a given course, each period has the same unit objects. In this way the menu structure I provided was misleading. I think Period is a property of Use. Based on the Day, Period combination we need to be able to contruct a lesson plan, perhaps as I've tried to describe above from the uses with that Day, Period combination.

Tom,
Thanks again for taking the time to read through my thoughts as I try to work though this. I hope this post does more clearing up than confusing. I really appreciate all the time and energy you've put into helping me.

~ Dan

Legacy 98055 · Aug 30, 2006

Hi Dan.

Could the Viewing of a Lesson Plan simply be a "CreateGUI" method of the 'Unit' object that accepts Period and Day arguments, so that it populates a form with only 'Use' Objects with the correct Period, Day Combination?

Well... This is up for suggestion and if you asked five people you may get three or more different answers. My solution, which may or may not be the best way, would be to create a new object. A Lesson Plan object, for instance. In your Unit object, you would have a function that accepts Period and Day arguments and then returns a Lesson Plan object. This object encapsulates all of the relevant children and would be responsible for showing itself to the user (useform or other), validating user input, and updating the various children with any changes. Once again, it's more code but I think it's better code. Our previous efforts have led to making this as easy as looping through and checking the period and day properties of each use obejct.

Aside:
I thought we might use the parent property of the 'Use' object to get the 'Piece' object, and the parent property of the 'Piece' object to get the section object. In fact, perhaps we could add a (sLevel as string) argument to the parent method. e.g. use.Parent("Piece") would return the parent piece object, use.Parent("Section") would return the section object etc.

Sure, but it's not the usual syntax. Using our current code to get the name of a "Use" object's parent Unit object's title is fairly straight-forward.

Debug.Print us.Parent.Parent.Parent.Title

You will find this same syntax used by Microsoft Office.

Code:

Sub Example()
    Dim r As Range
    
    Set r = Range("A1")
    
    Debug.Print r.Parent.Parent.Parent.Name
End Sub

Range.Worksheet.Workbook.Application.Name

BTW,
For a given course, each period has the same unit objects. In this way the menu structure I provided was misleading. I think Period is a property of Use. Based on the Day, Period combination we need to be able to contruct a lesson plan, perhaps as I've tried to describe above from the uses with that Day, Period combination.

I think my suggestion above proves my understanding on at least this point.

You have not done so yet, but I wanted to mention this.
Your menu creation. I would not include this in your Course Planning System class. It is too exclusive. That is, exclusive to Office and more specifically, Excel. However, you could easily loop though all of your child objects and create the menu structure obtained by iteration.

Thanks again for taking the time to read through my thoughts as I try to work though this. I hope this post does more clearing up than confusing. I really appreciate all the time and energy you've put into helping me.

It cleared things up, I think. It's beginning to come together in my head and I believe that you will be glad that you took the long road.

We are, I think, to the point where you need to decide where you will be storing your data. We need to plug some data into your objects to do several things such as creating your dynamic menu, your lessonplan object, and the resulting dynamic forms, and creating reports. We need to do this based upon data that is actually loaded into your project at runtime and not just hard-coded examples. Originally, you were going to store your data in various workbooks. This is "Ok", but you might want to consider some other options. Remember that your objects will not care where the data is stored. We decided to make them independant of the data source. At a bear minimum, your data should be stored in worksheets that are set up in such a way that they can be saved in csv format. Pretty much any database program will provide the ability to import csv files. If I were you, I would place your code in a simple Microsoft Dabase file. mdb (Access) file. I understand that all of your users do not have access to "Access". This is not a problem. We do not need Access to utilize an Access database. Irregardless, glance over your objects and decide if they contain all of the neccesary properties you will need. The properties that need to maintain state between sessions should be obvious. We can then design a flatfile database in Excel or an ISAM database using several tables that I can create in Access or Visual Studio. If you have Access available to you at all, we should use an mdb. An Access database can be easily upgraded to an MSDE SQL database. Microsofts MSDE is free and allows 10 simulatanous connections. This should allow plenty of room to grow if it ever gets to that point.

PA HS Teacher · Aug 30, 2006

My solution, which may or may not be the best way, would be to create a new object. A Lesson Plan object, for instance. In your Unit object, you would have a function that accepts Period and Day arguments and then returns a Lesson Plan object. This object encapsulates all of the relevant children and would be responsible for showing itself to the user (useform or other), validating user input, and updating the various children with any changes

I like this idea. It seems more intuitive to me.

Your menu creation. I would not include this in your Course Planning System class. It is too exclusive. That is, exclusive to Office and more specifically, Excel. However, you could easily loop though all of your child objects and create the menu structure obtained by iteration.

I agree, I think looping through the objects is a much better idea, though I'm still new at creating menus, and as you can tell, my VBA experience thus far has been almost exclusively through Excel. I have no intentions of merging the Hollow file above with our project. I just wanted to show you what a more fleshed out version of the project might look like.

We do not need Access to utilize an Access database.

I didn't know that. I don't have Access at home, and I will be getting into school for the first time on Friday. I see if I can get to any computers that have Access. Generally school computers do not.

Irregardless, glance over your objects and decide if they contain all of the neccesary properties you will need.

While we're taking the long road, I start school with the kids on Tuesday the 5th. I won't have time to do a lot of coding in the next week to two weeks but I will sit down and think through the properties of each class and make sure it contains everything I need. Actively teaching will also help me better match the properties to the Lesson Plan format of my new school.

One issue I can ask you help on is how to wrestle with/ where to store some preferences. I also want the ability to "export" a lessonplan to word. I want to be able to control how the lesson plan is presented. (both on a form and in the word document. I want to be able to control the order in which I present the Section, whether or not it contains a Header, Is the Header Bold, but most importantly the Sequence in which the Sections are presented. (and may need a few non generic sections, e.g. for Objectives, because the generic section object doesn't quite accomodate the the way we write objectives at this school) But if I have these additional specifically created objects. (which would be at an equal level on the tree of objects to the generic Section object) I want to be able to order them too. And I want to make sure these custom objects, there wouldn't be many, don't disrupt our object structure.

I'll report back later this week week or early next week with some kind of update.

Tom, thank you for taking the time to share your expertise. I really feel like you are showing me how professional programmers create professional programs. You have such a handle on how to do this, which is helping turn a dream of mine into a reality. Thank you.

Create Userform on the Fly?

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Excel Facts

Legacy 98055

Guest

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Similar threads

Forum statistics

Share this page

Create Userform on the Fly?

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Excel Facts

Legacy 98055

Guest

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock