2012年12月14日 11時
・大事な、コンテキストメニューの追加・リセット処理が抜けていたので追記しました。
1つのテンプレートエクセルをコピーして、複数人に配布、集計する流れが必要だったので、VBAでいろいろ処理を書いてみました。
その時、配布した際行列を追加・削除したり、キー項目を変更されるとまずいのでシートの保護を掛けました。
そのときにハマッタ事です。
配布するエクセルでやりたかったこと:
・列の削除や幅の変更は禁止したいけど、非表示・表示は出来るようにしたい。
コレを実現する為に、シートの保護を行い、列のコンテキストメニューに表示・非表示項目を追加して、VBAで列の表示・非表示が行えるようにしました。
なぜなら、シートの保護で"UserInterfaceOnly:=True"が使えるからっ!
ソースはコレ(標準モジュールにでも作る…):
ソースはコレ2(ThisWorkbookに作る…):
しかーし!!!!
作成された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 効かない
エラー 行を 列を 表示・非表示する方法
・大事な、コンテキストメニューの追加・リセット処理が抜けていたので追記しました。
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
ソースはコレ2(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 効かない
エラー 行を 列を 表示・非表示する方法