Solved

MSFlexGrid ColumnWidth

Posted on 2004-08-22
3
414 Views
Last Modified: 2013-12-25
I am using a MSFlexGrid in an Excel VBA App and I'm having some trouble setting the column width of the final column.  The number of columns depends on the user, and the widths of these columns equals the width of a corresponding column in an Excel Spreadsheet.  Now, say the user wants there to be 5 columns in the flex grid.  The total width of all these columns is 6000 twips (5000 for the first four and 1000 for the last one).  My problem is that the width of my grid control is 12000 twips and I want to extend the width of the last column to take up the extra space.  So, I want the last column to be 7000 wide instead of 1000.  All I should have to do is, after I get done filling the columns, test whether the sum of the widths of the columns is less than the width of the grid control and if it isn't, make the width of the last column equal to the difference.  Pretty simple right?  Well, it's not working and I don't know why.  It always leaves the last column short of the edge of the grid by an inconsistent amount (approx 400 twips).  Is there a better way to do this?  I've been looking for a setting that will make the width of the columns at least equal to the width of the grid control but I don't think it exists.  Here's what I have so far

With Grid
    .Clear
    .Cols = iCols
    For Each Col In SrchCols
        .TextMatrix(0, i) = ActiveSheet.Cells(HeadRow, Col).Value
        .ColWidth(i) = ActiveSheet.Columns(Col).Width * 20
        ColWidth = ColWidth + .ColWidth(i)
        i = i + 1
    Next Col
    If ColWidth < GridWidth Then
        .ColWidth(.Cols - 1) = GridWidth - ColWidth
    End If
End With

Thanks
Kyle
0
Comment
Question by:kgerb
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
PePi earned 400 total points
ID: 11872349
setting the .ColWidth to 1000 won't exactly set that column's width to 1000. if after setting the width to 1000, try and see the value of the width for that column. it will give 1005. that's probably what's been screwing your procedure. plus, if your flexgrid's appearance is set to 3-D, you have to take into account the little drop on the border.

so if the appearnce is 3-D, you have to offset the flexgrid's width by 95. to compute for the total column width (except for the last one, since you'll be resetting this and assuming you have more than 2 columns):

With Grid
    'compute column width except last column
    For i = 0 To .Cols - 2
        ColWidth = ColWidth + .ColWidth(i)
    Next i

    'set last column
    If ColWidth < (.Width - 95) Then               'remove 95 twips to augment the border if appearance is 3-D
        'set the last column
        .Colwidth(.Cols -1) = .Width - ColWidth - 95
    End If
End With

hope this is what you're looking for.


0
 
LVL 12

Author Comment

by:kgerb
ID: 11873047
PePi,
Thanks for your response.  My app is saved on my computer at home so I'll check your code out tonight and get back to you.  Thanks again

Kyle
0
 
LVL 12

Author Comment

by:kgerb
ID: 11876896
Well, I'm not sure why but I am still left with about 400 twips from the right edge of my last column to the edge of the grid control.  The wierd thing is that it's not a consistent offset that I can account for.  The distance changes each time I run the macro?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA color chart bars 12 76
change vba from autofit to 13.5 width? 4 28
VB6 Compile Compatibility Issue 4 87
VB6 - Convert HH:MM into Decimal 8 51
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now