【VBA】特定シート 名前編集・削除を禁止したい

いつも大変お世話になっております。
Excel2003を使用しております。

今回VBAでやりたいことは、
特定シートの名前編集・削除を禁止したいのです。

シート名　「データ」
シート名　「一覧」

この二つを名前編集・削除禁止にしたいと思っております。
保護する方法も考えたのですが、
現在様々なプログラムでシートを利用しており、
保護をかけると色んなフォーム上で保護解除をしないといけません。
データの参照・編集が出来ないと困るのです。

保護をかける
以外で何か、方法はありますでしょうか？

以上、
回答よろしくお願い致します。

No.2 ベストアンサー 回答者： WindFaller 回答日時： 2014/02/22 13:58

こんにちは。

何年ぶりかで、このようなマクロを書いてみました。

普通は、ブックの保護のシート構成の保護だけで、良いはすですが、以下のようなマクロが可能かと思います。ただし、上位バージョン(2010)では、稼働しませんでした。また、2003では、現在、完全には試しておりません。たぶん、ご質問者さんでしたら、コードに間違いがあっても、修正できますでしょうから、今はヒントということだけにしてください。

また、本来は、FindControlsのID、847, 889が該当するのですが、バージョン2003ですと、Control配列になってしまいますので、その中を選択するという方法は、ちょっと手間が多すぎます。

'//ThisWorkbookに登録

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
　If Sh.Name = "データ" Or Sh.Name = "一覧" Then
　　With Application.CommandBars("Ply")
　　　.Controls("削除&(D)").Enabled = False '削除
　　　.Controls("名前の変更&(R)").Enabled = False '名前の変更
　　End With
　End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'戻し
　If Sh.Name = "データ" Or Sh.Name = "一覧" Then
　　With Application.CommandBars("Ply")
　　　.Controls("削除&(D)").Enabled = True
　　　.Controls("名前の変更&(R)").Enabled = True
　　End With
　End If
End Sub
'ブックを開いたり閉じたりする場合
Private Sub Workbook_Activate()
　If ActiveSheet.Name = "データ" Or ActiveSheet.Name = "一覧" Then
　　With Application.CommandBars("Ply")
　　　.Controls("削除&(D)").Enabled = False '削除
　　　.Controls("名前の変更&(R)").Enabled = False '名前の変更
　　End With
　End If
End Sub
Private Sub Workbook_Deactivate()
'戻し
　If ActiveSheet.Name = "データ" Or ActiveSheet.Name = "一覧" Then
　　With Application.CommandBars("Ply")
　　　.Controls("削除&(D)").Enabled = True '削除
　　　.Controls("名前の変更&(R)").Enabled = True '名前の変更
　　End With
　End If
End Sub

'//

この回答への補足

回答ありがとうございました！
遅くなりましたが、試してみました。

…思い通りの結果です！感激しました!!

助かりました＾＾ありがとうございました！
補足にてお礼することになり、申し訳ありません。
ありがとうございました！

補足日時：2014/02/25 08:54

この回答へのお礼

回答ありがとうございます！
マウスで右クリックした時に
名前の変更・削除　ボタンが無くなるVBAでしょうか？
"Ply"　というのをはじめてみました＾＾
まだまだ、勉強していかなきゃだめですね！

とて参考になりました！
ありがとうございました＾＾
早速試してみます！

お礼日時：2014/02/22 17:12

No.5 回答者： MarcoRossiItaly 回答日時： 2014/02/25 01:06

＞ブックの保護（シート構成保護？）をしてしまうと新規シートは作成できないのですよね？

はい。


＞特定のシートのみ、名前の変更・シート削除ができないようにするには、この方法ではダメ…なのでしょうか？

特定シートのみ、とはならず、全シートですね。だからこその「ブックの」保護なのでしょうね。


ちょうど No.3 さんが Worksheet.Protect メソッドを紹介されていますが、それとは別に VBA には、Workbook.Protect メソッドというものも用意されています。

workbooks("保護.xlsm").Protect password:="abcd"　　'ブックを保護
workbooks("保護.xlsm").unProtect password:="abcd"　　'ブックの保護を解除

つまり、VBA でデータ処理するたびに手動で予め保護を解除しておくのは煩わしいということであれば、保護の解除と再設定についても、ついでにマクロで Excel に作業させてしまえばよいでしょう。

こうなると、マクロもパスワードで保護したい、となるかもしれません。しかしマクロのコードは、必ずしも同じブックに保存しておく必要はないのです。

この回答へのお礼

回答ありがとうございます。
どう対応するのがいいか、色々考えましたが…
その場合ですと、たくさんのフォームプログラム上に
保護解除プログラムを入れないといけません。
そうなってしまうと、20以上？のフォームプログラムに
保護解除、終了時に保護　を入れなければならず
少し面倒だと思っております…

お礼日時：2014/02/25 08:49

No.4 回答者： MarcoRossiItaly 回答日時： 2014/02/23 02:09

質問文を読んでいて、ちょっと腑に落ちないのは、質問者さんは「シートの保護」と「ブックの保護」を区別されていますか？という点です。

「シートの保護」をすると、ロックのかかっているセルの編集・削除ができないようにできますが、シート名の編集・削除はできます。逆に「ブックの保護」をすると、シート名の編集・削除ができないようにできますが、セルは編集・削除ができます。どちらの機能も、パスワードを設定することも可能です。

「データの参照・編集が出来ないと困る」というのは、セルの参照や編集ができさえすれば保護が存在していても問題ないという意味であれば、ブックを保護すれば解決のような感じもしたのですが、いかがでしょうか。ま、保護を解除させないためにパスワードを設定したいが、その管理が煩わしいと言われたら、採用できない案かもしれませんが。

この回答へのお礼

回答ありがとうございます＾＾
確かに、「シート保護」と「ブック保護」の違いが
良く分かっておりませんでした。
詳しい説明ありがとうございます。

ブックの保護（シート構成保護？）をしてしまうと
新規シートは作成できないのですよね？
色々情報不足で申し訳ないのですが、
新規シートを使うときに作成し、編集したり…という
こともVBAでしています。（保護したいシート以外のシート）

特定のシートのみ、名前の変更・シート削除ができないように
するには、この方法ではダメ…なのでしょうか？

回答ありがとうございました！

お礼日時：2014/02/24 08:56

No.3 回答者： hallo-2007 回答日時： 2014/02/22 18:15

No1です。

では、もう一案
フォーム上のコードを変更せずに保護の解除と保護を行う案として

目的のシートには保護をかけておいて

フォーム上に、ユーザーフォームを表示させるボタンがあると思いますので

フォームの表示　.show の前に
Unprotect
.Show の後に
Protect



Sheets(保護がかかっているシート).UnProtect　Password:="pass"
Userform1.Show
Sheets(保護がかかっているシート).Protect　Password:="pass"


といった感じではどうでしょうか。
必要なコードはマクロの記録でも出来ると思います。

この回答への補足

フォームは20種類くらいあるので、大変な作業になるかと思われます。
また、workbook_Open に
Application.ScreenUpdating = False
Worksheets("データ").EnableAutoFilter = True 'データのシート　フィルター可能に
Worksheets("データ").Protect Password:="pass", userinterfaceonly:=True, AllowFormattingCells:=True
Application.ScreenUpdating = True

上記のような感じで、シートを3つ
保護かけています。
しかし、現状ではシート削除できてしまうので
どうにかしたいと思いました。


この方法だと、VBAで
データの編集・削除・追加ができるため
あまり変えたくなく思っております。

補足日時：2014/02/24 08:47

この回答へのお礼

回答ありがとうございます＾＾

お礼日時：2014/02/24 08:47

No.1 回答者： hallo-2007 回答日時： 2014/02/22 13:56

フォーム上から編集はしたいが

直接、シートを編集してもらっては困る
ということでしょうか。

一案ですが、そのシートを非表示にしておくでは
ダメなのでしょうか。

この回答へのお礼

回答ありがとうございます！
おっしゃる通りかとは思いますが、
表示にしたら見えてしまうので、あまり良くないのです…

ありがとうございました！

お礼日時：2014/02/22 17:09