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 22, 2006

Norie,
You're the best. Take your time. I've many other tasks to keep me busy.

~ Dan

Legacy 98055 · Jul 22, 2006

Hi Dan and Norie...

Dan. You do not need Access to utilize an Access database. You would need to design it though. I understand Norie's point and thought of it as well. Even so, wrapping your data in classes is preferred to directly accessing a database as far as your project is concerned because of the compartmentalization aspect. I glanced at your project again and am not really concentrating on your code but on your logical design that the code should clearly portray. One very important consideration and a common mistake is to create objects explicitly that should only be create implicitly.

For example. You have a "Car" class. You do not want to explicitly create a "SteeringWheel" class in your code. This class should be returned as a property of the "Car" class and implicitly created from within the "Car" class. The Range object is a good example of this. You cannot directly create a range object. It can only be returned as a property of another class such as the application or worksheet classes. That is why this code will never compile...

Dim r As New Range

It is almost always a bad idea to use a collection outside of a class to maintain a collection of objects. It's easier in the short term but will bloat your project down the road and lead to more complicated and error prone calling code. The collection should be maintained within the parent class. Again, the Range object is a good example of this...

Sub Example()
Dim r As Range
'without your consideration, the Range
'being referenced by 'r' also contains
'an implicit collection of ten ranges
Set r = Range("A1:A10")

Debug.Print r(1).Address
Debug.Print r(10).Address
End Sub

The collection is created and maintained for you without your intervention and is available via the default 'Index' property...

Debug.Print r.Index(1).Address
Debug.Print r.Index(10).Address

My favorite part of a project is the logical design and a good logical design will lead to good code. A bad logical design leads to code that you will not be able to understand down the road even if you are the author.

Am going fishing with my son so I do not really have time to post a decent example right now. Your logical design should match the phyical design of your objects as closely as possible.

Briefly, create an outline on paper and decide how your objects should be aligned. Your procedure creates several different objects that might be better contained within a parent...

Example:
Sections
....Section
........Piece

The toplevel object being the 'Sections' class. This class would contain a method called 'Add' which accepts generic arguments. The 'Sections' object might return one or more child 'Section' objects. The 'Sections' object would be responsible for the actual creation and maintenance of the individual 'Section' objects. The 'Section' objects, in turn, would create 'Piece' objects. I may be making this sound more complex than it really is, but am really just trying to help you out. It is well worth your time to look deeper into the object based design. Especially if you later move into more recent versions of VB which are actually object oriented. I'll provide an example tommorrow that will make good sense too you...

Error:
Debug.Print r.Index(1).Address
Debug.Print r.Index(10).Address

The default property of the range is 'Item':
Debug.Print r.Item.Address
Debug.Print r.Item).Address

PA HS Teacher · Jul 23, 2006

Tom,
You explanation is clearing up many concepts in my mind. I really appreciate your perspective and I think I see what you mean. Please permit me to explain back to you what I think I am learning.

If I understand you,

Rather than creating the controls from my Regular module as in my posted file above, I would be much better off creating them in my 'Piece' class module. Similarly I would be much better off collecting my 'Piece' objects into a property of type collection in a 'Section' module, or better maybe as properties of a 'uf' object which would be a property of Section object. Maybe I should rethink of my sections as Uf objects. I will think this through on paper as you've suggested and post back an object model outline.

In this way I would be creating objects 'implicitly', they are created simply by instantiation of the parent object (code in the class module of the parent object is used to create these objects as a propert(y/ies) of the parent object), rather than having to 'explicitly' create these objects in a general code module everytime I create the parent object. If I sit down and really think through and map out an object model that closely resembles my physical situation, I save myself a lot of time and headaches, by setting up my objects the simple act of instantiation does most of the 'heavy lifting', leaving me less opportunities to make mistakes in my general modules, and greatly simplifying my code.

I would learn greatly from any example you might post. Do not feel rushed, My wife is off today and I will not be putting a lot of time into this project today anyway. I will post back an object model for my project, and perhaps you could look it over. 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 http://www.mrexcel.com/board2/viewt...c&highlight=implicit+class+property&&start=10

As always, thank you for your guidance. Good Luck getting your bait wet.

Legacy 98055 · Jul 23, 2006

I think you are grasping it. In essense, you are hiding complexity by encapsulating most of the functionality within your objects. This includes 'mass' error handling. You don't want to have to write code to teach a car how to drive from outside the car object. The car object should know how to drive already and should have a method called DriveMe. I'll get to that example but am hoping that Tusharm will add to this discussion or at least tear it apart!

PA HS Teacher · Jul 24, 2006

My Object Model 7-24-06 1:30 pm EST

Tom, Norie et al.,
Here is my draft of an object model for my model. See my next post for my attempt at implementing this model.
Edit [Here's my Current Attempt] http://www.box.net/public/yajt3katad

Object Model Planning System 7-20-06.xls

A

B

C

D

E

F

G

H

I

J

K

L

1

Courses

(cls_5_Course)

Object Model for My Course Planning System

2

Course

3

Units

(cls_4_Unit)

4

Unit

5

Title

as Range

Sections

(cls_3_Section)

6

Description

as Range

Section

7

Title

as Range

Pieces

(cls_2_Piece)

8

Description

as Range

Piece

9

Comments

as Range

Frm

as SectionTemplateFrm

Uses

(cls_1_Use)

10

TargetNumDays

as Range

ID

as Worksheet

Use

11

UnitFileAddress

as Range

PiecesRng

as Range

ID

as Range

12

Unit

as workbook

Title

as Range

Txt

as Range

Day

as Range

13

Line

as Range

Retired

as Range

Section

as Range

14

DisplayHeader

as Range

LastTerm

as Range

Term

as Range

15

Collection

CenterHeader

as Range

LastUse

as Range

Order

as Range

16

Exclude

as Range

Created

as Range

UseTB

as Range

17

Object

LastModified

as Range

UseLB

as Range

18

TxtTB

as Control

19

Property

TxtLB

as Control

20

updated: 7/24/06 1:30 PM

IDLB

as Control

Object Model

PA HS Teacher · Jul 24, 2006

The Class Modules for the Object Model outline above would be contained in a Workbook Called, Dans Courses.xls. This workbook will contain a Preferences Worksheet, and a Worksheet containing the data for each 'Course'.

Dans Courses.xls

A

B

C

D

E

1

Title

Physics

2

Description

College Prep

3

4

Units

5

Title

Description

Comments

TargetNumDays

UnitFileAddress

6

Unit 1: Kinematics

1-D Motion

Great Unit

15

C://Course 1/Unit1.xls

7

Unit 2: Newton's Laws

Forces

Also a Great Unit

12

C://Course 1/Unit2.xls

8

Unit 3: Momentum

Collisions

Like this too

10

C://Course 1/Unit3.xls

9

Course 1

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

PA HS Teacher · Jul 24, 2006

I've had some succes "wrapping" my lower objects into my higher objects, but I don't know how to use a class module to collect smaller objects into a collection.

For example, How can I use my cls_2_Piece to collect 'Use' objects into a Uses collection?

Here is the class module for my "smallest" object
cls_1_Use

Private p_rngDay As Range
Private p_rngSection As Range
Private p_rngTerm As Range
Private p_rngOrder As Range
Private p_ctlUseTB As Control
Private p_ctlUseLB As Control

 ' Day
Public Property Set Day(rngDay As Range)
 If p_rngDay Is Nothing Then _
 Set p_rngDay = rngDay
End Property
Public Property Get Day() As Range
 Set Day = p_rngDay
End Property

 ' Section
Public Property Set Section(rngSection As Range)
 If p_rngSection Is Nothing Then _
 Set p_rngSection = rngSection
End Property
Public Property Get Section() As Range
 Set Section = p_rngSection
End Property

 ' Term
Public Property Set Term(rngTerm As Range)
 If p_rngTerm Is Nothing Then _
 Set p_rngTerm = rngTerm
End Property
Public Property Get Term() As Range
 Set Term = p_rngTerm
End Property

 ' Order
Public Property Set Order(rngOrder As Range)
 If p_rngOrder Is Nothing Then _
 Set p_rngOrder = rngOrder
End Property
Public Property Get Order() As Range
 Set Day = p_rngOrder
End Property

 ' UseTB
Public Property Set UseTB(ctlUseTB As Control)
 If p_ctlUseTB Is Nothing Then _
 Set p_ctlUseTB = ctlUseTB
End Property
Public Property Get UseTB() As Control
 Set UseTB = p_ctlUseTB
End Property

 ' UseLB
Public Property Set UseLB(ctlUseLB As Control)
 If p_ctlUseLB Is Nothing Then _
 Set p_ctlUseLB = ctlUseLB
End Property
Public Property Get UseLB() As Control
 Set UseLB = p_ctlUseLB
End Property


Here is the class Module for the next object up
cls_2_Piece

Private p_RngID As Range
Private p_RngTxt As Range
Private p_RngRetired As Range
Private p_RngLastTerm As Range
Private p_RngLastUse As Range
Private p_RngCreated As Range
Private p_RngLastModified As Range
Private p_ctlTxtTB As Control
Private p_ctlTxtLB As Control
Private p_ctlIDLB As Control
Private p_colUses As Collection

' ??????????????????????????????????????????????????????????????
 ' Uses
' I'm not sure how to add cls_1_Use objects to p_colUses collection
' Related, how do I Add a ctlTxtTB, ctlTxtLB, and ctlIDLB to
' Frm of the parent cls_3_Section object ?

 ' ID
Public Property Set ID(rngID As Range)
 If p_RngID Is Nothing Then _
 Set p_RngID = rngID
End Property
Public Property Get ID() As Range
 Set ID = p_RngID
End Property

 ' Txt
Public Property Set Txt(rngTxt As Range)
 If p_RngTxt Is Nothing Then _
 Set p_RngTxt = rngTxt
End Property
Public Property Get Txt() As Range
 Set Txt = p_RngTxt
End Property

 ' Retired
Public Property Set Retired(rngRetired As Range)
 If p_RngRetired Is Nothing Then _
 Set p_RngRetired = rngRetired
End Property
Public Property Get Retired() As Range
 Set Retired = p_RngRetired
End Property

 ' LastTerm
Public Property Set LastTerm(rngLastTerm As Range)
 If p_RngLastTerm Is Nothing Then _
 Set p_RngLastTerm = rngLastTerm
End Property
Public Property Get LastTerm() As Range
 Set LastTerm = p_RngLastTerm
End Property

 ' LastUse
Public Property Set LastUse(rngLastUse As Range)
 If p_RngLastUse Is Nothing Then _
 Set p_RngLastUse = rngLastUse
End Property
Public Property Get LastUse() As Range
 Set LastUse = p_RngLastUse
End Property

 ' Created
Public Property Set Created(rngCreated As Range)
 If p_RngLastUse Is Nothing Then _
 Set p_RngCreated = rngCreated
End Property
Public Property Get Created() As Range
 Set Created = p_RngCreated
End Property

 ' LastModified
Public Property Set LastModified(rngLastModified As Range)
 If p_RngLastModified = Nothing Then _
 Set p_RngLastModified = rngLastModified
End Property
Public Property Get LastModified() As Range
 Set LastModified = p_RngLastModified
End Property

 ' TxtTB
Public Property Set TxtTB(ctlTxtTB As Control)
 If p_ctlTxtTB Is Nothing Then _
 Set p_ctlTxtTB = ctlTxtTB
End Property
Public Property Get TxtTB() As Control
 Set TxtTB = p_ctlTxtTB
End Property

 ' TxtLB
Public Property Set TxtLB(ctlTxtLB As Control)
 If p_ctlTxtLB Is Nothing Then _
 Set p_ctlTxtLB = ctlTxtLB
End Property
Public Property Get TxtLB() As Control
 Set TxtLB = p_ctlTxtLB
End Property

 ' IDLB
Public Property Set IDLB(ctlIDLB As Control)
 If p_ctlIDLB Is Nothing Then _
 Set p_ctlIDLB = ctlIDLB
End Property
Public Property Get IDLB() As Control
 Set IDLB = p_ctlIDLB
End Property

Legacy 98055 · Jul 24, 2006

Here is a beginning example. We'll work our way down your model and then create more concise code so we don't end up having code that looks like this:

Object(Index).Item(Index).Item(Index).Item(Index).SomeProperty

Use generic arguments whenever possible. Do not pass a range unless you have to. I replaced your range arguments with string. If you later decide to store your data in some other medium, your class code will break.

Download the example and step through the code in Module1.

Object Model Planning System 7-24-06 (Version 2).zip

Module1

Code:

Option Explicit

Dim cps As CoursePlanningSystem

Sub Example()
    Set cps = New CoursePlanningSystem
    
    If Not cps.CourseExists("Course One") Then
        cps.AddCourse "Course One", "Course One Description"
    End If
    
    If Not cps.CourseExists("Course Two") Then
        cps.AddCourse "Course Two", "Course Two Description"
    End If
    
    'same as cps.Courses(1).Title
    Debug.Print cps(1).Title
    'same as cps.Courses("Course One").Description
    Debug.Print cps("Course One").Description
    Debug.Print cps.CoursesCount
    cps.RemoveCourse "Course Two"
    Debug.Print cps.CourseExists("Course Two")
    Debug.Print cps.CoursesCount
End Sub

Class
CoursePlanningSystem

Code:

Option Explicit

Private pCourseCollection As Collection
Private WithEvents pCourse As Course
'it may seem redundant to wrap a collection object, but it is a good practice
'by giving you more control over your objects and providing code
'that is easier to understand and maintain

'this is a readonly property.  No Property Set.
'we do not want to allow direct access to create 'stand-alone' course objects.
'Access to the Course object and all child objects will be managed
'by this parent class.  You should change this limitation only if you think it is necc.
'
'the index is a variant to allow an integer or string
'this will allow you to refer to a course object by position or Course Title
'same as Sheets("Sheet1") or Sheets(1)
'VBA does not provide overloading so this is the only way we can provide
'this flexibility while using only one argument
'
'this is the default property
'see
'http://www.cpearson.com/excel/DefaultProperty.htm
Public Property Get Courses(Index As Variant) As Course
    'you will need to validate the index before using it
    
    Set Courses = pCourseCollection(Index)
    Set pCourse = Courses
End Property

'read only count of course objects
Public Property Get CoursesCount() As Integer

    CoursesCount = pCourseCollection.Count
    
End Property

'require the minimum amount of information to actually
'create a valid 'Course' object
Public Function AddCourse(Title As String, Description As String) As Boolean

    If Not CourseExists(Title) Then
        Dim c As New Course
        c.Title = Title
        c.Description = Description
        pCourseCollection.Add c, Title
    Else
        Err.Raise 457, , "This key is already associated with an element of this collection" & vbCr & "The course, """ & Title & """, already exists..."
    End If
    
End Function

Public Function RemoveCourse(Title As String) As Boolean

    If CourseExists(Title) Then
        pCourseCollection.Remove Title
    End If
    
End Function

Public Function CourseExists(Title As String) As Boolean

    On Error Resume Next
    
    Dim c As New Course
    Set c = pCourseCollection(Title)
    If Err.Number <> 0 Then Exit Function
    CourseExists = True
    
End Function

Private Sub Class_Initialize()

    'this is where you would load the data for all of your objects
    'set up defaults, ect...
    Set pCourseCollection = New Collection
    
End Sub

Private Sub pCourse_UpDate(Instance As Course, OldTitle As String, NewTitle As String)

    Dim SaveIndex As Integer
    
    For SaveIndex = 1 To pCourseCollection.Count
        If pCourseCollection(SaveIndex).Title = OldTitle Then Exit For
    Next
    
    pCourseCollection.Add Instance, NewTitle, SaveIndex
    pCourseCollection.Remove SaveIndex
    
    Set pCourse = Nothing
    
End Sub

Class
Course

Code:

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)

' Again, How do I collect up cls_4_Unit objects into p_colUnits ?

                    ' 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

Legacy 98055 · Jul 26, 2006

Hi Dan.

Am awaiting some input from you. I know that I have strayed far from your original question. We'll get to that as we move down the chain and get to your Section object or, if you have determined to go at it the way you have currently designed your object templates, we can just answer your original post. I think I have a pretty good handle on your project and your approach, but want to mention one more thing before continuing on. Your method of binding your ranges to object properties may not be a good idea and you may regret it down the road. My opinion and my approach would be to use native types whenever possible such as string or integer. The objects should not be directly bound to the type of container that is holding your data such as a range. What if you decide to store your data in a database? Or even a simple textfile? All of your objects would need to be changed. This, in turn, would alter the interface causing your calling code to break. A better approach is to make your objects as generic as possible. Make them independant of the data source. If you are set on storing your data in worksheets and ranges then create a "Binding" object to bind your object's properties to specific ranges. This way, if you later decide to change the way your data is stored, you will only need to change the way your data is bound. A much easier alternative.

Whatever you decide is just fine. Let me know your direction and I'll actually answer your original question. I hope I did not kill this post with bloat...

PA HS Teacher · Jul 26, 2006

Tom,
I appologize for not getting back to you sooner. I should have posted an update yesterday. I worked on a project for my wife yesterday (It was actually Excel VBA!), but I did not pick up this one.

I am about to sit down and work with your 7/24/06 post. You have in no way killed this post. I am eternally greatful to work with someone such command of Object oriented programming.

As I was reading, http://www.microsoft.com/technet/prodtechnol/office/office2000/solution/part2/ch09.mspx?mfr=true before my previous post, I thought it might be better to use "Scalar" properties. I was hoping you might see a better way than setting the properties of my objects = Ranges. I just didn't/ don't know how to do that. I am interested in how to use a "binding" object. You're absolutely right about using basic data types. It sounds like minimal work now that could save untold hours of work later if I were to change the storage medium. (Say to Access or a txt file (interesting)).

I am very much interested in doing this the right way, and I understand the original question about userforms may be a few steps away. I will post back after working with your example. (Though I realize I may need a binding object example first?) I will post back soon.

~ Dan

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

Legacy 98055

Guest

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Legacy 98055

Guest

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

Legacy 98055

Guest

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Legacy 98055

Guest

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