Newbie - Input Form Question

[Tempy]

Hi all, i have an input Form that has 86 textBoxes in it and just wanted
to know which is the best method to put the input into the desired cells
on the worksheet.
Is it better to make the textbox value a variable and then insert it
into the desired cell or is it better to link the inputbox to the
desired cell, so the input goes straight into the cell as it is typed
into the textbox ?

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Bob Phillips]

Depends how much work and how variable it is.

Linking is binding it, and takes a lot of effort for 86 textboxes, and is
inflexible with so many.

You could do it in a loop

Dim oCtl As Control
Dim i As Long

For Each oCtl In Me.Controls
If TypeOf oCtl Is msforms.TextBox Then
i = i + 1
ActiveSheet.Cells(i, "A").Value = oCtl.Text
End If
Next oCtl

 

[Tempy]

Thanks a lot Bob,

Please excuse the ignorance, but could you explain abit more how the
loop would work, as it has to place the values in different celss all
over the worksheet.

thanks,

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Rich_z]

IMHO, it's better to not link the cells, as this gives you the
opportunity to validate the input before it hits the spreadsheet. You
don't need to have an intermediate variable, you just use the textbox
object text property.

On another slant, an input form with 86 input fields can be a bit
daunting to the user. Have you thought about grouping your input
fields and then placing them on Tabs so that the user clicks a general
heading and then gets the text boxes that are relevant to that heading
??

For example you could group the fields (assuming you're doing a sales
order for instance) as:

General
Delivery
Items

And the general tab would contain the Account number, Po Number, Po
Date and Billing details.

The Delivery tab would contain the delivery instructions, and the items
tab the individual items.

Regards

Rich

 

[Bob Phillips]

Tempy,

It simply goes through all the controls on the from and tests whether it is
a textbox or not. If so, it drops the value into a cell.

Perhaps you could name the textbox as the cell address, such as
txtA1,txtM25, etc, then use

Dim oCtl As Control
Dim i As Long

For Each oCtl In Me.Controls
If TypeOf oCtl Is msforms.TextBox Then
i = i + 1
ActiveSheet.Range(Right(oCtl.Name, _
Len(oCtl.Name) - 3)).Value = oCtl.Text
End If
Next oCtl

This extracts that cell address from the textbox name and saves the value in
that cell.

Any good?

 

[Tempy]

Thanks Bob, that really helps me.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Tempy]

Thanks for the suggestion Rich, i think i might just go that way, as i
have 4 different groups.

Thanks for the help, much appreciated.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***