Same Macro in different Workbooks wont work 2

[harrisharris]

I have created a Macro called Alfa and placed it in a number of different workbooks. For all workbooks, control + g runs the macro. If only one of the workbooks is open, the macro works fine, however, if a number of the workbooks are open the macro does not always run properly.

What have I done wrong? I cannot find anything in Excel help to indicate special rules for what I am trying to do.

 

[TonyJollans]

Hi harrisharris,

What do you mean by the macro does not always run properly?

You are probably (by default perhaps) referring to something in a workbook which doesn't always resolve in the same way when you have multiple workbooks open. To say more I'd really need to see the code.

Enjoy,
Tony

 

[combo]

When you record a macro in excel, mostly it records what you selected or activated and what you did with the selection. This is why you can find a lot of 'Something.Select' or 'With Selection'. This a source of possible errors, for instance:
- there is no workbook with specified name to activate,
- a given object does not exists in the second workbook (for instance a sheet with specified name),
- selected object when you run macro is of different type than that when recording macro (cell vs. shape, worksheet vs. chartsheet),
- sheet is protected,
- when you work with Range object without specyfying workbook and worksheet, it is always in the active sheet.

Hope this will help to find why your procedure works so.

combo

 

[harrisharris]

Good afternoon Tony and Combo,

The situation is that I have two macros in each workbook, Sheets 1 and 2 have been renamed alfa and bravo respectively.
I have just found that even with one book open, when I look in the Macro List, I get two scenarios (depending when I look)
Scenario 1. The two macros are named Sheet1.alfa and Sheet2.bravo - as such the macros run properly.

Scenario 2. One of the macros name will change to Lans.tivoli.xlsalfa This is when the macro will not work.

 

[combo]

Hi harrisharris,
Excel macro dialog shows all available procedures (precisely: procedures without arguments) in all open workbooks.
Scenario 1 takes place when the active workbook is that containing macros. If you use worksheets and do not specify workbook name, you will change the workbook with macros.
Scenario 2 is when you activate other workbook, you still see procedures, but as they are in other than active workbook, you see their full address. When you run such macro, it will use active workbook other than containing macros (unless you include workbook name).
So this is the case Tony pointed.

I can also that in the first case you placed macro in sheet's code module. It is not the reason for errors, but, generally, this is the place for event handlers and supporting them private procedures. The standard place for general purpose procedures and functions is standard module.

combo

 

[harrisharris]

Morning Combo,

I think I get the drift, but, would appreciate some advice in laymans language.

My situation is that I have about ten workbooks. In each workbook there is a particular sheet for which I have designed the macro. The sheets are all the same in design but cover different areas of the business. Basically, the macros are designed to insert fill colors into certain cells.

What I wanted to do was have these two macros in all workbooks and be able to run them using the same Alt + no matter what workbook I am working on.

If I have more than one workbook open I sometimes get the Pop up message "400" (with red cross).

This is the code I have built:

Sub dwacolours()
Dim rnga As Range
Set rnga = Range(ActiveCell, ActiveCell.Offset(0, 16))
rnga.Select
rnga.Interior.Color = 16776960
ActiveCell.Offset(0, 11).Select
Selection.Interior.Color = vbYellow
ActiveCell.Offset(0, 1).Select
Selection.Interior.Color = vbYellow
End Sub
Sub completegreen()
Dim rnga As Range
Set rnga = Range(ActiveCell, ActiveCell.Offset(0, 16))
rnga.Select
rnga.Interior.Color = vbGreen
ActiveCell.Offset(0, 16).Select
End Sub

The workbooks are accessed on a shared drive and there are normally two users. One user tends to keep the majority of the books open.

If you think its possible to do what I have explained, I would really appreciate a step by step instruction.

This is my first attempt to have a real dab at VBA so bear with me.

Regards

 

[harrisharris]

P.S. My code is stored in the appropriate sheet of the VBA Project. Should I have stored it in a module?

 

[harrisharris]

P.S.S

I have removed all code from the workbooks and re-installed as a module of the project. This seems to have done the trick. I think this is what you have both been trying to tell me what to do!

Very many thanks for you assistance.

Regards
Laurence

 

[combo]

There are some points in this task.
Firstly - the code. All works for me, but in general there may be some cases when error occurs. For instance you can have protected worksheet or run macro when chart object is selected. To avoid execution of a part of code, each procedure can be like this:
[tt]Sub dwacolours()
Dim rnga As Range
On Error GoTo errh
' your old code here
Exit Sub
errh:
End Sub[/tt]

Secondly - the place where your code is. If all procedures to the same, it is not necessary to duplicate them in each workbook. They can be in one kept and opened locally. They should be written in a standard module. You can assign a keystroke to it, working together with CTRL key, via 'macros' excel dialog. You can also create personal workbook, designated for keeping and accessing macros and make it open on excel startp. Finally, You can save your workbook as excel add-in (last option of file format) and install it, once installed its macros will be silently accessible.

The code can be slightly polished. You can use vbMagenta instead of 16776960.
To avoid jumping between cells when the action is impossible, you can use:
[tt]rnga.Interior.Color = vbMagenta 'in first procedure
rnga.Interior.Color = vbGreen 'in second procedure[tt]
instead of selecting range first.

combo

 

[harrisharris]

Thanks a lot combo, I have tried the add-in suggestion and it works like a dream. This has given me ideas to build on.