hi all, am developing an automation addin for excel. And, while saving my
worksheet I am saving certain objects as the custom properties of that
worksheet. Now when am trying to get back those objects, I am getting an
exception that says 'Unable to cast type System.string to....'. What is the
problem here? How can I extract the values(stored as custom properties) as
Objects but not strings?!

Regards,
na_ab

Could you please paste your code

Please refer
(http://msdn.microsoft.com/en-us/libr...es(VS.80).aspx)


Jacob (If this post helps please hit Yes)

I don't think you can. An object is a pointer to memory where data is
stored. When you exit out of excel you loose the memory. To save the data
you will need to save the memory area to a file. When you open excel you
would need to allocate memory for the object and then read the data from the
file into the memory area.

If you had a compiled version of Visual Basic you could save the object as
part of the executable file. Then the object would be retained when you ran
the executable.

"NA_AB" wrote:

hi all, am developing an automation addin for excel. And, while saving my
worksheet I am saving certain objects as the custom properties of that
worksheet. Now when am trying to get back those objects, I am getting an
exception that says 'Unable to cast type System.string to....'. What is the
problem here? How can I extract the values(stored as custom properties) as
Objects but not strings?!

Regards,
na_ab

If I follow, in your addin trap worksheet events at application level and
look out for something recognisable to identify one of "your" sheets being
activated, a sheet that potentially has some aX controls that your app wants
to trap.

If such a sheet is activated, check if it has any buttons that you
previously added as identified by (say) some unique name. Once you've got
those you are almost done, reattach their withevents click event in the same
way you did originally.

Regards,
Peter T

PS, I think that answers the other questions you nudged me for.

"NA_AB" wrote in message
...
hi all, am developing an automation addin for excel. And, while saving my
worksheet I am saving certain objects as the custom properties of that
worksheet. Now when am trying to get back those objects, I am getting an
exception that says 'Unable to cast type System.string to....'. What is
the
problem here? How can I extract the values(stored as custom properties) as
Objects but not strings?!

Regards,
na_ab

hey thank you guys for replying :)

peter

Since am creating buttons this way,
btn = sht.Shapes.AddOLEObject("Forms.CommandButton.1",.. ..);

I'll have to look for a way of identifying each button uniquely. However
once I close my excel and reopen, I should still be able to identify them
uniquely so that I can assign their respective catch events. Is there a way
to do this?!

Also, there are more than one button at a time and I am going to have to add
and delete them dynamically. Once I click a button, it refreshes a certain
area of the sheet and loads the result set in that area. This area keeps
changing at different times even for a single button. So, how am I going to
attach the events? There are just too many things to be remembered like the
area dimensions etc.

Regards,
na_ab

There must be many ways to uniquely identify your buttons, eg

' normal module in Excel
Sub abc() ' make some unique buttons
Dim i As Long
Dim ole As OLEObject
ActiveSheet.OLEObjects.Delete
For i = 1 To 3
Set ole = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1" , _
Left:=20, Top:=30 + (i - 1) * 40, Width:=150, Height:=27)
ole.Name = "NA_AB" & Right("00" & i, 3)
Next
End Sub


Following is in VB6, but could be in any other VBA app, eg Word (with a
reference to Excel), or adapted for C#

' Form code
Private mCls2 As Class2
Private Sub Form_Load() ' in VBA use Userform's Initialize event
Set mCls2 = New Class2
Set mCls2.xlApp = GetObject(, "excel.application")
mCls2.Init
End Sub

Private Sub Form_Terminate()
Set mCls2 = Nothing
End Sub
''''''''''''''''''''''
' Class1
Public WithEvents btn As MSForms.CommandButton
Public ws As Excel.Worksheet
Public idx As Long

Private Sub btn_Click()
ws.Cells(idx, 5) = ws.Cells(idx, 5) + 1
End Sub
'''''''''''''''''''''''
'' Class2
Public WithEvents xlApp As Excel.Application
Private mCol As Collection
Private mSH As Object

Public Sub Init()
xlApp_SheetActivate xlApp.ActiveSheet
End Sub


Private Sub xlApp_SheetActivate(ByVal Sh As Object)
Dim i As Long
Dim ws As Worksheet
Dim ole As Excel.OLEObject
Dim c As Class1
Set mCol = Nothing
If TypeName(Sh) = "Worksheet" Then
For Each ole In Sh.OLEObjects
If Left$(ole.Name, 5) = "NA_AB" Then
If mCol Is Nothing Then
Set mCol = New Collection
Set mSH = Sh
End If
Set c = New Class1
Set c.btn = ole.object
c.idx = Val(Right$(ole.Name, 3))
Set c.ws = Sh
mCol.Add c, ole.Name
End If
Next
End If
End Sub

Private Sub xlApp_SheetDeactivate(ByVal Sh As Object)
If Not mSH Is Nothing Then
If Sh Is mSH Then
Set mCol = Nothing
Set mSH = Nothing
End If
End If
End Sub


This is bare bones, obviously would require a lot more and probably wouldn't
use GetObject. Even so this simple code will pick up any of "your" buttons
and trap their events.

Regards,
Peter T




"NA_AB" wrote in message
...

hey thank you guys for replying :)

peter

Since am creating buttons this way,
btn = sht.Shapes.AddOLEObject("Forms.CommandButton.1",.. ..);

I'll have to look for a way of identifying each button uniquely. However
once I close my excel and reopen, I should still be able to identify them
uniquely so that I can assign their respective catch events. Is there a
way
to do this?!

Also, there are more than one button at a time and I am going to have to
add
and delete them dynamically. Once I click a button, it refreshes a certain
area of the sheet and loads the result set in that area. This area keeps
changing at different times even for a single button. So, how am I going
to
attach the events? There are just too many things to be remembered like
the
area dimensions etc.

Regards,
na_ab

hey, many thanks for the help guys :)

I have another question... how can we get the shape residing on the active
cell?

if suppose i have a shape on my active cell. Is there a way, I can say some
thing like

Shape s = active_cell.shape; or somethin like dat...

Regards.

You'd need to loop all your shapes until you find one whose TopLeftCell
address is the same as the ActiveCell. Also depends what you mean by "on"
the cell, the TopLeftCell might be above and/or to the left of the
ActiveCell and the shape may well be regarded as "on" it.

Alternatively, to get a shape that whole or partially is over the
activecell -

Sub test()
Dim sh As Shape, r As Range

For Each sh In ActiveSheet.Shapes
With sh
Set r = Range(.TopLeftCell, .BottomRightCell)
End With
If Not Intersect(ActiveCell, r) Is Nothing Then Exit For
Next

If Not sh Is Nothing Then
MsgBox sh.Name
Else
MsgBox "no shape is over the activecell"
End If

End Sub

Regards,
Peter T

"NA_AB" wrote in message
...
hey, many thanks for the help guys :)

I have another question... how can we get the shape residing on the active
cell?

if suppose i have a shape on my active cell. Is there a way, I can say
some
thing like

Shape s = active_cell.shape; or somethin like dat...

Regards.

hey, peter.. did the same thing.. nd thus, am able to now save my pages and
reopen them and still have their eventhandlers intact.. respectively :)

thanks a ton

regards...