Tabular Style Form for Entering An Array of Values

[jmcclure]

I am trying to create a form that will allow me to enter a series of x-y values. I would like for it to resemble a table or spreadsheet where there are two columns and either a fixed or variable number of rows. Is there a control that I am not finding for this? Thanks in advance.

[lucas]

Hi jmcclure,
you can add a spreadsheet to your form in office 2003 and up. Not sure about previous versions. On the forms toolbox right click and select more controls.

You could also use a multicolumn listbox.

[mdmackillop]

Hi J
Welcome to VBAX
Have you tried a listbox? this can be set with 2 or more columns. Have a look at my demo file here.
http://vbaexpress.com/forum/showthread.php?t=9837

[jmcclure]

The problem with a list box is that the user cannot enter a value, only select them. Am I wrong about this? I need the user to enter (or copy and paste) all of the values in the table. The code will then copy these values to an array for calcualtions. The number of rows will be either a fixed 12 or variable between about 8 and 20.

I looked at the Spreadsheet control and it looks a bit more complicated than what I wanted. I would need to turn off all of the options such as toolbars, sheet tabs, etc. and I'm not sure if that is possible.

[lucas]

I looked at the Spreadsheet control and it looks a bit more complicated than what I wanted. I would need to turn off all of the options such as toolbars, sheet tabs, etc. and I'm not sure if that is possible.

Select the toolbar on the form and open the properties window from view on the main toolbar...
in the properties for the spreadsheet look for DisplayToolbar and set it to false. More control over the spreadsheet available in its properties that you might find useful.

[jmcclure]

I managed to turn off everything on the Spreadsheet control such that the only thing left are the cells themselves. Now I need to hide the cells I don't want. I've managed to do this but it takes about one minute to load the form. It seems that hiding that many cells is a very slow proecess. I used the following code to accomplish it, is there a faster way? I am using the MS SPreadsheet 10.0 Control, which seems the have 18,278 Columns and 262,144 rows rather than the 256 and 65536 that Excel 2003 has. Thanks.


With TestForm.TestCtl.ActiveSheet
.Range(.Cells(1, 3), .Cells(1, 18278)).EntireColumn.Hidden = True
.Range(.Cells(21, 1), .Cells(262144, 1)).EntireRow.Hidden = True
End With

[mdmackillop]

How about

Private Sub UserForm_Initialize()
For i = 2 To 21
Set MyTB = Controls.Add("Forms.TextBox.1")
k = i Mod 2
MyTB.Left = 20 + k * 40
MyTB.Top = 30 + 20 * Int(i / 2)
MyTB.Width = 40
MyTB.Height = 20
Next
For Each c In Me.Controls
Debug.Print c.Name
Next
End Sub