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?

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" wrote:

> 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?
>

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

On Oct 22, 9:00*am, redeagle <redea...@discussions.microsoft.com>
wrote:
> 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" wrote:
> > 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?

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



On Oct 23, 8:57*am, "caveman.savant" <caveman.sav...@gmail.com> wrote:
> 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
>
> On Oct 22, 9:00*am, redeagle <redea...@discussions.microsoft.com>
> wrote:
>
>
>
> > Hi caveman.savant-
>
> > You should be able to do what you want. *The basic algorithm would beto
> > 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" wrote:
> > > 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?- Hide quoted text -
>
> - Show quoted text -

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


On Oct 23, 8:57*am, "caveman.savant" <caveman.sav...@gmail.com> wrote:
> 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
>
> On Oct 22, 9:00*am, redeagle <redea...@discussions.microsoft.com>
> wrote:
>
>
>
> > Hi caveman.savant-
>
> > You should be able to do what you want. *The basic algorithm would beto
> > 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" wrote:
> > > 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?- Hide quoted text -
>
> - Show quoted text -

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?

On Oct 23, 6:43*am, Susan <bogenex...@aol.com> wrote:
> 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
>

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



On Oct 23, 10:02*am, wutzke <michael.wut...@gmail.com> wrote:
> 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?
>
> On Oct 23, 6:43*am, Susan <bogenex...@aol.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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

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