Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

Creating a control (flexgrid) programmatically in VB/VBA
Posted: 29 Jun 04




I came across a weird bug in excel97/VBA the other day. I was using a flexgrid to display some data I was retrieving from some text files. I did the usual thing, dragged the flexgrid onto a new form, set its properties and then started filling it with my retrieved data. So far so good. The problem came when I saved my workbook. It doubled in size. I opened it, saved it again and it doubled in size again. Pretty soon it went from about 750K to 70 Mbytes. Disaster. Anyway the way around this bug was to create the control programmatically. Here's how you do it.

In the declaration section - if you want your control to respond to events

Private WithEvents msflexgrid3 As MSFlexGrid

Now in your module or procedure

'Create the control
'
Set msflexgrid3 = userform1.Controls.Add("MSFLEXGRIDLIB.MSFLEXGRID", harry, True)

'set some of its properties
'
With msflexgrid3
            .Height = 120
            .Width = 563.4
            .Left = 15.6
            .Rows = 1
            .Cols = 10
            .FixedRows = 0
            .AllowBigSelection = False
            .AllowUserResizing = flexResizeColumns
            .Appearance = flex3D
            .Font.Name = "arial"
            .Font.Size = 8
            .Font.Bold = False
            .HighLight = 1
            .ScrollBars = 3
            .SelectionMode = 1
            .Top = 35
            .colwidth(0) = 240
            .colwidth(1) = 840
            .colwidth(2) = 1300
            .colwidth(3) = 840
            .colwidth(4) = 930
            .colwidth(5) = 1200
            .colwidth(6) = 1060
            .colwidth(7) = 2000
            .colwidth(8) = 2000
            .colwidth(9) = 795
            .CellAlignment = 2
            .Redraw = True

'Add some column titles
'           
.AddItem "Column 1" & vbTab & _
                "Column 2" & vbTab & _
                "Column 3" & vbTab & _
                "Column4" & vbTab & _
                "Column 5" & vbTab & _
                "Column 6" & vbTab & _
                "Column 7" & vbTab & _
  "Column 8" & vbTab & _
                "Column 9" & vbTab & _
                "Column 10", 0





'Assume we have a recordset full of data, this will add them one by one
' to the flexgrid

For i = 0 To ADOlookup2.RecordCount - 1

        With msflexgrid3
                    .AddItem "X" & vbTab &
         ADOlookup2.Fields.Item(0).Value & vbTab & _
                        ADOlookup2.Fields.Item(1).Value & vbTab & _
                        ADOlookup2.Fields.Item(2).Value & vbTab & _
                        ADOlookup2.Fields.Item(3).Value & vbTab & _
                        ADOlookup2.Fields.Item(4).Value & vbTab & _
                        ADOlookup2.Fields.Item(5).Value & vbTab & _
                        ADOlookup2.Fields.Item(6).Value & vbTab & _
                        ADOlookup2.Fields.Item(7).Value & vbTab & _
                        ADOlookup2.Fields.Item(8).Value, 1
End With

              ADOlookup.MoveNext

Next I

With msflexgrid3
.Rows = ADOlookup.RecordCount + 1
            .Row = 0
            .col = 0
            .RowSel = .FixedRows - 1
            .ColSel = .Cols - 1
            .CellAlignment = 2
            .Redraw = True
End with


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close