Using OWC9 spreadsheet on User Form

[Sherwood]

Hi,

I am trying to use a spreadsheet on a User Form for the first time. I am trying to set the cell background colour but none of the methods I can use on 'regular' sheets will work. The spreadsheet is the Office Web Components V9 (although I am not using it for web data).

My User Form is called frmUI and the spreadsheet (V9) on the form is called shtUI

Here's my test Sub with three failed attempts to set the colour:

Sub TestRowColours()

frmUI.shtUI.Cells(2, 3).Interior.ColorIndex = 6 'YELLOW
'runtime error '438'
'Object doesn't support this property or method

frmUI.shtUI.[B3].Interior.ColorIndex = 6
'runtime error '438'
'Object doesn't support this property or method

frmUI.shtUI.Range("A1:Z1").Interior.ColorIndex = 6
'runtime error '451'
'property let procedure not defined and property get procedure
did not return an object

End Sub


Q1 What am I doing wrong?

Q2 Is there a source of info I should peruse before I try to do anything more with this sheet? (I'll be needing to copy, paste, hide rows and columns, protect, get cell values, etc.; all the usual stuff that is no problem with 'regular' sheets that aren't on a form).

Q3 Is there another object I should be using instead of the OWC spreadsheet?

Cheers,
Sherwood.

[Norie]

Try this.
[vba]
shtUI.Worksheets(1).Cells(2, 3).Interior.ColorIndex = 6[/vba]
By the way the code you posted worked fine for me.:o

[Sherwood]

Norie,

Nope, adding Worksheets(1). still produced runtime error 438. Are you using a later version of the spreadsheet object (V10 or V11)? I can make it work with V10 (XP) but need to distribute to users without XP licence, hence desire to work with V9 (Win2000).


EDIT:
I've fixed it. You have to use 'Color' instead of 'ColorIndex' and then use something like
RGB(255,255,0) to set the colour (alternatively use HTML colours in a string like "Green")

frmUI.shtUI.Cells(6, 6).Interior.Color = RGB(255, 255, 0) 'Yellow

For more than one cell, the Range object goes after Cells:

frmUI.shtUI.Cells.Range("A9:AT10").Interior.Color = RGB(255, 255, 100)

Sherwood

[Norie]

Sherwood

I'm not sure what version I was using, I'm working with Office 2000.

By the way I would actually recommend avoiding using such controls when distributing files when you don't know what versions might be in use.

[johnske]

Version 9 OWC is forward compatible, but V10, V11 OWCs are not backward compatible