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 · Jul 26, 2006

Hi Tom,
I'm stepping through your code, and the flow of the code pretty much makes sense to me, but I'm curious what

the, RaiseEvent UpDate(Me, p_Title, NewTitle) line does in the Course Class

I See the Event Update line, but I'm not sure how it works.

Also, The Property Let Description below is the only place where I see the friend statement. (What does friend do here?)

Forgive me if it takes a little time to get the hang of it.

~ Dan

Course class

Option Explicit

Private p_colUnits As Collection
Private p_Title As String
Private pDescription As String

Event UpDate(Instance As Course, OldTitle As String, NewTitle As String)

 ' Title
Public Property Let Title(NewTitle As String)
 RaiseEvent UpDate(Me, p_Title, NewTitle)
 p_Title = NewTitle
End Property

Public Property Get Title() As String
 Title = p_Title
End Property

 ' Description
Friend Property Let Description(Description As String)
 pDescription = Description
End Property

Public Property Get Description() As String
 Description = pDescription
End Property

PA HS Teacher · Jul 26, 2006

I used the 'Course' class as a template/ model for the 'Unit' Class, and I was able to get the Course Objects to instantiate 10 Unit objects, via a loop in the .AddCourse function of the 'CoursePlanningSystem' class.

http://www.box.net/public/prnvi3xaky

See the output of the immediate window to see what I mean. None of my objects are bounded to any data yet. That was pretty cool having one object instantiate another, even if none of my objects contain actual data yet. I think I'll try adding 'Section' Objects to the 'Unit' Objects in the same manner.

Just keeping you up to date. (perhaps more up to date than needed)

~ Dan

Legacy 98055 · Jul 27, 2006

From a previous post.

I am particularly interested in the possibility of indexing a property 'Instance' of 'Piece' of 'Section' by the syntax Section.Piece(sPieceID).Instance(dDay,iPeriod), similar to the MyCar.Doors(x,y). , example I saw in Tushar-Meta's insightful post

Please elaborate.

Though I realize I may need a binding object example first?

After we have your model designed, we'll bind your ranges.

the, RaiseEvent UpDate(Me, p_Title, NewTitle) line does in the Course Class

Since we are allowing write access to the property that also serves as a key in the parent collection, we have to notify the parent when this property is changed in the child. The parent then refreshes the collection.

Example:

Code:

Sub Example()
    Dim cps As New CoursePlanningSystem, c As Course
    
    Set c = cps.AddCourse("Course One", "Course One Description")
    
    'the property c.Title = "Course One"
    'it also serves as the key for the item 'c' being stored in the
    'pCourseCollection located in the parent class 'CoursePlanningSystem'
    'if we change the c.Title property later on, the pCourseCollection
    'will need to be updated to reflect the new key
    
    'this line will cause the event to fire in the parent and run code to
    'refresh the collection
    c.Title = "New Title Here"

End Sub

Also, The Property Let Description below is the only place where I see the friend statement. (What does friend do here?)

I have no clue why I got friendly with this one.

Friend and Public have the same scope within the VBAProject that contains your objects. Friend only restricts access from outside access. That is, outside of your project.

I used the 'Course' class as a template/ model for the 'Unit' Class, and I was able to get the Course Objects to instantiate 10 Unit objects, via a loop in the .AddCourse function of the 'CoursePlanningSystem' class.

If you want to add default objects, you should create a separate method that calls the 'AddCourse' method 10 times as opposed to hard coding into your method. You did mention the hard coding in your comments and you are probably just experimenting, but I wanted to mention this. To do this in the updated file attached, you would use this in your calling code...

Code:

Sub Example()
    Dim cps As New CoursePlanningSystem, c As Course, u As Unit
    
    If Not cps.CourseExists("Course One") Then
        Set c = cps.AddCourse("Course One", "Course One Description")
    End If
    
    If Not c Is Nothing Then
        Dim i As Integer
        
        For i = 1 To 10
            If Not c.UnitExists("Unit " & i) Then
               Set u = c.AddUnit("Unit " & i, "Unit " & i & " Description", "Comments", i)
               Debug.Print ("Successfully added " & u.Title & " of " & _
                         "Course " & c.Title)
            End If
        Next i
        
        'your objects now support enumeration as well
        For Each u In c
            Debug.Print u.Title
        Next
    
    End If
    
End Sub

I added an ID property to each of your objects. In several of your classes, the ID is the same as the Title. The ID property is read-only in these classes. In classes that do not contain a Title property, the ID is Read-Write. If we can enforce a unique ID for each and every object, you can create shorthand code to reference objects.

Run Example1 and then Example2:

Code:

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
    
    '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("Piece 1", "Text", True, Now, Now, #6/7/2006#, Date)
    End If
    
    If Not p.UseExists("Use 1") Then
        Set us = p.AddUse("Use 1", #6/7/2006#, "Section", "Term", 1)
    End If
    
End Sub

Sub Example2()
    'define tree
    'Course 1, Unit 1, Section 1, Piece 1, Use 1
    
    Dim u As Use
    
    'all three of these syntax variants will return the same Use object
    Set u = cps.Courses("Course 1").Units("Unit 1") _
        .Sections("Section 1").Pieces("Piece 1").Uses("Use 1")
    
    Set u = cps("Course 1")("Unit 1")("Section 1")("Piece 1")("Use 1")
    
    Set u = cps(1)(1)(1)(1)(1)
    
End Sub

If all of your ID's are unique for each object created, we could place a method in your parent class such as:

Code:

Public Function RefByID(ID as String) as Object
    'some code
End Function

Then example2 would look like:

Code:

Sub Example2()
    Dim u As Use
    
    Set u = cps.RefByID("Use 1")
    
End Sub

A great convenience when dealing with embedded collections.

Anyway, take your time and look over the code when you get a chance and we'll go from there. This will all make sense when we're done.

Course Planning System 0727.zip

PA HS Teacher · Jul 27, 2006

Before I start pauring over your latest, and very appreciated code, let me answer your question about my possible desire for an ojext(???, ???) syntax. It is more an artifact of my ability to visualize the unit data stored in a single worksheet than anything else.

If you look this snapshot of my original proposal for storing the data in a 'Unit' object.

Quote: From my Earlier Post about how I might set up 'Unit' worksheet

Each 'Unit' is stored as an .xls file in a course folder as outlined above. Each 'Section' is stored as a worksheet in the 'Unit' file as shown below. Each 'Piece' is represented by a Column. Each 'Use' is represented by a Row.

Unit 3.xls

A

B

C

D

E

F

G

1

Title

Section 1

ID

1

2

Line

1

Txt

Txt P-1

Txt P-2

3

DisplayHeader

TRUE

Retired

FALSE

4

CenterHeader

TRUE

LastTerm

2004-2005

5

Exclude

FALSE

LastUse

5/21/2005

6

Created

5/1/2002

7

LastModified

5/1/2002

8

9

Day

Section

Term

Order

10

7/15/2006

Per 1

2006-2007

1

Specific Use Info Goes in these Cells

11

7/16/2006

Per 1

2006-2007

1

12

7/17/2006

Per 3

2006-2007

1

S1

My desire is to be able to store basic information about the 'Piece', which generally would not change much over time, in properties of the 'Piece' object. I would also like to be able to store some information about the particular use of the 'piece' object in 'use' objects. Each Use will be unique by the (Day, Section) combination. (not to be confused with 'Section', in fact let's call this section Period)

Now that I'm thinking about it, they should be two distinct objects. Day and Period.
Where 'Use' is renamed to 'Day' and one more level of objects, 'Period' is added. 'Period' would contain a property for the Txt value of the 'Piece' at the time 'Period' was recorded, and a property for any "use Specific" information.

(These objects are getting past the point where I could set up a spreadsheet as outlined above, but we are worrying about binding data later. And this object method seems like an unbelievably powerful tool compared to my previous attempts at this project)

Let me give you a concreate Example:

Book1

A

B

C

D

1

'CoursePlanningSystem'

I

2

'Course'

Teach a Physics 1 Class

3

'Unit'

Which has a Unit on Gravity

4

'Section'

Which has a Section called Objectives

5

'Piece'

One Objective is: Students should know why things fall

6

'Day'

This Lesson Takes Place on : 7/27/2006

7

Period'

One of My Periods Is: Period 1

Concrete Example

Do these end goals impact this object structure?

I envision storing the data in the objects we have been framing. As we continue to frame out these objects, it is useful to look up at the next step, which would be to interact with these objects. The primary mode of interaction would be a form containing the information pertaining to a particular 'Lesson' which consists of the 'Section' objects and their child 'Pieces'. I envision being able to Set up a 'Lesson' Object that consists of the appropriate objects we have beeen building, So I'm not that concerned. The one feature that concerns me slightly is the ability to display, 'Piece' objects on this form in a desired order. (might order be a property of my newly conceived 'Period' object.)

I am not trying to jump the gun, I was just trying to give you a sense of exactly what I am trying to accomplish. Thank you for your help, and if you have the opportunity to fish, fish. I've got time. I figure if I spend some time each day, I should have a fully functional system in place before the school year starts. I will take work through your most recent code and post back sometime today.

~ Dan

Legacy 98055 · Jul 27, 2006

It appears to me that the Piece and Use objects should be replaced with an Objective and Lesson object. Lesson is the lowest denominator worthy of classing I can conceive of in your description. The singularity being determined by the combination of lesson period and lesson date. We are probably on the same page but differing in semantics. The unique ID or key for each lesson would be a combination of the date and period. A lesson will always belong to an objective. But an objective can be carried out on different days and in different periods. So they are unique subsets of the objective. Also, the lesson object would seem to be the smallest object that you may later associate with other objects such as a 'Student' object if you intend on extending your project to track student progress, grading, etcetera. The Term property is redundant because we can determine the term using the lesson date. You could create a Period object but I would let it stand as a property of the Lesson object. The only purpose of time or period would be to differentiate or collate objects of the same type so there is not really a justification to create a separate object to hold objects that already know what period they belong to.

What is the desired order to diplay your lesson's? Date and then Period? If so, no problem here. Simple math to add the two together and sort ascending. Period 1 of some date will always be smaller than period 2 of the same date. Period should be an integer instead of a string. you can always add the string "Period" for display.

This structure lends it'self to more convenient implementation as well. You will edit lessons much more often than you will edit an objective. The lesson object will know how to display a userform, know where to place the data, and know what to do with the data that has been edited. It should also have the ability to add or remove lessons via the user interface. Remember that a userform is a class module. You may very well decide to place the lesson code within your userform class and then create instances of it just as you would any 'from scratch' class. I would only do this if I was certain that this code will be running from within VBA as opposed to stand-alone VB or VB.Net. If you intend on porting it to another platform eventually, I would stick with the custom class and create the userform object implicitly within your class.

PA HS Teacher · Jul 27, 2006

The only purpose of time or period would be to differentiate or collate objects

You are absolutely right about that.

You're also correct about the 'Lesson' being central to my thinking. I'm a little hung up on a Lesson belonging to an Objective. An objective is simply one kind of 'Section'. Wouldn't a Lesson also belong to the Homework instance of 'Section' and other every 'Section' object in the collection of sections?

My goal is to have the ability to quickly create Lesson plans from my own personal database. Think, choose your own adventure. For every lesson, I pick a few objectives, I pick a few (hopefully not too many for my students) homeworks, I pick a few daily activities. Objects that to this point I've though of as the 'Section' objects.

On most days the Lesson would be identical for each period, I just wanted to leave myself the ability to differenciate. Also, Lessons would naturally differ differ from year to year. The 'Use' object was my attempt to capture these differences. The Txt property of the 'Piece' Object would not change, but each 'Use' would allow me to record and specific comments I might make for that period or year.

When I spoke about the ability to order my 'pieces' what I meant is, Let's say I have 3 objectives, "associated with" a particular 'Lesson'. One year I might order them
1. X
2. Y
3. Z

Another year I might decide to change the order or use other objectives, or use differen objectives entirely.
1. Y
2. Z
3. W

The same would be true for my homeworks, acitivities, questions, and other 'Sections'.

Here is an abreviated version of what a Lesson might look like:

Example Lesson.xls

A

B

C

D

1

Date:

7/26/2006

2

Period

2

3

Unit

Gravity

4

5

Objectives:

6

1

Students Should be able to give an example of an object under the influence of gravity.

7

2

Students Should be give an example that demonstrate the effects of gravity.

8

9

HW:

10

1

Read pg 23-27. Based on what you've read, explain how life would be different if you grew up on the moon.

11

12

Activities:

13

1

Penny vs. a Feather Demonstration. (I use a vacuum, so they hit the ground at the same time.)

14

15

Bell Ringer/Concept Tests (These are Questions in Class)

16

1

If I drop this paper and this book which will hit the ground first. (I put the paper under the book!)

17

2

Do you think the earth is falling?

18

3

Is the gravity holding the earth in orbit around the sun different than the gravity holding you on the ground?

Lesson

As fas as period being a number. Generally that's true, I was just trying to leave myself the possibility of having say, Period 5E for 5 early, or 2A, or the Red period etc. I know it sounds silly, but sometimes schools have strange names for periods. Perhaps we'd assign each period a number and then it would have some property with the string name of the period. Its not critical, just trying to maximize flexibility.

If you intend on porting it to another platform eventually, I would stick with the custom class and create the userform object implicitly within your class.

You do think far ahead. I do like the possibility of being able to converting this down the road. I am trying to create something that will last a "long" time. Thank you for your help, and your patience.

I will post back about your code tonight or tomorrow morning.

~ Dan

Legacy 98055 · Jul 28, 2006

Ok. You know how you want to do this better than I do. Disregard my previous post as I really misunderstood.

PA HS Teacher · Jul 28, 2006

Tom,
My Mom came to visit my wife and I unexpectedly today. (Well a week ahead of schedule anyway). She will but up until Monday or Tuesday. If you do not hear much from me in the next couple of days, my interest in this project and my appreciation of your efforts have not wained.

If I had laid out the concept of a lesson plan earlier on, I would't have caused as much confusion. I find it amazing how well you comprehend my vision, where as most people I discuss/ draw it out with in person get lost pretty quickly.

Without having had time yet to think it through thoroughly, my current impression would be to stick with a 'use' object, but combine the Date and period together as one unique property to use a key for the 'use' as you have suggested. I'm confident that with this structure, a Lesson object can be created from the appropriate 'Use' objects.

Quote:
Set u = cps(1)(1)(1)(1)(1)

My goodness will this be slick way to code. It will make maintaining my objects much easier. Without your help I would never have considered so many of these issues. I will play with your code and post back my attempt to add to/modify/ experiment with it in a few days. Thank you for your patience in helping me make this quantum leap in programming.

PA HS Teacher · Aug 1, 2006

Tom,
I worked with your code for a few hours today (I'm still getting the hang of this.) I played around with adding a 'Period' object, and was reasonably successful, but didn't realize until afterwards that I had the file open as read only and it never saved. I think I'm better avoiding another object anyway, and possibly expanding the 'use' object.

My Concept for the 'Use' object is to store information about the 'piece' object at the time a 'piece' is used in a Lesson. So at some point down the line when I have Code that uses the Text property of a 'Piece' in the creatomg a lesson plan, a 'Use' object is added to a collection of 'Use' Objects in the 'Piece' object.

This Use Object should contain:
TextAtRecord - The value of Piece.Text when the LP is recorded.
Day - The Date the 'Piece' was used in the lesson
Period - Designation for the Class receiving Lesson, Period 1, Group A, etc.
Order - The order of this 'Piece' within its 'Section' on this particular day, for this particular period.
[Period replaces Section in your most recent code for 'Use']
(This Use might represent the 3rd Homework in Unit 2 of Course 1) The Order would be determined when a Lesson Plan is recorded.

Each Use would be Unique by the combination of Day and Period.

What would be a good way to create a unique ID ? Simply concatenate? Does the ID need to be numeric?

Thank you for putting so much time and effort into this project.

~ Dan

Legacy 98055 · Aug 4, 2006

Dan.

I have not forgotten about your post but will not have time to reply for a day or so...

Create Userform on the Fly?

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Excel Facts

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

PA HS Teacher

Well-known Member

Legacy 98055

Guest

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

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

PA HS Teacher

Well-known Member

Legacy 98055

Guest

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