Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
e-mail a worksheet from a spreadsheet | Excel Discussion (Misc queries) | |||
Reporting on multi worksheet spreadsheet | Excel Worksheet Functions | |||
Worksheet or Spreadsheet | Excel Worksheet Functions | |||
Extracting Worksheet names within a spreadsheet | Excel Discussion (Misc queries) | |||
archiving spreadsheet rows from one worksheet to another | Excel Programming |