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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,740
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
 
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,168,024
Messages
5,856,878
Members
431,837
Latest member
megantang

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