asked on

Dynamic Forms and textboxes VBA

I need to be able to dynamically create a form based on user input and then be able to references the textboxes created to use for logic decisions and to use the values in another form.

I can create the form dynamically but i cannot reference the text boxes. Basically the first message box asks for course name, then it asks if course is modularised (more than 1 module) if the user says yes then it asks for a number, the number entered is how many CourseName, GroupID and SessionID text boxes are created. EG: if it is 3
then the form will haev CourseName1, GroupID1, SessionID1, CourseName2, GroupID2, SessionID2, CourseName3, GroupID3, SessionID3.

I need to be able to say something like if GroupID(i) is "" then do something else do something else. Also would like to be able to use the values of each text box for other actions

All assistance accepted, i nee dthis Urgently so will assign top points

Sub MakeUserForm()
    Dim TempForm As Object
    Dim NewButton As MSForms.CommandButton
    Dim NewLabel As MSForms.Label
    Dim CourseLabel As MSForms.Label
    Dim SessionLabel As MSForms.Label
    Dim GroupLabel As MSForms.Label
    Dim frmLabel As MSForms.Label
    Dim NewTextBox1 As MSForms.TextBox
    Dim NewTextBox2 As MSForms.TextBox
    Dim NewTextBox3 As MSForms.TextBox
    Dim NewOptionButton As MSForms.OptionButton
    Dim NewCheckBox As MSForms.CheckBox
    Dim Line As Integer
    Dim MyScript(4) As String
    Dim Y
    
 
 
    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False
 
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
    
    
    
    spacer = "_"
    modM = ""
    'Create the User Form
    With TempForm
        .Properties("Caption") = "Redirect Pages"
        .Properties("Width") = 800
        .Properties("Height") = 600
    End With
 
   
course = InputBox("Please enter the name of the Assessment (eg:ICP100)")  'user input course name
If course <> "" Then
 z = MsgBox("Is this course modularised?.", vbYesNo + vbQuestion, "Modularised.")
    If z = vbYes Then
         z = InputBox("How many modules are in the course?.", 1)
         modM = "M"
         i = z - 1
    
     Set CourseLabel = TempForm.Designer.Controls.Add("Forms.Label.1")
        With CourseLabel
            .Name = "CourseLabel"
            .Caption = "Course"
            .Top = 90
            .Left = 20
            .Width = 200
            .Height = 18
            .Font.Size = 16
            .Font.Name = "Ariel"
            .BackColor = &HC0C000
        End With
 
    'Create 10 Labels
    
    For X = 0 To i
         Set NewTextBox1 = TempForm.Designer.Controls.Add("Forms.textbox.1")
        With NewTextBox1
            .Name = "CourseName" & X + 1
            .Value = course & spacer & modM & X + 1
            .Top = 120 + (20 * X)
            .Left = 20
            .Width = 200
            .Height = 20
            .Font.Size = 12
            .Font.Name = "Ariel"
        End With
    Next
 
 
 
    Set GroupLabel = TempForm.Designer.Controls.Add("Forms.Label.1")
        With GroupLabel
            .Name = "GroupLabel"
            .Caption = "GroupID"
            .Top = 90
            .Left = 250
            .Width = 150
            .Height = 18
            .Font.Size = 16
            .Font.Name = "Ariel"
            .BackColor = &HC0C000
        End With
 
    'Create 10 Text Boxes
    For X = 0 To i
        Set NewTextBox2 = TempForm.Designer.Controls.Add("Forms.textbox.1")
        With NewTextBox2
            .Name = "GroupID" & X + 1
            .Top = 120 + (20 * X)
            .Left = 250
            .Width = 150
            .Height = 20
            .Font.Size = 12
            .Font.Name = "Ariel"
            .BorderStyle = fmBorderStyleSingle
            .SpecialEffect = fmSpecialEffectFlat
        End With
    Next
 
 
 
    Set SessionLabel = TempForm.Designer.Controls.Add("Forms.Label.1")
        With SessionLabel
            .Name = "SessionLabel"
            .Caption = "SessionID"
            .Top = 90
            .Left = 450
            .Width = 150
            .Height = 18
            .Font.Size = 16
            .Font.Name = "Ariel"
            .BackColor = &HC0C000
        End With
 
 
 
    'Create 10 Text Boxes
    For X = 0 To i
        Set NewTextBox3 = TempForm.Designer.Controls.Add("Forms.textbox.1")
        With NewTextBox3
            .Name = "SessionID" & X + 1
            .Top = 120 + (20 * X)
            .Left = 450
            .Width = 150
            .Height = 20
            .Font.Size = 12
            .Font.Name = "Ariel"
            .BorderStyle = fmBorderStyleSingle
            .SpecialEffect = fmSpecialEffectFlat
        End With
    Next
 
Else
 
 'Create 10 Labels
    
        Set CourseLabel = TempForm.Designer.Controls.Add("Forms.Label.1")
        With CourseLabel
            .Name = "CourseLabel"
            .Caption = "Course"
            .Top = 90
            .Left = 20
            .Width = 200
            .Height = 18
            .Font.Size = 16
            .Font.Name = "Ariel"
            .BackColor = &HC0C000
        End With
         
         
         Set NewTextBox1 = TempForm.Designer.Controls.Add("Forms.textbox.1")
        With NewTextBox1
            .Name = "CourseName"
            .Value = course
            .Top = 120
            .Left = 20
            .Width = 200
            .Height = 20
            .Font.Size = 12
            .Font.Name = "Ariel"
        End With
   
 
 
 
    Set GroupLabel = TempForm.Designer.Controls.Add("Forms.Label.1")
        With GroupLabel
            .Name = "GroupLabel"
            .Caption = "GroupID"
            .Top = 90
            .Left = 250
            .Width = 150
            .Height = 18
            .Font.Size = 16
            .Font.Name = "Ariel"
            .BackColor = &HC0C000
        End With
 
    'Create 10 Text Boxes
   
        Set NewTextBox2 = TempForm.Designer.Controls.Add("Forms.textbox.1")
        With NewTextBox2
            .Name = "GroupID"
            .Top = 120
            .Left = 250
            .Width = 150
            .Height = 20
            .Font.Size = 12
            .Font.Name = "Ariel"
            .BorderStyle = fmBorderStyleSingle
            .SpecialEffect = fmSpecialEffectFlat
        End With
  
 
 
 
    Set SessionLabel = TempForm.Designer.Controls.Add("Forms.Label.1")
        With SessionLabel
            .Name = "SessionLabel"
            .Caption = "SessionID"
            .Top = 90
            .Left = 450
            .Width = 150
            .Height = 18
            .Font.Size = 16
            .Font.Name = "Ariel"
            .BackColor = &HC0C000
        End With
 
 
 
    'Create 10 Text Boxes
    
        Set NewTextBox3 = TempForm.Designer.Controls.Add("Forms.textbox.1")
        With NewTextBox3
            .Name = "SessionID"
            .Top = 120
            .Left = 450
            .Width = 150
            .Height = 20
            .Font.Size = 12
            .Font.Name = "Ariel"
            .BorderStyle = fmBorderStyleSingle
            .SpecialEffect = fmSpecialEffectFlat
        End With
   End If
   End If
   
  
 
    ' Add a CommandButton
Set NewButton = TempForm.Designer.Controls _
.Add("forms.CommandButton.1")
With NewButton
.Caption = "Create Redirect Page"
.Width = 100
.Left = 500
.Top = 145 + (20 * X)
 
End With
' Add an event-hander sub for the CommandButton
With TempForm.CodeModule
' ** Add/change next 5 lines
' This code adds the commands/event handlers to the form
Y = .CountOfLines
.insertLines Y + 1, "Sub CommandButton1_Click()"
.insertLines Y + 5, "Looping"
.insertLines Y + 9, "Unload Me"
.insertLines Y + 10, "End Sub"
End With
 
    'Show the form
    VBA.UserForms.Add(TempForm.Name).Show
 
 
 
 
    'Delete the form (Optional)
    ThisWorkbook.VBProject.VBComponents.Remove TempForm
 
End Sub

Open in new window

Rory Archibald

You can use:
tempForm.Controls("GroupID" & i)
to refer to a specific GroupId box. If you have a lot of controls that basically need to do the same thing, you might look at using some WithEvents variables to trap their events.
Regards,
Rory

MrMatty

ASKER

Rory thanks for the response, only problem is i need to reference the GroupID from a function that is called whenthe user clicks the command button

' This code adds the commands/event handlers to the form
Y = .CountOfLines
.insertLines Y + 1, "Sub CommandButton1_Click()"
.insertLines Y + 5, "Looping"
.insertLines Y + 9, "Unload Me"
.insertLines Y + 10, "End Sub"
End With

can i still use the tempForm.Controls as i have tried biut it isn't working

Let me know if you need to see all of the code

Thanks
Matt

Rory Archibald

Do you actually need the whole userform created at run time? Can you not simply have a dynamic number of textboxes?
Also, what exactly do you need that commandbutton code to do - I'm not really seeing where the problem lies in referring to the controls.

MrMatty

ASKER

I am happy to have a static form (excel) and have the textboxes created dynamically.

OK the intention of this app is to created redirect pages for assessments that are created in the organisation, the command button calls a looping function that iterates through to max number of mods and creates the html pages based on the user input earlier. So if the user says the name of the assessment is xyz100 and it has 5 modules then it creates 5 html pages with a a standard message (assessment xyz110_M1 will be available soon, assessment xyz110_M2 will be available soon, ...assessment xyz110_M5 will be available soon, etc) these are place holder pages so other areas can package there products with a link inside, then when the assessment becomes available, i need to be able to overwrite the html pages with the correct link which embeds a GroupID and SessionID in the link.

This means the client doesn't have to repackage their product as their link remains the same, we just change the info on the redirect page.

The 3 text boxes created dynamically are courseName, GroupID and SessionID, i need to be able to reference these text boxes after they have been created so that i can use a logic decision to determine if GroupID and SessionID are blank then it creates the placeholder html pages (assessment xyz110_M1 will be available soon, etc) if the GroupID and SessionID <> blank then it creates the html pages adding the value of GroupID and SessionID .

it sounds complicated i know but isn't really I am probably approaching it the wrong way, if you can assist me in getting the code to create dynamic text boxes(for excel form) and store these values in an array (or something along those lines) that can be used later in some decision logic i would greatly appreciate it.

Cheers Matt

Rory Archibald

I'm afraid I still don't see the issue. You know how many controls there are (the value of i) so why can't your code simply loop using the syntax I gave you earlier? What am I missing?

MrMatty

ASKER

OK, it might be easier if you see the workbook. I will attach a copy with some modules removed (for organisational security)

The issue is with Module 1, I am new to vba so I am probably making a very simple mistake (from the confusion on your end)

I have attached the workbook, let m ek now if you can be of any help

Thanks for taking the time I am really going around in circles now

Cheers Matt
Assessment-AutomationV1.2-EE.xls

Rory Archibald

Ok I'll try and take a look today

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrMatty

ASKER

Super effort, you have gone above and beyound. Thanks you for your excellent help and Tutelage