Can I program a userform via spreadsheet?
 

Can I create a spreadsheet and then "build" a userform from it?

Example:

Speadsheet1 has 5 columns

A B C D E
F G H
LabelName Type Title_name VarName TabOrder CrossRefSheet
CrossRefCol Style
Name TexBox 1 Name_ 1
data A
Name label 1
Address TexBox 2 Add_ 2
data B


Spreadsheet2 is called Data
It is build like:
Name Address

Here is what I want the form to look like:
----------------------------------------------------------
| Name: | | Address: | | |
|
|
|
|
----------------------------------------------------------

Here is what I think the code should flow like: but have no idea of the
syntax...

select spreadsheet1
recloop=0
do while recloop <= reccount
mLabelName = spreadsheet.A & recloop
mTYPE = spreadsheet.B & recloop
mMarker = spreadsheet.C & recloop
mStyle = spreadsheet.E & recloop
mTagOrder = spreadsheet.D & recloop
mWorkSheet = spreadsheet.F & recloop
mWorkCol = spreadsheet.G & recloop

if mType = "label"
..Caption = mLabelName
..Style = mStyle
create object. mLabelName

else if mType = "Textbox"

..Caption = mLabelName
..Style = mStyle
..Tag = mTagOrder
create object. mLabelName
mLabelName =end while
Textbox & mMarker = " worksheet " & mWorkSheet & mWorkCol
endif




end loop





TIA
Phil

Can I program a userform via spreadsheet?
 

Phillips,

You need to show the userform first and get that to pull the first data from
the worksheet.
You'll need a button on the form to get the next row of data into it (until
you reach the end)
The code behind the form would be something like:

Public MyRow as Integer
MyRow = 2 'To avoid headers

Private Sub Myform_Initialize()
TextBox1.Text = Sheets("Data").Range("A2").Value '1st Name
TextBox2.Text = Sheets("Data").Range("B2").Value '1st Address
End Sub

Private Sub NextBtn_Click()
MyRow = MyRow +1
If MyRow <= Sheets("Data").Last Used.Rows.Count Then 'Is it past the last
row?
TextBox1.Text = Sheets("Data").Range("A" & MyRow).Value 'Name
TextBox2.Text = Sheets("Data").Range("B" & MyRow).Value 'Address
Myform.Repaint
End If
End Sub


HTH
Henry


"Phillips" wrote in message
news:S1cxb.313809$HS4.2778403@attbi_s01...
Can I create a spreadsheet and then "build" a userform from it?

Example:

Speadsheet1 has 5 columns

A B C D
E
F G H
LabelName Type Title_name VarName TabOrder
CrossRefSheet
CrossRefCol Style
Name TexBox 1 Name_ 1
data A
Name label 1
Address TexBox 2 Add_ 2
data B


Spreadsheet2 is called Data
It is build like:
Name Address

Here is what I want the form to look like:
----------------------------------------------------------
| Name: | | Address: | |
|
|
|
|
|
----------------------------------------------------------

Here is what I think the code should flow like: but have no idea of the
syntax...

select spreadsheet1
recloop=0
do while recloop <= reccount
mLabelName = spreadsheet.A & recloop
mTYPE = spreadsheet.B & recloop
mMarker = spreadsheet.C & recloop
mStyle = spreadsheet.E & recloop
mTagOrder = spreadsheet.D & recloop
mWorkSheet = spreadsheet.F & recloop
mWorkCol = spreadsheet.G & recloop

if mType = "label"
.Caption = mLabelName
.Style = mStyle
create object. mLabelName

else if mType = "Textbox"

.Caption = mLabelName
.Style = mStyle
.Tag = mTagOrder
create object. mLabelName
mLabelName =end while
Textbox & mMarker = " worksheet " & mWorkSheet & mWorkCol
endif




end loop





TIA
Phil

Can I program a userform via spreadsheet?
 

Create a blank form, then add code similar to below
Use the Visual Basic help for Controls.Add to get names
needed for other control types. Not that you'll need to
specify
the dimensions of the controls. If the number of controls
will "overflow" the form length, then add a ScrollBar to
the form as well.

Private Sub UserForm_Initialize()
Const mLabelName = 0
Const mType = 1
Const mMarker = 2
Const mName = 3
Const mTabOrder = 4
Const mWorkSheet = 5
Const mWorkCoL = 6
Const mLeft = 7
Const mTop = 8
Const mWidth = 9
Dim rng As Range
Dim ctrl As Control

Set rng = ThisWorkbook.Worksheets("Speadsheet1").Range
("A2")
Do While Not IsEmpty(rng)
Set ctrl = Me.Controls.Add("Forms." & rng.Offset
(0, mType) & ".1")
With ctrl
.TabIndex = rng.Offset(0, mTabOrder)
.Left = rng.Offset(0, mLeft)
.Width = rng.Offset(0, mWidth)
.top = rng.Offset(0, mTop)
.Height = 18

Select Case rng.Offset(0, mType)
Case "TextBox"
.ControlSource = ThisWorkbook _
.Worksheets(rng.Offset(0, mWorkSheet))
_
.Range(rng.Offset(0, mWorkCoL) & "2") _
.Address(True, True, xlA1, True)
.Name = rng.Offset(0, mName)
Case "Label"
.Caption = rng.Offset(0, mLabelName)
End Select

End With
Set rng = rng.Offset(1, 0)
Loop
Set ctrl = Nothing
Set rng + Nothing
End Sub


Kevin Beckham


-----Original Message-----
Can I create a spreadsheet and then "build" a userform
from it?

Example:

Speadsheet1 has 5 columns

A B C
D E
F G H
LabelName Type Title_name VarName
TabOrder CrossRefSheet
CrossRefCol Style
Name TexBox 1 Name_ 1
data A
Name label 1
Address TexBox 2 Add_
2
data B


Spreadsheet2 is called Data
It is build like:
Name Address

Here is what I want the form to look like:
----------------------------------------------------------
| Name: | | Address:
| | |
|
|
|
|
----------------------------------------------------------

Here is what I think the code should flow like: but have
no idea of the
syntax...

select spreadsheet1
recloop=0
do while recloop <= reccount
mLabelName = spreadsheet.A & recloop
mTYPE = spreadsheet.B & recloop
mMarker = spreadsheet.C & recloop
mStyle = spreadsheet.E & recloop
mTagOrder = spreadsheet.D & recloop
mWorkSheet = spreadsheet.F & recloop
mWorkCol = spreadsheet.G & recloop

if mType = "label"
..Caption = mLabelName
..Style = mStyle
create object. mLabelName

else if mType = "Textbox"

..Caption = mLabelName
..Style = mStyle
..Tag = mTagOrder
create object. mLabelName
mLabelName =end while
Textbox & mMarker = " worksheet " & mWorkSheet & mWorkCol
endif




end loop





TIA
Phil







.