ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Why Spreadsheet worksheet is not a worksheet? (https://www.excelbanter.com/excel-programming/380774-why-spreadsheet-worksheet-not-worksheet.html)

Bula
Why Spreadsheet worksheet is not a worksheet?
 
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
Why Spreadsheet worksheet is not a worksheet?
 
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" wrote in message
oups.com...
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

Bula
Why Spreadsheet worksheet is not a worksheet?
 
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 wrote:
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" wrote in message
oups.com...
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
Why Spreadsheet worksheet is not a worksheet?
 
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


"Bula" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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
Why Spreadsheet worksheet is not a worksheet?
 
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

"Peter T" <peter_t@discussions wrote in message
...
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


"Bula" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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

NickHK
Why Spreadsheet worksheet is not a worksheet?
 
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

"Peter T" <peter_t@discussions wrote in message
...
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


"Bula" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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

Bula
Why Spreadsheet worksheet is not a worksheet?
 
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




NickHK wrote:
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

"Peter T" <peter_t@discussions wrote in message
...
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


"Bula" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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
Why Spreadsheet worksheet is not a worksheet?
 
I don't think comments are possible in an OWC spreadsheet, at least not in
my version.

Regards,
Peter T

"Bula" wrote in message
ups.com...
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




NickHK wrote:
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

"Peter T" <peter_t@discussions wrote in message
...
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


"Bula" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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


All times are GMT +1. The time now is 05:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com