VBA コマンドバーを消した後に残る空白部分

お世話になります。

ExcelVBAで、コマンドバーの類を全て消去した後に残る、
空白部分ができる原因が解りません。
この部分をダブルクリックすると、エラーになって強制終了に
なってしまいます。
完全に消してしまいたいのですが、この状況になってしまう、
原因と対策を教えてください。

内容は、『システム』という名のシートにある『ロック』ボタンを
押すと、コマンドバーを全て消去して、Sheet1に切り替わるといった
流れです。

↓モジュール側
-------------------------------------------------------------
Dim myCB As CommandBar

Public Sub onCmdBarAttr()

DoEvents

With Application

For Each myCB In Application.CommandBars
myCB.Enabled = True
Next myCB

.CommandBars("Standard").Visible = True

.CommandBars("Formatting").Visible = True

.CommandBars("Visual Basic").Visible = True

.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("CELL").Enabled = True

' タスクバーに表示させる
.ShowWindowsInTaskbar = True

' 数式バーを表示
.DisplayFormulaBar = True
' ステータスバーを表示
.DisplayStatusBar = True

End With
End Sub

Public Sub offCmdBarAttr()

Dim myCB As CommandBar

On Error Resume Next

For Each myCB In Application.CommandBars
myCB.Enabled = False
Next myCB

On Error GoTo 0

With Application
.CommandBars("Worksheet Menu Bar").Enabled = False
.CommandBars("CELL").Enabled = False

' 数式バーを消去
.DisplayFormulaBar = False

' ステータスバーを消去
.DisplayStatusBar = False

' タスクバーに表示させない
.ShowWindowsInTaskbar = False
End With

End Sub
' シートロック
Public Sub protectSheet(ByVal stSheetName As String)

Application.ScreenUpdating = False

' 一旦シート保護をかけ直す
With Worksheets(stSheetName)
.Activate
.Unprotect
.Protect UserInterfaceOnly:=True

' カーソルの移動範囲を設定する
.ScrollArea = "$A$1"

' 左上セルを選択
.Range("H16").Select

' 保護されたセルは選択不可にする
.EnableSelection = xlNoRestrictions

End With

Application.ScreenUpdating = True

End Sub
' シートロック解除
Public Sub unprotectSheet(ByVal stSheetName As String)

' 一旦シート保護をかけ直す
With Worksheets(stSheetName)
.Unprotect

' スクロール範囲を解除する
.ScrollArea = ""

' 保護されたセルでも選択可能にする
.EnableSelection = xlUnlockedCells
End With
End Sub
Public Sub setBookAttribute(ByVal bFlg As Boolean)
With Windows(ThisWorkbook.Name)

' タブを設定
.DisplayWorkbookTabs = bFlg

' スクロールバーを設定
.DisplayHorizontalScrollBar = bFlg
.DisplayVerticalScrollBar = bFlg

' グリッドを設定
.DisplayGridlines = bFlg

' 行列数表示を設定
.DisplayHeadings = bFlg
End With
End Sub

↓シート側
------------------------------------------------------
Private Sub btnProtect_Click()

' アプリケーション全体の処理
Call Module1.offCmdBarAttr

' シート単位の処理
Call Module1.protectSheet("Sheet1")

' ブック単位の処理
Call setBookAttribute(False)

Worksheets("Sheet1").Activate
End Sub

Private Sub btnUnprotect_Click()
Call Module1.onCmdBarAttr
Call Module1.unprotectSheet("Sheet1")
Call setBookAttribute(True)
End Sub

以上、よろしくお願いいたします。

No.7 ベストアンサー 回答者： end-u 回答日時： 2009/05/24 16:28

前述した私の環境と[vista/xl2000SR1]の環境では

・[フォーム]のボタンを使う
・ActiveCell.Selectを入れる
・TakeFocusOnClick=False
それぞれで解消できます。
なので、もしかしたら現象がちょっと違うかも。
（違うというより、＋αでPosition = msoBarFloatingのCommandBarがある？）

だとすると、CommandBarを仕舞う処理を最後に（ScreenUpdating = Trueの後に）
持ってくると良いでしょう。

念のためbtnProtectのプロパティでTakeFocusOnClickをFalseにして、
Private Sub btnProtect_Click()
　　Call Module1.protectSheet("Sheet1")
　　Call setBookAttribute(False)
　　'この処理を最後に
　　Call Module1.offCmdBarAttr
　　Worksheets("Sheet1").Activate
End Sub

offCmdBarAttrの内容は以下。
Public Sub offCmdBarAttr()
　　Dim myCB As CommandBar

　　With Application
　　　　.ScreenUpdating = False '不要と思う。
　　　　.DisplayFormulaBar = False
　　　　.DisplayStatusBar = False
　　　　.ShowWindowsInTaskbar = False
　　　　.ScreenUpdating = True
　　　　'以下の処理を最後に
　　　　On Error Resume Next
　　　　For Each myCB In .CommandBars
　　　　　　'myCB.Visible = False
　　　　　　myCB.Enabled = False
　　　　Next myCB
　　　　On Error GoTo 0
　　End With
End Sub

#元コードの
#.CommandBars("Worksheet Menu Bar").Enabled = False
#.CommandBars("CELL").Enabled = False
#これは myCB.Enabled = False とダブってるので不要。

この回答へのお礼

end-uさん、何度もありがとうございます。

上記の方法でもなかなかうまくいかなかったので、
全体を見直して、ScreenUpdatingを使用せずに
回せるように書き換えてみることにしました。

本当にいつもありがとうございます。

今後とも、何かありましたらお願いいたします。

お礼日時：2009/05/24 16:50

No.6 回答者： myRange 回答日時： 2009/05/24 14:10

myRangeです。

当方(WinXp, xl2000sp-3)ではどうやっても再現できないので
はっきりとは言えないのですが、

end-uさんが指摘されたbtnProtectにFocusがある状態が原因だとすれば
提示のように、Activecell.Selectで上手くいくはずですね。

ま、ダメもとで、
即、btnUnProtectにFocusを当てるとかしてみたらどうでしょうか。


'-----------------------------------------
Private Sub btnProtect_Click()

●Sheets("Sheet1").btnUnProtect.Select

　　' アプリケーション全体の処理
　　Call Module1.offCmdBarAttr
'----------------------------------------


同じバージョンで現象が出たり出なかったりするわけですから
バグであることは確かでしょう。
　
当方では現象の再現を確認できませんので、
後はend-uさんにお任せ、ということで。(^^;;;
お役に立てず申し訳ないことです。
　
　
　

この回答へのお礼

myRangeさん、なんどもありがとうございます。

再現できませんでしたか…。

完全にExcelの『不具合』ということであれば、それはそれで
あきらめはつくのですが…。

なにはともあれ、何度もありがとうございました。
何とか回避方法を見つけて、対処したいと思います。

お礼日時：2009/05/24 16:46

No.5 回答者： end-u 回答日時： 2009/05/24 11:50

[フォーム]のボタンでも解消しませんでしたか？

もしくはCommandButtonのプロパティでTakeFocusOnClickをFalseにしてみるとかはどうでしょう。

次善の策として
>ScreenUpdatingを無効にしておかないと、一瞬でもユーザー
>情報が表示されてしまうため
これはコードを見直す事によって対応可能だと思います。
内容不明なので具体的には書けませんが表示やSelectしてなくても検索は可能ですが？

No.4 回答者： end-u 回答日時： 2009/05/23 16:35

ぁ...CommandButtonにフォーカスを残しておかなければいいので

Private Sub btnProtect_Click()
　　ActiveCell.Select '●
　　' アプリケーション全体の処理
　　Call Module1.offCmdBarAttr
　　' シート単位の処理
　　Call Module1.protectSheet("Sheet1")
　　' ブック単位の処理
　　Call setBookAttribute(False)
　　Worksheets("Sheet1").Activate
End Sub
これでもいいかも。

この回答への補足

end-uさん、いつもありがとうございます。

いただいた方法で試してみましたが(Activecell.Select)、
私のほうでは解消しませんでした。

現在はダミーでツールバーを表示させておく方法で、
回避していますが、できればきれいに解決したいものです。

ScreenUpdatingを無効にしておかないと、一瞬でもユーザー
情報が表示されてしまうため、なんとか解決しないといけないの
ですが…。

お手数をおかけしますが、引き続きお願いできたらと思います。

補足日時：2009/05/24 10:42

No.3 回答者： end-u 回答日時： 2009/05/22 22:15

[win2000sp4/xl2000sp3]の環境で、提示コードの２種類を試して再現しました。

実行後、CommandButtonがある『システム』シートに切り替えると出てくるようですね。
Application.ScreenUpdating = False を削除すれば出ないわけですから、
それで良いような気がしないでもないです。
（画面のチラつきも全然気になりませんが）

試してみてもいいかもしれないのは
Sub test()
　　Call offCmdBarAttr
　　Call protectSheet("Sheet1")
　　Call setBookAttribute(False)
　　Worksheets("Sheet1").Activate
End Sub
こんなコードを標準モジュールに置いて、
[コントロールツールボックス]ではなく[フォーム]のボタンにこのマクロを登録して実行してみるとか。
少なくとも私の環境では解消されます。

No.2 回答者： myRange 回答日時： 2009/05/22 21:18

いま、ScreenUpdating=False（画面更新をしない）を

標準モジュールの全てのプロシージャに入れて
最初のコードを実行してみましたが再現できません。

（補足要求）
（１）xl2000は、SP-3になってるでしょうか。
（２）補足のコードは最初のコードからすると大分削除されてますが
　　　それでも質問の現象が出るのでしょうか？
　　　　（当方ではこれも上手く動作します）


ScreenUpdatingをTrueにすると上手くいくが、
飽くまでも、Falseでやりたいということですね。
　
で、次を試してください。
ツールバー、数式バー、行列番号をFalseにする時に
APIのSleepを使って、200～300ミリ秒実行を止めてみてください。

（ご存知とは思いますが、Sleepの使い方）

●標準モジュールの宣言セクションで次の１行を宣言

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

●プロシージャでは、Sleep 止めたいミリ秒　とするだけです

Sub test55555()
　　MsgBox "aaa"
　　Sleep 300
　　MsgBox "bbb"
End Sub
　

　

この回答への補足

myRangeさん、続けてありがとうございます。
いただいた方法を試してみたのですが(Sleep)、
やはりだめなようです。

あの後試してみた結果、ツールバーを非表示にし、ScreenUpdatingを
falseにし、シートを切り替えると発生するようです。

サンプルのソースでは、必要なところのみ絞っているので、
ScreenUpdatingを行わなくても、大きな問題にはならないのですが、
実は本活用部分では、直前にユーザ情報を開き、そこから
ユーザIDとハスワードを検索するといった処理が入っているため、
Updatingを無効にしないと、一瞬とはいえ、ユーザー情報が
見えてしまうため、どうしてもUpdatingは無効にしておかないと
いけないのです。

現在では、『開く』と『閉じる』の二つのボタンをもったツールバー
を表示させておき、応急手段としています。

…が、やはり気分的には納得したいので、できればきちんと
解決できればと思います。

お手数をおかけいたしますが、よろしくお願いいたします。

補足日時：2009/05/24 10:34

No.1 回答者： myRange 回答日時： 2009/05/22 11:27

ｘｌ２０００で提示のコードを試してみましたが再現できませんでした。

考えられる原因としては、画面表示更新にあるようにも思えますが
提示のOnOffのプロシージャにはScreenUpdating = Falseがないので
ん？という感じですが。

で、ダメもとで
標準モジュールの全てのプロシージャに
強制的に、画面表示更新の
Application.ScreenUpdating = True
を入れてみたらどうでしょうか。


●それから、Excelのバージョンも提示しておいた方がベターだと思いますが。
　
　


　

この回答への補足

myRangeさん、ありがとうございます。
あれからいろいろ試してみたのですが、やはり
状況は変わりませんでした。後、最初に掲示したサンプルでは
頭にScrollUpdatingが抜けていました。これを行わなければ
問題の空白部分は出ないのですが…。記述漏れですみませんでした。

今回もサンプルを掲示します。必要な限り絞って調べてみました。

↓モジュール側
---------------------------------------------------
Option Explicit

Dim myCB As CommandBar

'*******************************

Public Sub onCmdBarAttr()

DoEvents

With Application

For Each myCB In Application.CommandBars
myCB.Enabled = True
Next myCB

.CommandBars("Standard").Visible = True

.CommandBars("Formatting").Visible = True

.CommandBars("Visual Basic").Visible = True

.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("CELL").Enabled = True

End With



End Sub

'******************************

Public Sub offCmdBarAttr()

Dim myCB As CommandBar

On Error Resume Next

Application.ScreenUpdating = False

For Each myCB In Application.CommandBars
myCB.Enabled = False
Next myCB

On Error GoTo 0

With Application
.CommandBars("Worksheet Menu Bar").Enabled = False
.CommandBars("CELL").Enabled = False

End With

Application.ScreenUpdating = True

End Sub

↓シート側
---------------------------------------------------
Option Explicit

Private Sub btnProtect_Click()

Call Module1.offCmdBarAttr

Worksheets("Sheet1").Activate
End Sub

Private Sub btnUnprotect_Click()
Call Module1.onCmdBarAttr

End Sub

なお、使用しているVerは2000ですが、2003でも同じ現象が
起きました。

以上、よろしくお願いいたします。

補足日時：2009/05/22 18:14