userforms - making label visible :: newbie

[pnguine]

Hi all

I need help. I have every book on vba our local library has and have
been googling all weekend and my label still doesn't show up.

I'm trying to programmatically create an entire userform. The books all
say 'insert userform; drag and drop controls from the toolbox; etc.' but
I don't want to follow that approach - I want to be able to create it
entirely in code. The form appears when I run the macro and I get no
errors but, like I said, the label is nowhere to be found. This is just
the beginning of a form that will eventually contain about three dozen
controls on the first page. Here is what I have (mostly copied from
'eng-tips' and 'www.sas-tips.com'):

---------------------------------------------------------
Option Explicit



' procedure to programmatically create userform
' from eng-tips on web
'
Sub makeJobForm()

Dim jobForm As Object
Dim formName As String
'Dim newButton As MSForms.CommandButton
Dim lbl As MSForms.Label
'Dim textLocation As Integer
'Dim X As Integer

'locks spreadsheet and speeds up form processing
Application.VBE.MainWindow.Visible = False
Application.ScreenUpdating = False

' create the userform
Set jobForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

'set properties
With jobForm
.Properties("Caption") = "Job Quote Entry"
.Properties("Width") = 387
.Properties("Height") = 462
'.Properties("Name") = "frm_jobQuote"
End With
'formName = tempForm.Name
formName = "frm_jobQuote"
'jobForm.Name = "jobform1"

' add a label
Dim lbl1 As MSForms.Label
Dim lbl1Name As String

jobForm.Designer.Controls.Add ("Forms.Label.1")
Set lbl1 = jobForm.Designer.Controls.Add("Forms.Label.1")
With lbl1
.Caption = "Label1"
.Left = 12
.Top = 6
.Width = 360
.Height = 24
.BorderStyle = fmBorderStyleSingle
End With

' show the form
VBA.UserForms.Add(formName).Show


End Sub

Sub testForForm()

Dim frm As UserForm

'MsgBox ("test")
For Each frm In VBA.UserForms
'If frm.Caption = "Job Quote Entry" Then
MsgBox (frm.Caption)
'End If
Next frm

End Sub

-------------------------------------------------------------------

Thanks

Also can anyone recommend a _really_ good book on coding vba - not one
that tells you to drag and drop everything in the vbe gui then add some
event handlers? Something that explains why I need to make a reference
to a gui editor before I can generate an on-screen form (or am I reading
this wrong)? And where do I find complete documentation for the
'Designer' object? Oh; is there a Faq for this group?

Thanks again

Phil Newcombe
Vancouver Canada

 

[Jonathan West]

Hi all

I need help. I have every book on vba our local library has and have been
googling all weekend and my label still doesn't show up.

I'm trying to programmatically create an entire userform. The books all
say 'insert userform; drag and drop controls from the toolbox; etc.' but I
don't want to follow that approach - I want to be able to create it
entirely in code. The form appears when I run the macro and I get no
errors but, like I said, the label is nowhere to be found. This is just
the beginning of a form that will eventually contain about three dozen
controls on the first page. Here is what I have (mostly copied from
'eng-tips' and 'www.sas-tips.com'):

What you appear to be trying to do is create a new userform within the VBA
project. I wouldn't advise trying that - it is all too likely to fail on
user machines because of security settings.

Instead, create a blank form called UserForm1 in your project. Then use the
following code

Sub makeJobForm()

Dim jobForm As UserForm1
Dim formName As String
'Dim newButton As MSForms.CommandButton
Dim lbl As MSForms.Label
'Dim textLocation As Integer
'Dim X As Integer


' create the userform
Set jobForm = New UserForm1

'set properties
With jobForm
.Caption = "Job Quote Entry"
.Width = 387
.Height = 462
'.Properties("Name") = "frm_jobQuote"
End With
'formName = tempForm.Name
formName = "frm_jobQuote"
'jobForm.Name = "jobform1"

' add a label
Dim lbl1 As MSForms.Label
Dim lbl1Name As String

Set lbl1 = jobForm.Controls.Add("Forms.Label.1")
With lbl1
.Caption = "Label1"
.Left = 12
.Top = 6
.Width = 360
.Height = 24
.BorderStyle = fmBorderStyleSingle
End With

' show the form
jobForm.Show
End Sub

That should get you going on the project.

 

[pnguine]

That should get you going on the project.

Thanks Jonathan; I'll try that as soon as I get my new box up (Athlon
4200, Asus MB, 1G ram - should be fun)