Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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" 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 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?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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" 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 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?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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 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 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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Userform from Worksheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: how to create label forms inside the worksheet not userform | Excel Programming | |||
Create a task from a userform | Excel Programming | |||
Can I create a userform in Excel to create an appointment in Outlo | Excel Programming | |||
Dynamically create userform | Excel Programming | |||
userform create | Excel Programming |