Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support

Visual Basic 400 Error

This is a discussion on Visual Basic 400 Error within the Microsoft Office support forums, part of the Tech Support Forum category.


Closed Thread
 
Thread Tools Search this Thread
Old 05-11-2009, 07:36 AM   #1
Registered Member
 
Join Date: Jul 2008
Posts: 7
OS: Vista Home Premium



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:

Sub RefreshAllPivotTables2()
ActiveSheet.Unprotect Password:="MyPwd"
Dim PivotTable As PivotTable
For Each PivotTable In ActiveSheet.PivotTables
PivotTable.RefreshTable
Next
ActiveSheet.Protect Password:="MyPwd", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowUsingPivotTables:=True
End Sub

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.

Regards,

Joseph Daniel

__________________
jdaniel1221 is offline  
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2


All times are GMT -7. The time now is 11:03 PM.


Copyright 2001 - 2014, Tech Support Forum

Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts