Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet program? | Excel Discussion (Misc queries) | |||
Existing spreadsheet program | Excel Worksheet Functions | |||
userform for spreadsheet | Excel Discussion (Misc queries) | |||
Spreadsheet in an Userform | Excel Programming | |||
Repost: Looking for info on how to program a userform to use filters | Excel Programming |