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?

Legacy 98055 · Jul 20, 2006

Hi PaHs. Yes, Yes, and Yes... I'll delve into this with you later today. In the mean time, if you have the time, please describe what you need these ten forms to accomplish in a bit more detail. Your "highest" object can return these forms as properties or you can initiate instances directly. You can create as many as you'd like.

"I don't understand why your code does not have its own class module, and I"m not sure how to create multiple instances."

That's just example code placed in a sheet class to accommodate the WithEvents requirements. VBA lacks any ability to really utilize inheritance and polymorphism. If you will have ten forms in your project, create a template that contains the properties that are common to most or all of your forms. Create a private custom class module that returns intances of this form. Within, you could add any relevant events, properties, ect... The alternative is to create a "code" template using Tom U's or JWalks methods. The method we are discussing is a better choice when dealing with multiple forms that have similiar interfaces. I'll post an example later.

If possible, post your workbook, or at least some of your code. If you wish, you can post your workbook by:

1. Zip up your Excel file. It must be in zip format.
2. Email it to (mrexcel at fuse dot net)
3. Make sure that “MrExcelPostFile” is in the subject (without the quotes)

You should get a reply shortly with a URL to copy and paste directly to the board.

PA HS Teacher · Jul 20, 2006

Right_Click
Thank you for taking the time to look at this. I really appreciate it. Let me preface this by saying that you need only consider Goal i). However, I’ve tried to provide you with the full picture of what I am trying to do. I’ve followed your instructions and am waiting for a response from MrExcel@fuse.net. ( I was wondering how people were posting their zip files). I’ve tried to strip the file down to only what is essential and I’ve included descriptions of what the file does in comments and textboxes within the file itself.

I’ve also included my code below. My goals, explanation of what the forms should do is below the code.

Overall Purpose:
1. To Design a Lesson planning system for myself. (I am a high school teacher)
To maintain and manipulate a database of Sections. Each Section represents a type of piece to one of my lessons, (HW, Lesson Objectives, Lab Descriptions etc.)

2. To facilitate the quick writing and viewing of my lesson plans.

3. To facilitate the quick generation of reports related to my lesson plans.

For now I am concerned predominantly with Purpose # 1.

What I've done:
a) I’ve set up this database such that each section gets 1 sheet.
b) Each Sheet contains “Pieces” or records of an object of class clsPiece
Option Explicit
Public colInstances As Collection

Public IDRng As Range
Public TxtRng As Range
Public NumUsesRng As Range
Public RnkUsesRng As Range
Public LastUseRng As Range
Public OrderRng As Range
Public RetiredRng As Range
Public LastTermRng As Range
Public CreatedRng As Range
Public LastModifiedRng As Range

Public TxtLabel As New clsCtlTxtLbl
Public IDCommand As New clsCtlIDCmd


c) Each Piece Contains information about that Piece (particular HW, Objective etc.). It also contains information about specific uses of that Piece, with the relevant information for each specific use stored in a the collection listed above called colInstances, which contains objects of class clsInstanceSpecific

Option Explicit
Public DayRng As Range
Public SectionRng As Range
Public TxtRng As Range
Public DaySectionRng As Range
Public TermRng As Range
Public OrderRng As Range
d) I’ve had success creating the TxtLabel, and IDCommand properties (controls) on an existing userform.

Problem:
I would like be able to create a clsSection Object
Option Explicit
Public Pieces As Collection
Public PieceFrm As New PieceTemplateForm
Public ID As String
Public Title As String
Public Line As Integer
Public DisplayHeader As Boolean
Public CenterHeader As Boolean
Public Exclude As Boolean
for each section, based on a template form called PieceTemplateForm, and then collect all of the clsSection objects into a collection called AllSections.

The clsFrmPiece was my attempt at adapting your code
 Public WithEvents uf As UserForm
 Private mtf As PieceTemplateForm

 Sub MyInitialize()
 Set mtf = New PieceTemplateForm
 Dim l As MSForms.Label

 mtf.Caption = "Instance of MyTemplateForm"
 Set l = mtf.Controls.Add("Forms.Label.1", "Description", True)
 l.Caption = "This is a simple blank form. I can do whatever " & _
 "I wish via code... Click the form!"
 l.AutoSize = True
 ' mtf.Show
 Set uf = mtf
 Unload mtf
 Set mtf = Nothing
 Set uf = Nothing
 End Sub

 Private Sub uf_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 With mtf.Controls("Description")
 .Left = X
 .Top = Y
 End With
 End Sub


 Private Sub uf_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 mtf.Caption = X & ", " & Y
 End Sub

I also have a Text Label Object clsCtlTxtLbl
Option Explicit
Public WithEvents TxtLabelGrp As MSForms.Label
Private Sub TxtLabelGrp_Click()
MsgBox "clicked me "
End Sub
And an ID Command Button Object clsCtlIDCommand
Option Explicit
Public WithEvents IDCommandGrp As MSForms.CommandButton
Private Sub IDCommandGrp_Click()
Dim sID As String
sID = IDCommandGrp.Caption
EditRecForm.Show
EditRecForm.IDLbl.Caption = sID
End Sub

My Routine: Here is my routine for setting everything up. It’s in a regular module.

Public fn As WorksheetFunction
Sub EstablishSections()
Dim TempFrm(1 To 5) As New PieceTemplateForm ' Array, only because I've not had success
 ' creating multiple instances of TmpFrm otherwise

Dim AllSections As New Collection ' Section Collection (Each record represents the Info
Dim recSection As New clsSection ' on one Sheet, P1, P2 etc.
Dim colPieces As New Collection ' Collection of records within AllSections/
Dim recPiece As New clsPiece
Dim ColSpecificInstances As New Collection ' Year Specific Instances, See G21 of Sheet P1 for an Expl.
Dim recInstance As New clsInstanceSpecific

Dim LastRow, LastCol, iFirstRow, iFirstCol, SecCnt, PieceCnt, InstCnt As Integer
Dim PiecesRng As Range ' Designates the cells on SectionWs sheet corresponding
Dim SectionWs, SettingsWs As Worksheet ' to the pieces for a particular section
Dim sSectionName As String
Dim ctl As Control
Dim IDWidth, iLeft, iTop, iHSpace, iVSpace, iMinHeight, iTxtWidth As Integer
Dim iMinFormWidth, iMinFormHeight, i, j As Integer
Dim PieceFrm As UserForm
Set fn = Application.WorksheetFunction
Set SettingsWs = ThisWorkbook.Worksheets("Settings") ' Will reside in a separate WS later. See Settings WS for expl.
iFirstRow = 21 ' The Year specific Instances, recPiece.colInstances start at this row
iFirstCol = 7 ' First recPiece starts at this column of SectionWs worksheet

For SecCnt = 2 To SettingsWs.Range("C65535").End(xlUp).Row ' Loop through each "Section" as listed on on SettingsWs

 sSectionName = SettingsWs.Cells(SecCnt, 3).Value
 Set SectionWs = Worksheets(sSectionName)

 LastRow = SectionWs.Range("A65535").End(xlUp).Row
 LastCol = SectionWs.Range("IV1").End(xlToLeft).Column

 Set PiecesRng = SectionWs.Range(SectionWs.Cells(1, 7), SectionWs.Cells(LastRow, LastCol))
' *****************************************************************************
' ***** Pull Pieces Information into Collection of clsPiece Objects ******
For PieceCnt = 1 To PiecesRng.Columns.Count
 With recPiece
 Set .IDRng = PiecesRng(1, PieceCnt)
 Set .TxtRng = PiecesRng(2, PieceCnt)
 Set .NumUsesRng = PiecesRng(3, PieceCnt)
 Set .RnkUsesRng = PiecesRng(4, PieceCnt)
 Set .LastUseRng = PiecesRng(5, PieceCnt)
 Set .OrderRng = PiecesRng(6, PieceCnt)
 Set .RetiredRng = PiecesRng(7, PieceCnt)
 End With

' Collect The Specific Instances for this Piece (See G21 of P1 sheet for an explanation)
 For InstCnt = iFirstRow To PiecesRng.Rows.Count
 With recInstance
 Set .DayRng = PiecesRng(InstCnt, 1).Offset(0, -6)
 Set .SectionRng = PiecesRng(InstCnt, 1).Offset(0, -5)
 Set .DaySectionRng = PiecesRng(InstCnt, 1).Offset(0, -4)
 Set .TermRng = PiecesRng(InstCnt, 1).Offset(0, -3)
 Set .OrderRng = PiecesRng(InstCnt, 1).Offset(0, -2)
 .DaySectionRng.Value = Format(.DayRng.Value, "mmmm dd yyyy") & ":" & .SectionRng.Value
 Set .TxtRng = PiecesRng(InstCnt, PieceCnt)
 End With
 ColSpecificInstances.Add recInstance, recInstance.DaySectionRng.Value
 Set recInstance = Nothing
 Next InstCnt

 Set recPiece.colInstances = ColSpecificInstances
 colPieces.Add recPiece, recPiece.IDRng.Value
 Set ColSpecificInstances = Nothing
 Set recPiece = Nothing
 Set recInstance = Nothing
Next PieceCnt

'***************** Set Up Piece Form *******************
iLeft = 10
IDWidth = 25
iTxtWidth = 378
iHSpace = 10
iVSpace = 10
iMinHeight = 10
iMinFormWidth = 500
iMinFormHeight = 300
iTop = iVSpace

Set recSection.Pieces = colPieces
Set recSection.PieceFrm = TempFrm(SecCnt)
Set colPieces = Nothing
 recSection.PieceFrm.Caption = sSectionName
 recSection.PieceFrm.Width = iTxtWidth
 recSection.PieceFrm.Height = fn.Max(ActiveWindow.Height, iMinFormHeight)
 recSection.PieceFrm.ScrollBars = fmScrollBarsVertical

For Each recPiece In recSection.Pieces
With recPiece
 ' Piece ID Label
 Set recPiece.IDCommand.IDCommandGrp = recSection.PieceFrm.Controls.Add("forms.CommandButton.1")
 .IDCommand.IDCommandGrp.Top = iTop
 .IDCommand.IDCommandGrp.Left = iLeft
 .IDCommand.IDCommandGrp.Height = iMinHeight
 .IDCommand.IDCommandGrp.Width = IDWidth
 .IDCommand.IDCommandGrp.Caption = .IDRng.Value

 ' Piece Text Label
 Set recPiece.TxtLabel.TxtLabelGrp = recSection.PieceFrm.Controls.Add("forms.Label.1")
 .TxtLabel.TxtLabelGrp.Top = iTop
 .TxtLabel.TxtLabelGrp.Left = iLeft + IDWidth + iHSpace
 .TxtLabel.TxtLabelGrp.Height = iMinHeight
 .TxtLabel.TxtLabelGrp.Width = recSection.PieceFrm.Width - 3 * iHSpace - IDWidth
 .TxtLabel.TxtLabelGrp.Caption = .TxtRng.Value
 .TxtLabel.TxtLabelGrp.AutoSize = True

 iTop = iTop + .TxtLabel.TxtLabelGrp.Height + iVSpace
End With
Next recPiece

recSection.PieceFrm.ScrollHeight = iTop + 20
recSection.PieceFrm.Show
recSection.ID = SettingsWs.Cells(SecCnt, 3).Value
recSection.Title = SettingsWs.Cells(SecCnt, 4).Value
recSection.Line = SettingsWs.Cells(SecCnt, 5).Value
recSection.DisplayHeader = SettingsWs.Cells(SecCnt, 6).Value
recSection.CenterHeader = SettingsWs.Cells(SecCnt, 7).Value
recSection.Exclude = SettingsWs.Cells(SecCnt, 8).Value

MsgBox (recSection.Pieces.Count & " records and " & Chr(10) & _
 recSection.Pieces(1).colInstances.Count & " 'Yr Specific Instances' in " & _
 recSection.Pieces(1).IDRng.Value & Chr(10) & " from sheet " & _
 recSection.ID & ", which is the " & recSection.Title & " Section.")

AllSections.Add recSection, recSection.ID
MsgBox AllSections(1).PieceFrm.Caption
Set recPiece = Nothing
Set recSection = Nothing

Next SecCnt

End Sub

Goals: (In order of decreasing importance to me at the moment)
i) A userform for each section is always loaded and accessible in the background.
ii) Clicking on the Command Button on the Section Form allows an EditRecord form to load and show, allowing the user to edit information about the particular piece corresponding to that command button (IDCommand of clsPiece)
iii) When the EditRecord form is closed, I would like all changes to be reflected in clsPiece, and hence the appropriate cells on the Section Worksheet.
iv) Allow me to manipulated pieces on the PieceFrm (e.g. move them up or down, reorder them, change the date for specific uses, only show pieces for a particular use etc.)

Future Goals:
v) A Later Goal Display Forms
I. Daily Lesson Plan (Display all pieces for a particular day and section)
II. Weekly Overview (Brief representation of the Lesson Plans for the Week)
III. Month Overview (Brief representation of the Lesson Plans for the Year)
IV. Displays by Section (e.g. List all homeworks for a unit)

Goals in a Land Far Far Away:
vi) Tools for comparing Lessons for year to year.
vii) tracking of student data such as attendance, tardiness, parent contact, discipline
vii) creating an excel menu structure for selecting classes, sections, semester, and
accessing various features.

PA HS Teacher · Jul 20, 2006

Tom,
Thank you for sharing your spreadsheet. It is very didactic, but I am trying to leave the VBE locked. As always, thank you for the help. I have saved this file away for another project, another day.

~ Dan

Legacy 98055 · Jul 20, 2006

PA HS Teacher Frm Obj.zip

Sorry Dan. I have some code in Outlook that was supposed to post your file but it errored out. Just got home from work. Anyway, Dan's file is above. I'll take a look at it later...

Legacy 98055 · Jul 20, 2006

BTW. I am not affiliated with Mr Excel. I only post here. The email address is just for my own organizational reference...

PA HS Teacher · Jul 20, 2006

Tom,

Take your time, It's a personal project for me, so I don't suffer from any pressure's or deadline's from above. It's the largest of my personal pet projects for the summer. I was hoping to finish Goal i) over the next week or two. I have numerous other projects Excel and otherwise to keep me busy. Thanks again for having a look at it, and for your creativity in providing a means for posting files on this board.

~ Dan

[edit] I just found this site today which lets you store and share up to
1 GB of files for free! Here's a link to an unzipped version of my file
http://www.box.net/public/b96rioc39f

Legacy 98055 · Jul 22, 2006

Hi Dan.

I have looked at your file and will need to take several more glances before I can get the big picture.

Just wanted you to know that I have not forgotten your post.

If anybody else wants to take a look and offer some suggestions...

PA HS Teacher Frm Obj.zip

Norie · Jul 22, 2006

Dan

Why not just use Access or another database application?

PA HS Teacher · Jul 22, 2006

Norie,
Access would be great for this task, but most school computers do not have Access, and I want this file to be functional on any PC. Virtually all school computers have Excel, maybe 10% has access. (I also to not have Access at home.)

Tom, (and anyone else interested)
Please do not feel obligated to digest all of my previous post. This project is a culmination of several years of thinking, and I don't won't to force it on anyone. Let me distill my question into a simple problem that represents my question.

Objective:
Create Multiple Instances of a variable/ class called clsMyObject, based on information contained in the sheet shown below. MyObject contains a userform, controls, and other "regular" variables.ufAs UserForm
MyProperty1AsString
MyProperty2AsInteger
MyTextBoxesAs Collection

Book1

A

B

C

D

E

F

G

H

1

MyObject

MyForm

MyProperty1 as String

MyProperty2 as Integer

MyTextBox1

MyTextBox2

MyTextBox3

As many as 100 Textboxes

2

MyObject1

MyForm1

I am Form 1

1

x

3

MyObject2

MyForm2

I am Form 2

2

x

4

MyObject3

MyForm3

I am Form 3

3

x

5

MyObject4

MyForm4

I am Form 4

4

x

Sheet1

I will not know until runtime how many instances to create. If I was to run the desired code on the example sheet below, 4 instances of MyObject would be created.

Instance 1 would include a userform with 4 textboxes, each containing the letter x.

Instance 2 would contain a form with 3 textboxes.

Instance 3 would contain a form with 2 textboxes.

Instance 4 would contain a form with 1 textbox.

The userforms should be loaded simultaneously in the background.

The template form for uf could be a blank form. The only reason for a template form at all is my desire to create the forms w/out accessing the VBE via code.

Thank you for entertaining my problem.

Norie · Jul 22, 2006

Dan

Thought that might be your response, just wanted to put forward another idea.

I'll download the attachments, and have a thorough read of the thread when I have more time.

Create Userform on the Fly?

PA HS Teacher

Well-known Member

Legacy 98055

Guest

Excel Facts

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Legacy 98055

Guest

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Legacy 98055

Guest

Norie

Well-known Member

PA HS Teacher

Well-known Member

Norie

Well-known Member

Similar threads

Forum statistics

Share this page

Create Userform on the Fly?

PA HS Teacher

Well-known Member

Legacy 98055

Guest

Excel Facts

PA HS Teacher

Well-known Member

PA HS Teacher

Well-known Member

Legacy 98055

Guest

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Legacy 98055

Guest

Norie

Well-known Member

PA HS Teacher

Well-known Member

Norie

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