Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Macro Buttons on Toolbar

0

I have created several macros coupled to subroutines in VBA and have these attached to buttons on the Toolbar above the excel sheet. (2007) I have a frequent problem when it comes to adding a new button.

I am going into Customize and can see all my Macros on the Left Hand Side of the screen waiting to be added. However, the heading on the right hand side is 'For All documents (default)' which is not what I want to do, so I change the selection to 'For.... followed by the particular workbook I am accessing. As soon as I do that several macros on the left disappear, including the one I was going to add! Please help

excelmacros-vba
MS Office Version: 2007
posted by: AliWood on Wed Dec 20, 2017 at 5:53 pm
Answer
Discuss

Answers

0

I suggest that you look at the availability (scope) of the macros that disappear, following the logic that you couldn't make a macro available in workbook "X" if the macro in question isn't visible from workbook "X".

Therefore you might first look at the location of the macros that disappear. Are they in the same XLA workbook as those who don't? Then look at the modules they are in. Is 'Option Private Module' declared anywhere? Last, not least, look at the scope declared for each of them. Macros are public by default, unless explicitly declared as 'Private'. As a kind of desparate measure you might add 'Public' to their declarations.

Before you totally give up consider the possiblity of an error in the testing procedure, not the macros or their scope. You might have added macros to the XLA workbook but not yet updated the add-in, so that the loaded add-in doesn't have the new macros yet. This theory would explain that the macros show in the list because the XLA file is open, and they disappear because an earlier version of ad-in is still loaded.

posted by: Variatus (rep: 4889) on Wed Dec 20, 2017 at 10:39 pm
Discuss

Discussion

Thank you.
I tried making them explicitly Public, but as you inferred, this did not change anything. None of them were/are Private.
Having experimented a bit more, I think I have found an answer just by playing around with the names of the subroutines within the modules. I had a few subroutines with the same name as the Module they belonged to, and they were the ones that couldn't be found on the list. So I have renamed them to something different and that has solved that problem.
Nevertheless I am somewhat bemused by the behaviour of this (rather old) software - some of the macros are listed prefixed by their module name and some are not.
I should add that ALL my code is particular to this workbook - there are no other workbooks involved.
I did not understand your talk about updating the "add-in"....? I don't think I use 'Add-in"s
AliWood Dec 21, '17 at 5:38 am
Yes, the behaviour you describe surprises me too. However, it seems that you have solved the problem and that is the main thing.
I thought that your macros would be in an XLA workbook and loaded as an add-in. In that way they would be available to any workook you load. If you need them in a single workbook only you wouldn't use that method.
Variatus (rep: 4889) Dec 21, '17 at 7:12 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login