Create Userform from Worksheet

[caveman.savant]

I'm not sure this can be done with VBA.
I would like to create a macro that would allow me to select a group
of cells from a worksheet and convert that selection into a userform.

starting with
ActiveCell.Offset(-3, -1).Range("A1:E4").Select

The 1st row and 1st Column would be Labels, so I would loop thru and
use the text to use for each caption.

The rest of the cells would become a textbox for numerical data.
Beside each textbox could be a spinner to select a value for the box.

Once the form is created I'd like an option to save it. Otherwise I
would use the dynamically created form to input data and return it the
the originally selected cells

Can Excel Macros create UserForms?
Anyone with an idea how to start?

 

[redeagle]

Hi caveman.savant-

You should be able to do what you want. The basic algorithm would be to
loop through your selection with a For/Each (For Each cell in Selection...)
to get the value of each cell. Then you could declare and instanciate a
userform and the controls. Once everything is defined you could display the
form.

Something like:

Sub Main()

Dim myCell as Variant
Dim myLabel as Label

'Declare and instantiate new Userform and set properties
Dim uf As UserForm
Set uf = New UserForm
uf.Caption = "my dynamic form"

For Each myCell in Selection
Set myLabel = New Label
myLabel.Caption = myCell
uf.Controls.Add myLabel
Next

'Finally display your form
uf.Show

End Sub

.... my syntax may not be right, but that is the idea.

John

 

[caveman.savant]

Thanks.
Still having problem calling a new form.
i found the following code but the AActiveDocument.VBProject part
fails

Sub BuildMyForm()
Set mynewform = _
ActiveDocument.VBProject.VBComponents.Add(vbext_ct_MSForm)
With mynewform
.Properties("Height") = 246
.Properties("Width") = 616
.Name = "HelloWord"
.Properties("Caption") = "This is a test"
End With
End Sub

 

[Susan]

using john walkenbach's book "excel 2000 power programming with vba" i
ammended your code slightly & it works for me:

'===============================
Sub BuildMyForm()

Dim MyNewForm As Object

Set MyNewForm = _
ThisWorkbook.VBProject.VBComponents.Add(3)
With MyNewForm
.Properties("Height") = 246
.Properties("Width") = 616
.Name = "HelloWord"
.Properties("Caption") = "This is a test"
End With

VBA.UserForms.Add(MyNewForm.Name).Show

End Sub
'==================================

susan

 

[Susan]

ps - some of the reason you were having trouble calling the form is
that "active document" is a WORD command, not excel.
you must've stolen the coding off a word document.
susan

 

[wutzke]

is that why I get the 1004 runtime error from
Set MyNewForm = _
ThisWorkbook.VBProject.VBComponents.Add(3)

is there a way to do it in excel?

 

[Susan]

the code i posted is excel code.
i don't know why you'd be getting a 1004 runtime error - it worked for
me.
i do know you can't step through the code, you have to just run it all
at once.
susan

 

[caveman.savant]

I tried this with out errors

Sub BuildMyForm()
Dim txtbx1 As msforms.TextBox
Set txtbx = UserForm1.Controls.Add("Forms.textbox.1")
With txtbx
.Name = "nOK"
.Value = Worksheets("Sheet1").Range("d3").Value
.BackColor = &H8000000D
.Font.Size = 8
End With

UserForm1.Show
End Sub

Now I just have to go back and figure out the selection loop to set
the textbox values

 

[caveman.savant]

To test this I have selected a range of cells


1 2 3
a red blue green
b yellow black orange
c white grey purple

using
Sub BuildMyForm()
Dim txtbx As msforms.TextBox
Dim myLabel As msforms.Label

Dim i As Integer
For i = 1 To Selection.Columns.Count
Set myLabel = UserForm1.Controls.Add("Forms.Label.1")
With myLabel
.Left = 24 * i
.Top = 4
.Width = 18
.Caption = Worksheets("Sheet1").Range("c3:h6").Cells(i).Value
.BackColor = &H8000000D
.SpecialEffect = fmSpecialEffectRaised
End With
Next i

For y = 0 To Selection.Rows.Count
For i = 1 To Selection.Columns.Count
Set txtbx = UserForm1.Controls.Add("Forms.textbox.1")
With txtbx
.Name = "nOK" & i
.Value = Worksheets("Sheet1").Range("c3:h6").Cells(i +
y).Value

.BackColor = &H8000000D
.Font.Size = 8
.Height = 18
.Left = 24 * i
.Top = 24 + (24 * y)
.Width = 18
.SpecialEffect = fmSpecialEffectBump
End With
Next i
Next y
UserForm1.Show
End Sub

i get whacky results