How do I dynamically create a form with controls

[barnard90]

Hi

I need to create a Form dynamically with controls in it

It is an Employee Info form with Empno , Empname , Sal,
Deptno, Deptname as the fields ( Actually the no. of fields can more ).
The information of the field names, and the corresponding column control type ( text box, label, combo box etc ) would be provided on an excel sheet.

Could some one please suggest how can I dynamically create a form / user form with controls being loaded dynamically
Each control is loaded one below the other with 25 pixels gap

Any suggestion would be greatly valued

thanks

 

[SkipVought]

Hi,

In what application.

If it's ACCESS, then please post your question in one of the many MS Access Forums, maybe Microsoft: Access Modules (VBA Coding) forum705.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[barnard90]

I am provided with an Excel sheet with certain values

All the information ,it has is like this

Empno Textbox
Empname Textbox
Sal Textbox
Deptno List Box
Deptname Combo box

Based on these values I am supposed to dynamically create a form in which I have all these controls one below the other
The type of control can be a text / list / combo box depending on the info provided in the excel sheet

Could you please suggest

thanks

 

[SkipVought]

Hi,

Can you please state the business case for this requirement?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[barnard90]

Hi SkipVought

What do you specifically mean by the business case ?

I need to load certain HR related stuff which is given in an excel sheet onto a Visual Basic form with controls

Please respond if I need to provide more info

Thanks

 

[SkipVought]

There's more than one way to skin a cat.

Exactly WHAT are you trying to accomplish, not HOW do you expect to accomplish it.

Butons and boxes are HOW questions.

I'm trying to find out the WHAT questions.

THEN we can talk about HOW to get it done.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[barnard90]

I am provided with an Excel sheet with control names and control types

They are like
Empno Textbox
Empname Textbox
Sal Textbox
Deptno List Box
Deptname Combo box


Based on the info, I need to dynamically create a form with all those controls in them . Each control is placed one below the other with 25 pixels gap

 

[SkipVought]

What is the purpose of the FORM with these control on it?

Why does it need to be dynamically created?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[barnard90]

The number of fields and the field names like Empno, Empname , Sal etc are never constant .
They always change on the Excel sheet provided
Depending on the info , provided on the sheet a form needs
to be created with all the corresponding info

We could not make a static form in the Design time as the total number of controls , control names or control types are not fixed
They dyanamically change and they are simply provided in the last minute in an excel file
Based on whatever info we have on the excel sheet , we need to design the form with controls . ( The no. of controls per form is maxs of 10 )

 

[SkipVought]

But what is the PURPOSE for the form?


Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[barnard90]

The purpose of the form is to provide a GUI to the user
This form can take input from the user , and that information is later displayed on the web .

The form is for the user interface


Thanks

 

[SkipVought]

Have you looked at Data>Form?

Are you working with related data on multiple sheets? Example.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[barnard90]

Hi SkipVought
I am working on data on a single sheet only
No multiple sheets....

I dont want to use the Data -- > form because ,
I cannot later link this to a database or HTML pages

I prefer to load a User form / VB form
Your suggestion would be valued

 

[SkipVought]

So what DATA is being linked to a database or HTML page?

Certainly data on an Excel sheet can be so linked.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[MichaelRed]

Some several confusions here, I think.

First, to DIRECTLY answer the question, search these (Tek-Tips) fora fior KeyWords [CreateForm } CreateReport] and [CreateControl]. There are a few therads which amply illustrate the process.

Next, there is something awry with the organization which requires this silliness of the level detail. A simple spreadsheet really shouldn't be directing the NAMES of controls. The implication that you need to create HTML (e.g. WEB based forms) using VBA just shows the retro thought processes of the org. If you are going to create Web Based data, pleas have someone there wake up and smell the VB.NETs




MichaelRed

 

[fumei]

There can be a business case for the look of a form. That is - some controls are there some of the time, some times they are not there.

However, the essense of it is, IMHO, not whether they ARE there, but whether they are VISIBLE to the user. I do this fairly often. Depending on conditions, indeed, what they user sees on the form can change.

So... this is a design issue. I use the .Visible property., and sometimes I dynamically change the size of the form so it looks better. If controls are no longer visible, I don't like big empty spaces where the invisible ones are.

Private Sub cmdHide_Click()
With frmBookmarks
    .Height = 30
    .Width = 140
    .Top = 0
    .Left = 300
End With
End Sub

Private Sub UserForm_Click()
If frmBookmarks.Top = 0 Then
    MakeBigger
End If
End Sub

Sub MakeBigger()
With frmBookmarks
    .Height = 166
    .Width = 225
    .StartUpPosition = 1
    .Show
End With
End Sub

The above is an example where if the user clicks Hide, the form shrinks itself and moves itself to the top of the screen, showing just the title bar. If it is clicked there it resizes itself and put itself back to screen centre. As for resizing (with some controls visible, some not - depending on an input parameter), here is an example:

[COLOR=red]' in a public module[/color red]
Public strInput As String
Public strDump As String

Sub FormParameter()
strInput = LCase(InputBox("type in either Small or Big"))
UserForm1.Show
End Sub

[COLOR=red]' in the form code module[/color red]
Private Sub UserForm_Initialize()
Select Case strInput
    Case "small"
        Label1.Visible = False
        TextBox1.Visible = False
        Label2.Visible = False
        TextBox2.Visible = False
        Label3.Visible = False
        ComboBox1.Visible = False
        Label4.Visible = True
        TextBox3.Visible = True
        UserForm1.Height = 86
        UserForm1.Width = 184
        CommandButton1.Left = 120
        CommandButton1.Top = 30
    Case "big"
        Label1.Visible = True
        TextBox1.Visible = True
        Label2.Visible = True
        TextBox2.Visible = True
        Label3.Visible = True
        ComboBox1.Visible = True
        Label4.Visible = False
        TextBox3.Visible = False
        UserForm1.Height = 140
        UserForm1.Width = 205
        CommandButton1.Left = 140
        CommandButton1.Top = 70
    Case Else
        Msgbox "Input must be either ""small"" or ""big""."
        strDump = "Dump me"
End Select
End Sub

Private Sub UserForm_Activate()
If strDump <> "" Then
    Unload Me
End If
End Sub

This is of course VERY simplified. The controls are not created dynamically, but their visibility and location ARE. Notice the location of the command button moves, depending on whether the other controls are visible, or not. Also notice the error trap - if the user enter neither "small" or "big" UserForm_Initialize runs and passes something off to Activate (which runs after), which dumps the form.

Needless to say, this could be done much better - I just wrote this quickly. But somethines there ARE reasons for have form controls dynamically useable. However, useability does not have to mean created.

Gerry