READING FROM DYNAMIC FORM

[ANDYH]

Im new to VBA coding. I've been piecing together some code to use a form to select colums form one spreadsheet and rearange them to another I've got it close but when I enter data on the form I can't figure out how to read the data from the active form I generated. I'm using office 2007 and have atached the file for your review. Any help will be aproeciated.

I haven't done much with the spreadsheet manipulation because I can't read the data for my text box controls.

This is what I use to generate the form and form code.....

Sub MakeUserForm1()
Dim TempForm As Object
Dim NewButton As MSForms.CommandButton
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim myCheckBox As Control
Dim X As Integer
X = 0
Dim Row As Integer
Row = 0
Dim Col As Integer
Col = 20
Dim Line As Integer
Dim MyScript(4) As String
Dim CName As String
Dim Cell As Range

'This is to stop screen flashing while creating form
'Application.VBE.MainWindow.Visible = False

count = WorksheetFunction.CountA(Range("A1:BZ1"))
'MsgBox count

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("UserForm1")
VBProj.VBComponents.Remove VBComp

'Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)




'Create the User Form

With TempForm
.Properties("Caption") = " Select Columns"
.Properties("Height") = count * 13 + 100
.Properties("Width") = 400
End With

'Add textboxs to Form

For Each Cell In Range("A1", Range("IV1").End(xlToLeft))
If Cell <> "" Then
Set mytextBox = TempForm.Designer.Controls.Add("Forms.textbox.1")
X = X + 1
Row = Row + 20
'CName = Cell.Value
'MsgBox Name
With mytextBox
.Name = "Box" & X
.Top = 5 + (12 * X)
.Left = 40
.Width = 20
.Height = 12
'.Text = "A" & X
.Font.Size = 7
.Font.Name = "Tahoma"
.BorderStyle = fmBorderStyleSingle
.SpecialEffect = fmSpecialEffectFlat
End With
End If
Next Cell

X = 0


'Add labels to Form

For Each Cell In Range("A1", Range("IV1").End(xlToLeft))
If Cell <> "" Then
Set mylabel = TempForm.Designer.Controls.Add("Forms.label.1")
X = X + 1
Row = Row + 20
'CName = Cell.Value
'MsgBox Name
With mylabel
.Name = "FieldLabel" & X + 1
.Caption = " Type a letter associated with column where you want to see the " & Cell.Value & " data."
.Top = 5 + (12 * X)
.Left = 65
.Width = 300
.Height = 12
.Font.Size = 7
.Font.Name = "Tahoma"
.BorderStyle = fmBorderStyleSingle
.BackColor = &HFFFFFF
End With
End If
Next Cell
X = 0

' Add a CommandButton
Set NewButton = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewButton
.Caption = "Click to create new Spreadsheet"
.Left = 140
.Width = 140
.Top = 40 + 12 * count
End With

' Add an event-hander sub for the CommandButton
With TempForm.CodeModule

' ** Add/change next 5 lines
' This code adds the commands/event handlers to the form
Y = .CountOfLines
.InsertLines Y + 1, "Sub CommandButton1_Click()"
.InsertLines Y + 5, " Dim ctl As MSForms.Control"
.InsertLines Y + 9, " Dim sData As String"
.InsertLines Y + 13, " Dim K As Integer"
.InsertLines Y + 19, " For Each ctl In UserForm1.Controls"
.InsertLines Y + 25, " If TypeName(ctl) = ""TextBox"" Then"
.InsertLines Y + 30, " MsgBox ""Control Name "" & ctl.name"
.InsertLines Y + 35, " MsgBox ""Control Value "" & ctl.value"
.InsertLines Y + 40, " 'K = Right(ctl.Name, Len(ctl.Name) - 3)"
.InsertLines Y + 50, " 'ActiveSheet.Columns(K).EntireColumn.Copy"
.InsertLines Y + 60, " End If"
.InsertLines Y + 70, " Next ctl"
.InsertLines Y + 80, " Unload Me"
.InsertLines Y + 100, "End Sub"
End With
'MsgBox "pause"
'MsgBox TempForm.Name
VBA.UserForms.Add(TempForm.Name).Show


End Sub

[Bob Phillips]

You have a real problem with forms created on the fly, as it means it has no event code. You can add event code, but why bother? Or you could add code to get all of the data from the form, but you lose immediate validation, and again why bother.

It is far better to design a form with all necessary controls on it, and launch that.

[ANDYH]

I don't know if you ran the code but I was hoping it is something simple like setting focus to the form that is one the screen. or saving the text box entries to the newly created form before I collect the values. Any thoughts along those lines?

Thanks

[Bob Phillips]

I did run it and all it did was give me a load of messages.

So what do you think it's doing?

[ANDYH]

The dynamic form is created based on the colum headings on the source worksheet. From there I want to put a colum letter in the associated form's text box to indicate what colum I want the colum data displayed on the new worksheet. I enter letters in the textboxs as needed but when I read the ctl.vale or ctl.text it does not return the letters displayed there. I beleive it is reading the original form that has blanks by design. I need a way to ether update the form to include the letter values before I read them or change focus to the ctl with the values before i read through them. This probably doesn't present a clear picture of what I'm trying to do but I'm not sure how to describe it any better.

Thanks

[ANDYH]

If you are getting errors you may have to load a couple of references

Add Microsoft Visual Basic for Applications Extensibility 5.3

and

Microsoft Forms 2.0 Object Library at c:\windows\system32\fm20.dll

I beleive they are need toread the older language used inthe code.

[Bob Phillips]

I didn't say I was gettin g errors, just messages.

I don't need either of those references, Forms is loaded as a default, Extensibility isn't needed.

If YOU run that code, you will see that all it does is to iterate the textboxes and output the control name and the control value, only it fails to get the value.

I re-iterate what I said earlier.