Macro created in XL2000 not working in XL97

[Shetty]

I have created a simple addin to run a macro through a context menu.
It works fine in XL 2000 but does nothing in XL97. Even context menu
command and icon is not created. Is there different syntext for XL2000
and XL97?

Code as below.

Regards,
Shetty

Code:
In a module:

Sub cmdadd()
With CommandBars("Cell")
With .Controls.Add
.FaceId = 1183
.Caption = "Delete Star"
.OnAction = "DeleteStar"
End With
End With
end sub

Sub DeleteStar()
Selection.Replace "~*", "", xlPart, xlByRows, False
End Sub

In this workbook code panel:
Private Sub Workbook_Open()
Call cmdadd
End Sub

 

[Bob Flanagan]

Shetty, try putting the reference to the macro in a Auto_Open() procedure in
a regular module. Perhaps the workbook open event is not working. I recall
a problem with either Excel 2000 on this, but it has been awhile.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Dave Peterson]

Are you sure you put that code under ThisWorkbook (workbook_open)?

And macros are enabled?

I'd add a few msgboxes to the code so I could "watch" it execute when it's
loading.

I don't have xl97 anymore, but your code sure looks like it should work. (And
it does in xl2002!)

But you may want to be a little more careful:

Option Explicit
Sub cmdadd()
With CommandBars("Cell")
On Error Resume Next
.Controls("Delete Star").Delete
On Error GoTo 0
With .Controls.Add
.FaceId = 1183
.Caption = "Delete Star"
.OnAction = ThisWorkbook.Name & "!DeleteStar"
End With
End With
End Sub

I deleted the existing option to stop from adding multiple "delete stars".

 

[Shetty]

Dave,
Thanks for your reply.
I had send the file as addin to many people in different offices.

1. I have used the same code but created the addin in the pc who has
reported that it is not working(with XL97). I used VNC for remote
access to this pc. Now it works as required without changing a single
line of code.

2.Another person having office 2000 and Windows 2000 professional has
also complained that nothing happens after installing the addin from
tools>addin>browse and selecting the addin file. They do not have VNC
so I could not do anything there.

I didnt understand the behaviour. Macro is always enabled. I make sure
of that. It is something else.
In the meanwhile, I will try the code provided by you. It may take a
while to test it on different pc's at different locations. I will post
the results.

Thanks again.
Shetty.

 

[Dave Peterson]

But if the macro didn't do anything, then I don't think my macro will run,
either.

I don't have another suggestion, though.

 

[Shetty]

Boss,
Sorry for not explaining clearly. The macro and addin works perfectly
in my pc but at the same time does nothing(no error, not creating
command in context menu)in the other users pc.
Anyway thanks for replying.

Regards,
Shetty.

 

[keepitcool]

seem to remember that excel97 wants
application.commandbars not commandbars

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >