ANDYH
11-25-2008, 09:36 AM
:banghead: 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
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