MSIGRID help

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
I added the MSIGRID control to a userform, containing five rows and 10 columns - I've never used this control before...Is there a way to have the cells in this grid become populated by certain cells in the workbook when the userform is opened?

thanks,
kevin
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi Kevin,

Is it MS flexgrid control ? If not please ignore my comment.

If so then you can use the TextMatrix property. ControlName.TextMatrix(1,2)=3 will put 3 into the Row2 and Column3 (rows and columns start counting from 0)

A sample code might be like the following one: Control name is flxControl in sample code.

Code:
Private Sub UserForm_Initialize()
Dim rng As Range
Dim cll As Range
Dim i As Long
    
    'Assuming data range is "E2:H18" on sheet1
    Set rng = Sheet1.Range("E2:H18")
    
    'flxControl is the flexgrid control name
    With Me.flxControl
        .ColWidth(0) = 300
        .Rows = rng.Rows.Count + 1
        .Cols = rng.Columns.Count + 1
        
        'Row Numbers
        For i = 1 To .Rows - 1
            .TextMatrix(i, 0) = i + rng.Cells(1, 1).Row - 1
        Next i
        'Column Letters - simplified: doesn't work correctly after Z
        For i = 1 To .Cols - 1
            .TextMatrix(0, i) = Chr(64 + i + rng.Cells(1, 1).Column - 1)
        Next i
        
        'TextMatrix(Row,Column) : TextMatrix(0,0) is the top-left cell of flexgrid
        For Each cll In rng.Cells
            .TextMatrix(cll.Row - rng.Cells(1, 1).Row + 1, cll.Column - rng.Cells(1, 1).Column + 1) = cll.Value
        Next cll
    End With
End Sub


I hope this helps.
Suat
 
Upvote 0
I did a little digging and am pretty sure that the MSIGRID.ocx file is actually a control for a different program :oops: Solomon IV i think...I tried inserting a MSFlexGrid control but it told me that the control could not be created because it wasn't properly licensed (??)

Basically what I'm trying to accomplish is having a grid of cells within my userform that is populated with cell values from designated cells within my workbook. I could then select a cell or range of cells in the grid within the userform, copy them and paste the contents elsewhere, etc...

Is this possible, and if so how would I go about inserting such a grid? And what is up with that MSFlexGrid error message? Thanks for the help

regards,
kevin
 
Upvote 0
any takers on this one? still looking for a resolution on this

thanks
kevin
 
Upvote 0
Hi,

The error message is because you don't have a design time version of the control, only a runtime version which is presumably used by another application on your machine. You'd need to get something like Visual Studio or Office Developer to get a design time version.

If you have Office 2000+ you could use the Office Web Components (OWC) spreadsheet control on a userform.
 
Upvote 0
Thank you for the tip dk, that works perfectly

kevin
 
Upvote 0
I am using the spreadsheet control 9.0 in my userform (I'm using Office 2000 - thanks dk), and I'm pulling values from my spreadsheet into the individual cells in the spreadsheet control that displays on the userform.

Is there a way to adjust the width of the individual columns in the control? I am running into problems with data being too wide to show up correctly in the current column width (showing up as #####)... Also, I am trying to control the format that the data appears on the control:

Code:
Spreadsheet1.ActiveCell.Offset(i - 1, 2).Value = Format(Sheet1.Cells(LastRow + i, 4), "0000.00")

However, it is inserting the data without the above formatting applied...

Are there any workarounds for either of these issues?

thanks,
kevin
 
Upvote 0
Hi Kevin,

You can autofit the columns by using something like:-

Me.Spreadsheet1.Columns.AutoFitColumns

or you can control an individual column using:-

Me.Spreadsheet1.Cells(1, 1).ColumnWidth = 20

To change the format, use the NumberFormat property (the same as in Excel). Here's some sample code:-

Code:
Private Sub UserForm_Initialize()
    Dim owcRange As OWC.Range
    Dim owcRangeCell As OWC.Range

    Set owcRange = Me.Spreadsheet1.Cells(1, 1).Range("A1:E10")

    owcRange.NumberFormat = "0000.00"

    For Each owcRangeCell In owcRange

        owcRangeCell.Value = Rnd() * 100000

    Next owcRangeCell


End Sub
 
Upvote 0
using the .numberformat property works perfectly, thanks...but i wonder why using the format() function doesn't work for me

thanks again
kevin
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top