Why Spreadsheet worksheet is not a worksheet?

[Bula]

Appreciate very much if you can help.
I add a Spreadsheet control on my userform, and want to reference a
worksheet on the control. But got a "Type mismatch" error.


Set Sht = Me.Controls.Add("OWC10.Spreadsheet.10")

Dim Wksht As Worksheet
Set Wksht =Sht. Worksheets("Sheet1")

Why the worksheet on the Spreadsheet control is not of type worksheet?


George

 

[Peter T]

Try -

dim Sht As OWC.Spreadsheet
dim Dim Wksht As OWC.Worksheet

Set Sht = Me.Controls.Add("OWC10.Spreadsheet.10")

(might be easier to add at design time, at least for testing)

set Wksht = Sht.activesheet

The control has just the single sheet

Put a break and look at Sht and Wksht in Locals or look at OWC in object
browser.

Regards,
Peter T

 

[Bula]

Peter:

Thank you so much. It really works if we dimension the objects like
this at the beginning:

dim Sht As OWC10.Spreadsheet
dim Dim Wksht As OWC10.Worksheet

May I also ask how I can add a procedure to handle the SheetChange
event of the OWC10.worksheet at run time.

George

 

[Peter T]

Hi George

There only appears to be the one event, at least in my xl2k version. I added
the OWC control to a form and in its code module -

Dim WithEvents spr As OWC.Spreadsheet

Private Sub UserForm_Initialize()
Set spr = Me.Controls("Spreadsheet1")
End Sub

Private Sub spr_BeforeCommand(ByVal EventInfo As _
OWC.SpreadsheetEventInfo)
Dim evnt As OWC.SpreadsheetEventInfo
Set evnt = EventInfo
Stop ' look at locals

End Sub

You may need to change OWC (which is what it is in my xl9) to OWC10

Regards,
Peter T

 

[Peter T]

There only appears to be the one event,

A bit misleading, I meant only one as relates to the 'spreadsheet', quite a
few others though.

Regards,
Peter T

 

[NickHK]

George,
I'm no expert on this, but with the OWC10/Office2002 control there's the
"normal" events also as it appears to be an instance of Excel . e.g.

Private Sub Spreadsheet1_SheetChange(ByVal Sh As OWC10.Worksheet, ByVal
Target As OWC10.Range)
MsgBox "SheetChange : " & Sh.Name
End Sub
However, it does not seem to fire, although this does :

Private Sub Spreadsheet1_SheetActivate(ByVal Sh As OWC10.Worksheet)
MsgBox "SheetActivate : " & Sh.Name
End Sub

Looks like the OWC9 only supports a single WS (as you say Peter), so a
SheetChange event makes no sense.

NickHK

 

[Bula]

Peter and NickHK:

Thank you guys so much. It really helps.
The OWC10 Spreadsheet control does not have Comment property for the
cells on its worksheet. Is there anyway we can add Comment in a cell in
the worksheet of the OWC10 spreadsheet, as we do in the MS Excel
Spreadsheet?

Regards!

George

 

[Peter T]

I don't think comments are possible in an OWC spreadsheet, at least not in
my version.

Regards,
Peter T