Excel Executing the Wrong Macro 1

[ajcarr]

I have a worksheet which contains a lot of macros linked to buttons. This works fine until I protect the worksheet whereupon the wrong macros are executed. Removing protection excutes the correct macro once more. Unfortunately, the worksheet has to be protected.

This is Office XP running under Windows XP. Any ideas?

 

[combo]

From excel help:

[tt]Sub ProtectSheet()
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub[/tt]

allows to change protected sheet by a macro.

combo

 

[ajcarr]

Combo, I've tried that but it doesn't make any difference. It's a really wierd problem. It also occurs when I load the workbook under Excel 2000. However, an earlier version of the workbook is perfectly OK.

Incidentally, I have found that 'Userinterface' protection doesn't allow full access from macros to 'Shapes' when I'm changing the text within buttons.

 

[monikai]

I suggest to unprotect for the moment executing macro and at the end of macro to protect again

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[ajcarr]

Monikai, th e problem with doing this is that the system is used by non-technical users who must not be allowed to cahnge the display. The macros themselves do exactly what you suggest but unfortunately, the wrong macros are being executed. It can be even more strange with some of the buttons in that if the button is clicked a second time, the correct macro is executed! With no protection on the worksheet, everyting functions perfectly. I have tried unlocking parts of the worksheet but tis makes no difference - it is the protect function itself which is causing the problems.

The package was built under Excel 2000 and works in its unmodified form under XP. The only additions under XP have been to put in an additional data load module which does (should!) not affect the display at all. If I take this out, the problems remain.

 

[monikai]

If it possible:
When macro is working - show empty (or with appropriate caption eg 'WAIT' or with picture) sheet.

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[combo]

ajcarr,
really, UserInterfaceOnly does not allow full access to the worksheet, especially drawing objects. However, you can link its contents to cell (select shape and in formula bar write, say, =A1).
The option UserInterfaceOnly=True works in XP, so it may be a question of pointing the right sheet to protect.

combo

 

[combo]

To explain why it's not always work, copied from excel help file:

If you apply the Protect method with the UserInterfaceOnly argument set to True to a worksheet and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To re-enable the user interface protection after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.
If changes wanted to be made to a protected worksheet, it is possible to use the Protect method on a protected worksheet if the password is supplied. Also, another method would be to unprotect the worksheet, make the necessary changes, and then protect the worksheet again.

combo

 

[ajcarr]

Combo, you are right, it's drawing objects that 'Userinterface' does not allow access to. Because they are an irregular shape, they can't really be linked to a cell (the package is a map with sophisticated drill down facilities and about 20000 lines of code). I have an unprotect/protect userinterface in the AutOpen routine. The routines which manipulate the drawing objects start with an unprotect statement and protect again at the end of the routine.

However, it doesn't seem to be the VBa code that is causing the problem. If I protect the sheet manually, the problem still occurs. When protection is applied, the macros which are being executed are not those which should be linked with the shapes. I have confirmed which macros are being executed by putting break points at the beginning of the Subroutines. Removing protection makes everyting work OK.

 

[combo]

ajcarr, certainly, macros start at the same place, but it has to be something in the code (error handler, condition depending on protection, or registry setting) that causes different actions. Without detailed description of code it is hard to say more.

combo

 

[ajcarr]

Combo, it's difficult to know where to start in terms of the code, as i said earlier, it's a massive application with over 20,000 lines of code and well over a 100 subroutines driving a map. It worked OK under Excel 2000 and under XP in its unmodified form. I added another couple of routines to do a new type of data load but did not do anything to the display section. I have tried taking out my new additions and reverting to the original code but the problems remain. The basic modus operandi has been described above where protection is turned off when entering a routine and on again when leaving because of the restriction of 'Userinterface' protection with Drawing objects. I have debug facilities within the program where I can turn off this protection while testing and in this mode, everything is OK.

However, I can't see any reason why applying protection should change the macro links. Applying protection manually causes the same problems as doing it via VBa so I'm at a bit of a loss as to understand what could be causing it. Perhaps the only solution will be to go back to the original version under 2000 and re-do all the modifications under that version just to see if the problem lies with XP. It shouldn't be such a huge job since I can cut/paste the code from an export file. The 2000 version is on a slow machine though!

 

[ajcarr]

Thanks to all those who made suggestions yesterday. I went back and rebuilt the changes using Excel 2000 and it all worked OK. Moving it to XP caused the problems to re-occur so it is an incompatibility between the two versions of Excel. I have found that it can be worked around by re-doing a 'zorder.bringtofront' on all the buttons currently displayed (the map has 4 levels of overlay and is so complex that it can only be manipulated by program!). This slows the program down a bit.

However, the underlying cause remains a mystery.

 

[combo]

ajcarr,
just to give an example what I meant in my post concerning sheet protection sensitive code:

[tt]Sub ProtectionSensitive()
If ActiveSheet.ProtectContents Then Call sub1 Else Call sub2
End Sub

Sub sub1()
MsgBox "Sheet protected!"
End Sub

Sub sub2()
MsgBox "Sheet unprotected!"
End Sub[/tt]

There may be other tricks, as On Error... statement etc. It is hard to find them in such a big application, but have a good luck.

combo

 

[ajcarr]

Combo, the difficulty is how you would initiate a macro like that. For example, I have a button which toggles Full Screen mode (some of the users put the map into full screen and then didn't know how to get out of it so I have a button which toggles the modes). When I apply protection, this button kicks off one of the data loading modules instead! If I bring it to the front after applying protection, it's OK. Applying protection also allocated a macro to the background surrounding the map which is simply blank cells which I can't even select in unprotected mode to get rid of the assigment. Protection seems to apply an overlay across the map with a macro assigned to it. Goodness knows how this has happened. What I have done is to rename the macro it referenced and leave the original macro name as a dummy.

Do you know if it is possible when calling a macro assigned to a shape to find out what the shape name was that called it?

 

[combo]

Ajcarr,
unfortunatelly, there is no MS Map with my office XP (???), so I can't test the map. However, maybe something below you will find usefull:
1. Try Msgbox Application.Caller in the code. When code is activated via clicking drawing object, object's name is returned, even when the "Msgbox.." is placed in nested sub. (Calling from activex control will produce an error.),
2. A part of the code can be in code modules of sheet/workbook, especially event procedures,
3. When dealing with buttons and other controls, first check if they are activex controls (from control toolbox) or form controls (forms). Activex controls can be edited only in VBA design mode, form controls can be selected after right-clicking them. The code behind activex controls is located (starts) on the sheet's code module, double-click to see it, For drawing objects and forms it is in a standard module. When you right-click such object and select "assign macro" you will see the name. (But if there is no macro behind the control, for activex control it will be generated, for the drawing object excel will show macro which does not exist.)
4. the code for full screen mode (Application.DisplayFullScreen=True/False) probably refreshes the map, see the full code behind the button,
5. with worksheet(...).EnableSelection=xlUnlockedCells you can limit selection in protected mode to not protected cells only.

As for the sample code above - insert a module with it, add any shape onto the sheet, right-click it and assign a macro named ProtectionSensitive. See the difference when you click the shape with protected/unprotected sheet.

combo

 

[ajcarr]

Combo, that answer was just what I needed! By identifying the calling object I have been abale to break the link to the data load routine by program - it can't be selected manually. It was by the way a strange code that was obviously system generated, not one of mine so how and why Excel generated it, I don't know.

Your answer has also given me a way in which I can simplify my program beacuse at present, there is a separate routine for each object , only a couple of lines of code, but this will make it much simpler. The package is not Microsoft Map by the way, it is one which I built myself because it uses non-standard data - company geographical service regions, local government wards etc.

Many thanks for your help.