DAta grid in a Userform ???

[Wes]

Anyone know if it's possible to place a data grid into a Userform? I have a situation where I would like to have 20 lines in a daily activity area of a userform.

Each line would consist of :
Start time - End Time - Interval-Code-Activity-BHA-Depth

multiply this by 20 and it's quite a few text boxes. I was wondering if there is something out there close to a data grid for userforms?

Thanks,

Wes

 

[SydneyGeek]

Sorry Wes,

tried searching for data grid, flex grid, and ActiveX controls. If there is such a feature in Excel, it's undocumented.

EDIT Just a thought -- what about using a ListBox to represent the data set? You could set it to load with 20 blank rows if required, and use a looping routine to push the data out to the worksheet.

Denis

 

[Wes]

Thanks Denis

 

[TryingToLearn]

This help at all? 7 x 12 grid Play with sizes or size of userform to get more on it!

Private Sub UserForm_Initialize()
    Dim TBray(1 To 7) As msforms.TextBox
    Dim LBray(1 To 7) As msforms.Label

    Dim intTop As Integer
    Dim lft As Integer
    Dim cap, capt, c, i

    cap = Array("Start Time", "End Time ", "Interval", "Code", "Activity", "BHA", "Depth")
    intTop = 0
    For c = 1 To 12
        lft = 0
        For i = 1 To 7
            capt = cap(i - 1)
            Set TBray(i) = Controls.Add("Forms.TextBox.1", "TB" & i & c)
            With TBray(i)
                .Top = intTop + 36
                .Width = 58
                .Left = lft + 63
                .Height = 18
            End With
            Set LBray(i) = Controls.Add("forms.Label.1", "Lb" & i & c)
            With LBray(i)
                .Top = TBray(i).Top - 12
    '////  Take out "& i & c" if you dont want numbers in labels
                .Caption = capt & i & c
    '////
                .Width = TBray(i).Width
                .Left = TBray(i).Left
                .Height = 12
                .TextAlign = fmTextAlignCenter
                lft = lft + 63
            End With

        Next i
        intTop = intTop + 30

    Next c

    '//// comment out this section when you are done with testing
    For c = 1 To 12
        For i = 1 To 7
            Me.Controls("TB" & i & c).Text = "TB" & i & c
        Next i
    Next c
    '////
End Sub

 

[SydneyGeek]

Bob, nice bit of code!
Presumably the size of each text box can be adjusted too...
As for getting data to the spreadsheet, it looks like reading it out to an array and dumping it would be the way to go too.

Denis

 

[TryingToLearn]

Bob, nice bit of code!
Presumably the size of each text box can be adjusted too...
As for getting data to the spreadsheet, it looks like reading it out to an array and dumping it would be the way to go too.

Denis

Well thank you Denis! It's from what I've learned here in the past 4 or 5 months thanks to you and the others. Glad I'm at a point to give back.

And yes, basically the output I've used for my project doesn't use an array, just uses the loop to output the values of the textboxes to their various new homes.

p.s. & thanks to Nate, it's nicely formatted too (don't want him yelling at me again...)

 

[SydneyGeek]

Hi Bob,

Yep, this is a great site -- I'm learning all the time! If you're interested in the array method for reading to the sheet (faster than looping), have a look at this:

Private Sub cmdOK_Click()
Dim DATAray(1 To 10, 1 To 7) As Variant

For i = 1 To 7
    For c = 1 To 10
        DATAray(c, i) = Me.Controls("TB" & i & c).Text
    Next c
Next i
Range("A65536").End(xlUp).Select
Range(ActiveCell, ActiveCell.Range("A1:G10")).Value = DATAray
End Sub

I've set this to work for a 7 x 10 array (7 cols, 10 rows). The form stays loaded and the textboxes can be cleared using this, for the next set of 10:

Private Sub cmdClear_Click()
For c = 1 To 10
    For i = 1 To 7
        Me.Controls("TB" & i & c).Text = ""
    Next i
Next c
End Sub

BTW, I've declared c and i at the top of the form so I don't have to do it for each routine.

Cheers
Denis

 

[TryingToLearn]

Thank you Denis for turning on the light bulb in 2D.
Set up a grid, pop it in the array, pop it in a grid on page.