Solved: Application OK only in Excel 2000 or earlier

[Peter58]

Hi,

First of all I am not a VBA programmer. I am, however, a VB and C programmer and so, for my sins, I have been asked to try and sort out a problem with some code written in 1997 which works fine in Excel 2000 and earlier but not in Excel 2007 (I haven't tried it in Excel 2003).

I have spent quite some time looking into the problem but have not found a solution to it as yet. I have installed two .dll's that I believe were needed but this has not made any difference (VBACV10.DLL and VBACV10D.DLL).

The code is part of a larger application but the offending file is one called a.XLS. Basically, it is a simulation used in a chemistry teaching lab.

Run under Excel 2000 there are no problems. However, under Excel 2007: the first section is "The Arrhenius Equation". Click "Next" and then the "right arrow" on the 2nd section above "Quit" to get to the 3rd section. If you now click to right arrow here (just above "See calculation"), an error occurs (Unable to set the visible property of the TextBoxes class).

Under Excel 2000, the 4th section works fine showing some calculations and a graph.

I wonder if anyone has any idea why this is, please, and what can be done to fix it. I guess I may resolve the issue if I spend more time but perhaps someone experienced in VBA will be able to look at it and see the problem straight away. One can always hope!

I see that as this is my first post I cannot post a link to download the file.


Hopefully the following will be allowed and the file can be downloaded from here:


h t t p / /:139 (dot) 184 (dot) 163 (dot) 77/a.xls


(Obviously replace "dot" with a real full stop and no spaces).





Very many thanks,

Peter

[frank_m]

Hi Peter - Welcome to the Forum,

Just to see if this narrows down the issue, try disabling all the places where you are unprotecting and reprotecting the sheet. Then run this:

Public Sub ProtectAll_ExceptDrwgObjects()
For Each ws In ThisWorkbook.Worksheets
With ws
If .ProtectContents Then
.Unprotect
.EnableSelection = xlNoRestrictions
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, userinterfaceonly:=True
End If
End With
Next ws
End Sub

I'm not sure if this code will run in Excel 97 but if it does, you no longer should unprotect the sheet when the visibility of objects, or other code induced changes are made.

Protect DrawingObjects:=False makes it so the visible property of objects can be changed while the sheet is protected, as that seems to be where you get an error.

The userinterfaceonly:=True part is what protects against user changes but allows code induced changes.

By the way for your download sample URL, you have the forward / / slashes before the colon : when they should be after. - The routines seem to run without errors on my Excel 2003, as I tested it following your instructions. - I'll try to get to testing it with Excel 2007 later today.

[Peter58]

Hi Frank,

Many thanks for your response and help. Your suggestion of what the problem could be seems very logical.

I have now been through and disabled the protection and unprotection of the sheet. However, I am not sure where to put the code that you provided. Sorry if I seem dumb but I really am a VBA newbie and I don't know where this should go. The only code I see in the project is for Sheets 1 through 4 and the code in the two modules.

I have tried putting it at the top of the code for Sheet 4 but this did not seem to help.

I'd be very grateful if you could help a bit further with this.

Many thanks,

Peter

[frank_m]

Hi again Peter,

It's been so very long since I used Excel 97. I would think you could put the procedure in the macro code sheets, but I don't remember how you would run it. If you can try it in Excel 2000 there is a menu item in the code module named "Run" - Click that and there is a drop down where you should click Run Sub/userform. (or in 97 if you don't see any obvious way to run the macro, you could attach the macro to a new button temporarily just for this test. - Or the code to the begining of another Sub.

After you run it once it will never need to be run again. - If this does help your issue, you can then experiment with the drawing objects protection part of the code, as you may possibly be able to set that to true.

[Peter58]

Hi Frank,

Sorry for the silence but I have been playing around with the problem amongst other things!

I have found that it >appears< that under Excel 2007, the Text Boxes on sheet 4 are not being found.

After much trial and error, I have found that if I put code such as:

Sheets("arrplot").Buttons.Visible = True
or
Sheets("arrplot").GroupObjects.Visible = True

in the Sub goarrplot()

after the code line

Sheets("Arrplot").Select

and then start on sheet 3 and move on to sheet 4, these objects are displayed on sheet 4 before an error occurs when it tries to display the Text Boxes. It seems it falls over when it is asked to make the Text Boxes visible.

I have added a couple of Text Boxes on sheet 4 (arrplot) and called them (1) and (2) and if I use the code:

Sheets("arrplot").TextBoxes(1).Visible = True
Sheets("arrplot").TextBoxes(2).Visible = True

or

Sheets("arrplot").TextBoxes.Visible = True

then these Text Boxes are displayed.

However, it does not seem to want to display the Text Boxes that are (or should be) already built into the sheet.

I hope this makes sense! I am just getting my head around it.

One question for you if you have got this far and are still reading that could help a lot is:
As far as I can see, if you run the application and make a text box invisible, if you then close down the application and restart it, these text boxes remain invisible if you look at the sheet that they are contained on (and vice versa, ie if you then change the code to make them visible and then close and restart the application they are then still visible if you look at the sheet they are contained on).
The question is then, is there a way of forcing these object to be visible, ie if I right click on sheet 4 on the Project Explorer, can I do something to make all the objects visible? I want to be able to see if I really can see these missing text boxes when I am using Excel 2007 without the application being run.

Very many thanks for your help so far - it did point me to look at the protection states and that led on to what I have described above.

Regards,

Peter

[Peter58]

Hi again Frank,

I am almost there! Would still be interested in what I asked you about in the last post but I have now found that if I add the code:

Sheets("arrplot").DrawingObjects.Visible = True

in the right place(!), the thing is virtually running.

I have to leave now but will look at it again in the morning and let you know how I get on.

At least I am going home on a bit of a high!!

Regards,

Peter

[frank_m]

Hi Peter,

You're on the right track I think, largely because you are being explicit by using the sheet names,
but I would go with only changing the visibility of specific shapes at the appropriate moments,
as I assume the original code tries to do. Otherwise you will likely alter the intended functionality of the workbook.

Something like this:

' to hide
Sheets("arrplot").Shapes("My Arrow").Visible = False
'one way to get the shapes name is to run the macro recorder, select the shape, then look at the code.

'Sheets("arrplot").Shapes("Text Box 1").Visible = False

' to unhide
'Sheets("arrplot").Shapes("My Arrow").Visible = True
'Sheets("arrplot").Shapes("Text Box 1").Visible = True

and for anywhere that the code does need to hide all the texboxes on a sheet, try changing this:
ActiveSheet.TextBoxes.Visible = False
To:

For Each shp In ActiveSheet.Shapes
If shp.Type = 17 Then ' 17 is textboxs
shp.Visible = False
End If
Next shp

[Peter58]

Hi Frank,

Well I have solved the problem, or rather got round it. It seemed that whatever I did, I could not make the text boxes that were on the problem sheet visible. I know there are there because it runs fine in Excel 2000. So, what I did was to create a new sheet and replicate what should be on it and re-wrote the code. It didn't take that long in the end and now it is all working just fine. I have removed the original sheet and although the style of the text boxes etc is slightly different in the new sheet, it is working and everyone is happy.
So many thanks for your help. At least I have learnt something of VBA now and may even get to use it in the future!
Best regards,
Peter