Excel の VBAにやられた…

2012年12月14日 11時
・大事な、コンテキストメニューの追加・リセット処理が抜けていたので追記しました。



1つのテンプレートエクセルをコピーして、複数人に配布、集計する流れが必要だったので、VBAでいろいろ処理を書いてみました。

その時、配布した際行列を追加・削除したり、キー項目を変更されるとまずいのでシートの保護を掛けました。
そのときにハマッタ事です。

配布するエクセルでやりたかったこと：
・列の削除や幅の変更は禁止したいけど、非表示・表示は出来るようにしたい。


コレを実現する為に、シートの保護を行い、列のコンテキストメニューに表示・非表示項目を追加して、VBAで列の表示・非表示が行えるようにしました。
なぜなら、シートの保護で"UserInterfaceOnly:=True"が使えるからっ！


ソースはコレ(標準モジュールにでも作る…)：

'*****************************************************************************************************
' 列のコンテキストメニューに追加する
' パラメータ：キャプション(表示名称)
' パラメータ：アクション（実行するサブルーチン）名
' パラメータ：グループを分ける（true）、分けない（false）
' 戻り値　　：なし
'*****************************************************************************************************
Public Sub addMenu(ByVal inCaption As String, ByVal inAction As String, ByVal inBiginGroup As Boolean)
    Dim Newb
    
    On Error Resume Next
    Application.CommandBars("Column").Controls(inCaption).Delete
    
    ' before:=1 は一番上に表示する。Temporary:=True 今のエクセルだけ
    Set Newb = Application.CommandBars("Column").Controls.Add(before:=1, Temporary:=True)
    With Newb
        .Caption = inCaption
        .OnAction = inAction
        .BeginGroup = inBiginGroup
    End With
    
    On Error GoTo -1
    
End Sub

'*****************************************************************************************************
' 列のコンテキストメニューから削除する
' パラメータ：キャプション(表示名称)
' 戻り値　　：なし
'*****************************************************************************************************
Public Sub delMenu(ByVal inCaption As String)
    
    On Error Resume Next
    
    Application.CommandBars("Column").Controls(inCaption).Delete
    
    On Error GoTo -1
End Sub

'*****************************************************************************************************
' 全てのコンテキストメニューを元に戻す
' パラメータ：なし
' 戻り値　　：なし
'*****************************************************************************************************
Public Sub allReset()
    
    Application.CommandBars("cell").Reset    'セルのショートカットメニューのリセット
    Application.CommandBars("column").Reset  '列のショートカットメニューのリセット
    Application.CommandBars("row").Reset     '行のショートカットメニューのリセット
    Application.CommandBars("curve").Reset   '線と矢印のショートカットメニューのリセット
    Application.CommandBars("shapes").Reset  '図形のショートカットメニューのリセット
    Application.CommandBars("ply").Reset     'シート見出しのショートカットメニューのリセット
End Sub

'*****************************************************************************************************
' 列のコンテキストメニューに表示、非表示メニューを追加する
' パラメータ：なし
' 戻り値　　：なし
'*****************************************************************************************************
Public Sub addColumnMenu()
    
    Call Shortcut.addMenu("列の再表示", "showColumn", False)
    Call Shortcut.addMenu("列の非表示", "hiddenColumn", True)
End Sub

'*****************************************************************************************************
' 選択中の列を表示する
' パラメータ：なし
' 戻り値　　：なし
'*****************************************************************************************************
Private Sub showColumn()
    
    For Each col In Selection.EntireColumn
        
        col.Hidden = False
    Next
End Sub

'*****************************************************************************************************
' 選択中の列を非表示にする
' パラメータ：なし
' 戻り値　　：なし
'*****************************************************************************************************
Private Sub hiddenColumn()
    
    Selection.EntireColumn.Hidden = True
End Sub

ソースはコレ２(ThisWorkbookに作る…)：

' ブックを閉じた時の処理
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Call Shortcut.allReset
End Sub

' ブックを開いた時の処理
Private Sub Workbook_Open()
    
    Call Shortcut.addColumnMenu
    
    ' 各シートにVBAから処理できるように設定
    Dim setSheet As Worksheet
    For Each setSheet In ThisWorkbook.Sheets
        
        ' シート保護のパスワード
        Call setSheet.Protect(Password:="パスワード", _
                              UserInterfaceOnly:=True)
    Next
End Sub

' ブックがアクティブになった時の処理
Private Sub Workbook_WindowActivate(ByVal Wn As Window)

    Call Shortcut.addColumnMenu
End Sub

' ブックが非アクティブになった時の処理
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

    Call Shortcut.allReset
End Sub

しかーし！！！！
作成されたExcelを開いて、コンテキストメニューから列の表示・非表示をすると、なぜかエラーが出るんですよね。
エラー内容：
実行時エラー '1004'：
Range クラスの Hidden プロパティを設定できません。

なぜだっ！VBAからの変更は許可してるじゃないかっ！！！！
……怒ってもしょうがないので、VBAが得意な人に聞いた…結果っ！
下記内容とURLを教えてもらいました。

「UserInterfaceOnly:=True」の有効期間はそのワークブックを閉じるまでで、開いた時には再設定が必要です。このため、立ち上げ時のマクロに記述しています。

参考URL：http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_030_040.html
：http://support.microsoft.com/kb/810788/ja



そんな馬鹿な…
いやぁ～参りました。

注意：
シート保護で、パスワードを設定している場合、「UserInterfaceOnly:=True」を設定するだけでもパスワードが必要になります。
その為、パスワード保護が掛かっているExcelは注意してください。
パスワードを配布用ExcelのVBAに直接書くのは気が引けますしね…

私のやりたかったこともパスワードを付けた保護なので、意味が無くなった…orz


検索用：Excel VBA シートの保護 VBAから操作 Protect UserInterfaceOnly 効かない
エラー 行を 列を 表示・非表示する方法