ExcelでVBAにおけるシートの保護に関して・・・

質問

ユーザーフォームで入力した値がシートのセルに入力
されるというものを作っています。

そこで、シートの列挿入及び列削除を禁止しておきたい
のですが、そのままシートを保護してしまうとユーザー
フォームが開かなくなるので、VBAにてユーザーフォーム
が開く前に保護を解除し、閉じるときに再び保護をかける
ようにしました。

ただ、この方法だと、シート保護の項目全て（初期設定）
に対して保護されてしまうため、例えば行挿入や行削除、
あるいはオートフィルタや並べ替えといったことまでが
出来なくなってしまいます。（手動で保護を解除すれば
できるのですが、一々解除しなければならないのは面倒
なので何とか避けたいのです。）

禁止したいのは、列削除及び列挿入のみなのですが、
他によい方法はないでしょうか。

回答No.4

Wendy02 · ベストアンサー

こんばんは。

おそらく、私（たち？)の想像したものとは、内容が違うと思います。
ROMしていた私も、#1 さんで、正解だと思っていました。
もし欲を言うなら、

　ActiveSheet.EnableAutoFilter = True
を加えることです。
特に、マクロのみなら、必要はありません。

私の試した例：

Private Sub UserForm_Initialize()
　ActiveSheet.EnableAutoFilter = True
　ActiveSheet.Protect UserInterFaceOnly:=True
End Sub

>フォームを開く過程でエラーが出てしまい断念しました。
もしかして、このプロパティがないとか？

>そのままシートを保護してしまうとユーザーフォームが開かなくなるので
ここが再現できません。フォームツールと、コントロールツール、両方のボタンで試してみました。また、Modal モードも変更してみましたが、Excel 2000 以上の仕様ですと、問題はないはずです。UserFormにその種の問題が起こるのは、97 だったように思います。

実際のところ、Excel のバージョンはいくつですか？

それから、#3 の回答のお礼の中のコードは、UserFormとシートのメソッドとまぜこぜで、それはちょっとうまくありません。ただ、どんなにイベントに工夫を凝らしても、この種のものはうまくいきません。多くの掲示板で、完全に出来ないもののひとつです。かろうじて、コマンド・ボタンにインスタンスを設けることは出来ますが、行や列自体の選択には取れません。

SelectionChangeイベントで、列の選択のTargetのアドレスでイベントを取るのですが、意図的に削除や挿入をしようとすると出来てしまいます。

お礼

どうもありがとうございます。

Excel のバージョンはXPです。

先程No.1さんの方法を試したときは私のミスですね。
再度試したところ問題なくフォームも開きました。
（ご迷惑をおかけしてすいませんでした）

思っていたものにそこそこたどり着けそうです。

とりあえず列の操作は禁止、みたいなことを
シートのどこかに表示しておきつつ、
教えて頂いた方法で進めてみます。

丁寧に教えて頂き本当に助かりました。
ありがとうございました。

回答No.6

starsip

'例えばこういう事？
’ユーザーには操作禁止（Protect）します。userinterfaceonly:=True
’例えばパスワード＝0000

Private Sub Workbook_Open()

Sheet1.Protect Password = "0000", userinterfaceonly:=True

end sub

'なのに、VBAでは操作出来ます。
'VBAで記述してみて試して下さい。

’ユーザー操作はNG
’VBAではOK

’もし質問意図と違ったならば補足を下さい。

お礼

ありがとうございます。
いろんな方法を勉強させて頂きました。

またよろしくお願いします。

回答No.5

zap35

＃０２です。
列単位の処理が全くできなくなってしまいますが、こんな手段はいかがでしょう。

列を選択したら強制的にその列の１行目をSELECTします。シートのモジュールとしてペーストしてください

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Rows.Count = 65536 Then
Selection.Cells(1.1).Select
MsgBox ("Ｓｏｒｒｙ 列単位の操作はできません")
End If
End Sub

お礼

zap35さん、お付き合い頂きありがとうございます。

なるほど、こんな事もできるんですね。
ちょっと覚えておきます。

それにしてもVBAはやっぱり本格的に勉強しないと
いけませんね。全くお恥ずかしい限りです。

またよろしくお願いします。

回答No.3

driverII

ワークシートの SheetChange イベントを使用して、

列が削除・挿入されていたら、Undo してやるのはどうでしょうか？

お礼

ありがとうございます。

試してみましたが、書き方が悪いようで
上手くいきません。

Private Sub Worksheet_Change(ByVal Target As Range)
Columns.Select
MsgBox "列の変更・削除はできません"
If Selection.Delete Then
Object.UndoAction
End If
If Selection.Insert Then
Object.UndoAction
End If
End Sub

としてみたのですが、試しに任意の列で挿入してみると
メッセージボックスが表示された後、画面のデータベースが
全て消えて再計算中となりものすごくマシンが重たくなって
（止まってしまう）しまいました。

一応、列を削除又は挿入したらメッセージを出して
元に戻す、というイメージなんですが・・・
勉強不足ですいません。

この場合はどのように書けばよいのでしょうか。
よろしくお願いします。

回答No.2

zap35

シートの１行目はデータ入力しますか？

そうではないなら、シート全体を保護セルにするのではなく、１行目だけを保護セルにしてください。

そして「ツール」→「保護」→「シートの保護」でプロテクトをかけます。

こうすれば列の削除、挿入はできませんが、２行目以降のデータは自由に操作可能です。

もし１行目もデータ入力することがあるなら、１行目にダミーの行を追加するか、別のデータ入力がない行を保護対象にすればよいでしょう

お礼

ありがとうございます。

仰るとおり、この方法が今の自分にとって
最も簡単な方法ではあるのですが、
この方法だと別のユーザーが保護を外して
行の操作をする恐れがあり最終手段として
保留しております。
パスワード保護という手もありますが、
できれば自動で列の操作を禁止できないかと
思っています。

もう少し調べてみたいと思います。

回答No.1

hana-hana3

フィルタやソートのパターンが決まっているならＶＢＡで行う事も可能です。

下記のような記述でプロテクトを掛けると、保護シートをＶＢＡで操作出来ます。

ActiveSheet.Protect UserInterfaceOnly:=True

お礼

ありがとうございます。

教えて頂いたやり方を試しましたが
フォームを開く過程でエラーが出てしまい断念しました。
（あまりVBA慣れしていないもので…すいません）
またよろしくお願いします。