PDA

View Full Version : Solved: trying to dynamically create a userform



CatDaddy
06-20-2012, 01:31 PM
Sub make_userform1()
Dim TempForm
Dim NewTextBox As MSForms.TextBox
Dim NewLabel As MSForms.Label
Dim NewCommandButton1 As MSForms.CommandButton
Dim NewCommandButton2 As MSForms.CommandButton
Dim x As Integer
' Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False
' Create a Temp UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
' Add a Label
Set NewLabel = TempForm.Designer.Controls.Add("forms.label.1")
With NewLabel
.Width = 140
.Height = 20
.Left = 48
.Top = 10
.Caption = "Lotus Notes Password"
End With
' Add a TextBox
Set NewTextBox = TempForm.Designer.Controls.Add("forms.textbox.1")
With NewTextBox
.PasswordChar = "*"
.Width = 140
.Height = 20
.Left = 48
.Top = 30
End With
' Add the OK button
Set NewCommandButton1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton1
.Caption = "OK"
.Height = 18
.Width = 66
.Left = 126
.Top = 80
End With
' Add the Cancel button
Set NewCommandButton2 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton2
.Caption = "Cancel"
.Height = 18
.Width = 66
.Left = 30
.Top = 80
End With
End Sub


getting compile error user type not defined on
Dim NewTextBox As MSForms.TextBox

CatDaddy
06-20-2012, 01:38 PM
missing reference, never mind

Jan Karel Pieterse
06-20-2012, 10:23 PM
Why are you doing this like this if I may ask?

CatDaddy
06-21-2012, 08:50 AM
i'm using this userform basically as an inputbox but with the characters hidden to enter a password (PasswordChar = "*") which is a functionality the inputbox doesnt have, and i have to make this userform on multiple spreadsheets so i thought it would just save a lot of my time to automatically create it instead of playing with the stupid userform editor every time

Jan Karel Pieterse
06-22-2012, 04:28 AM
You do know you can export a form and import it in another project, or even simply drag the form in the Project explorer from one project to another?

mikerickson
06-22-2012, 07:17 AM
I did something like that once and discovered that repeatedly creating and deleting a userform increased the file size with each creation and deletion. The file got pretty big.

Jan Karel Pieterse
06-24-2012, 09:16 PM
I'm not suggesting to do that repeatedly, just to insert an existing form into an existing project in order to prevent redoing the same work.