Problem in adding macro to ribbon through Add-in

[paul mullan]

Hi -

I am using the following simple code to protect and unprotect all
worksheets

Sub UnprotectAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="2016"
Next ws

End Sub


Sub protectAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="2016"
Next ws

End Sub


I have tested this in the workbook that it was created in and it is
working fine. I then save as Excel Add-in file so that I can use teh
function in a ribbon in other workbooks.

I load up a workbook and select the add in. I add the bottons to the
ribbon and assign the macro

Now the buttons appear on teh ribbon but have no function i.e. they do
nothing.

Under the developer bar I have checked the 'Macros' button. This shows
no macros listed - though I am not sure if it would as teh macro is
not assigned to the workbook so to speak, but to excel through the add-
in.

If I open VB editor, the codeis visable and all seems correct. However
if i 'run' it, again nothing happens.

The problem seems to be in linking the macro to the ribbon through the
add-in process. This is the first time I have tried thsi so any help
would be greatlyapprecated.

p.s. the workbook this was created in was the default excel sheet with
"sheet 1; sheet 2; sheet 3" as workbook tabs. The workbook I want this
to work on has 55 work sheets with a variety of names. I dont think
this would make a difference but I am stumped ......
Thanks all

 

[GS]

I believe the Ribbon requires xml in order for menuitems to function. I
don't think you can 'assign' a macro directly to a Ribbon menuitem.

If your 'addin' creates menuitems they will appear on the Addins tab of
the Ribbon. You assign an 'OnAction' to these when your VBA creates
them (usually via Workbook_Open event), and you destroy them at
shutdown (usually via Workbook_BeforeClose event)!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[paul mullan]

hI -

thanks for getting back. I was following the step by step guide in
this link (http://mohammedkb.wordpress.com/2010/09/14/customize-excel-
ribbon-and-make-your-macros-default-commands-2/) which showns macros
being 'assigned' to the ribbon.

thanks

 

[Ben McClave]

Paul,

You may try changing the line:

For Each ws In ThisWorkbook.Worksheets

to

For Each ws In ActiveWorkbook.Worksheets

as the "ThisWorkbook" object refers to the workbook containing the macro code, whereas the "ActiveWorkbook" object refers to whatever workbook is active when the macro runs. At least, that's where I'd start...

Ben

 

[paul mullan]

lookign further at this my worksheets are named:

Index, Summary, 1,2,3,4,5,6,7,8,9 .......... 51,52, Data Summary.

Will teh code I have written recognise varied worksheet names?

Thanks

 

[paul mullan]

Ben - I think that has done it!

I have been looking at this for hours and think it got to the point I
couldn't see the wood for the trees!!!

Many thanks appreciate it

Paul

 

[Ben McClave]

Paul,

Thanks for the feedback. I'm glad to hear that you got it working.

Ben

 

[GS]

paul mullan used his keyboard to write :

hI -

thanks for getting back. I was following the step by step guide in
this link (http://mohammedkb.wordpress.com/2010/09/14/customize-excel-
ribbon-and-make-your-macros-default-commands-2/) which showns macros
being 'assigned' to the ribbon.

thanks

Ok, I didn't get that you're running XL2010, which I'm not familiar
with. It's nice to see that this can be done via the UI instead of
having to modify the workbook XML for the addin. Certainly makes it
simpler!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion