DAta grid in a Userform ???

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
This help at all? 7 x 12 grid Play with sizes or size of userform to get more on it!

Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2003
Messages
723
SydneyGeek said:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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:

Code:
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:

Code:
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

Well-known Member
Joined
Sep 10, 2003
Messages
723
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.
(y)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,332
Messages
5,769,470
Members
425,552
Latest member
learnerrr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top