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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,264
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
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
No, it is the MSIGRID control
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

any takers on this one? still looking for a resolution on this

thanks
kevin
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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.
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you for the tip dk, that works perfectly

kevin
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
using the .numberformat property works perfectly, thanks...but i wonder why using the format() function doesn't work for me

thanks again
kevin
 

Watch MrExcel Video

Forum statistics

Threads
1,122,222
Messages
5,594,904
Members
413,952
Latest member
JGer

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