Dear Almighty Visual Basic Guru(s)...
I am having trouble with my Visual Basic programming where I want the excel activesheet to unprotect (using defined password), refresh all pivot tables, and then reprotect (using defined password). When I only have one worksheet this works out well, but when I create a second sheet I get a Visual Basic 400 error. My options are "ok", or "help" and neither are helpful to me.
Basically what happens is that when all sheets are unprotected, I can pick one sheet and run the macro via an option button with no problem at all. The pivot tables refresh and the activesheet becomes protected. I can re-run it on the same sheet with no problem too. As soon as I move to the next one, I get the 400 error. If I unprotect the prior sheet, then I can start anew, pick a new sheet and run it, but then moving on runs into the same problem. If I have used the macro once and leave a prior sheet protected I cannot move on.
I have been searching online for answers. Microsoft itself and many forums have just posted this:
"-Form already displayed; can't show modally (Error 400)
-You can't use the Show method to display a visible form as modal. This error has the following cause and solution:
-· You tried to use Show, with the style argument set to 1 – vbModal, on an already visible form.
-Use either the Unload statement or the Hide method on the form before trying to show it as a modal form."
It is Greek to me. I tried experimenting with the "Unload Me" function, but Excel spit at me and said it could not be Loaded or Unloaded. I did not try the Hide function, because I do not know the name of the form that is showing. I guess I could start guessing starting at "Form1", but I'm not sure of the implications if I do that.
Here is my code:
Dim PivotTable As PivotTable
For Each PivotTable In ActiveSheet.PivotTables
ActiveSheet.Protect Password:="MyPwd", _
DrawingObjects:=True, Contents:=True, _
As I said before, it works great if you only want to work with one worksheet on one workbook, but as soon as you throw another worksheet on there, the Visual Basic 400 error comes up.
I did create these out of two other codes. One was exclusively to unprotect and reprotect a workbook automatically. The other was just to refresh all pivot tables. I combined them to get the desired affect, so I admit I cannot be sure I combined them correctly, so it you have a suggestion for a better order, an addition, or a completely new code, I'll be glad to try it out.
My own efforts have resulted in a bit of frustration, so any help would be greatly appreciated.