シートの保護でデータ追加が出来ない Excel

質問

表の項目で関数を入れているなど、データ入力以外さわらせたくない項目があるため、
シートの保護を設定するとデータの追加（新しいレコードの追加）が出来なくなりました。
（表をリストに変換しています。またセルの設定で「ロック」は外しています）
またマクロも実行時にエラーになります。
「シートの保護」の「～許可する操作」の設定をいろい変えてみたのですがうまくいきません。

リストへレコード追加やマクロ実行時が出来て、ユーザーには関係のない項目を触らせなくする方法は無いでしょうか。
エクセル２００３です。

回答No.6

Wendy02 · ベストアンサー

>今回教えていただいたマクロの設定についても ANo.2の方の説明が無ければどこに設定すればいいのやら分からないところです。

それは、「ANo.2の方の説明が無ければ」は、「ANo.2の方のように説明が無ければ」だとは思います。私のコードは、#2さんのとは違います。私は、マクロを中心に回答してきていますが、マクロの設定は分かりません、と書かれていなければ、そのような説明は書きませんし、それは、質問者さんに失礼になるからです。それに、設定場所を書いていたのにも関わらず、モジュールの名前すら分からないなら、まだ、VBAは分かりません、と書いていただいたほうが良いです。

>（表をリストに変換しています。またセルの設定で「ロック」は外しています）
私の#3で書いたものは、掲示板では一般的に知られたオーソドックスで、初歩的なマクロのひとつです。ただ、ここのカテゴリでは、半分ぐらいのマクロの質問者は、例えば、エラー回避は不要だ、コードは短ければよい、識別子も変数もメソッドもワークシート関数も、文字だから、皆同じだ、というような、どういう理屈なのか理解不能な反応を示されることが多いようですが。

>(リスト内に保護項目を設けること）

少し考えれば分かりますが、
リスト・オブジェクト内の「新規入力を許す」ことは、
　リスト・オブジェクトは生かす」ことです。
それなのに、
　「リスト・オブジェクトは保護されている」
ということでしたら、現在のExcel 2003の機能の範囲では、そのロジックは矛盾しています。
今回、そのブックの利用者にどこまでの利用を許すか、ということが明確に示されていません。

ほとんど、シート全体を保護しながら、おそらく不特定多数の人に入力させるのに、オブジェクトの入力自体をExcelの仕様に依存するということ考え方なら、それは間違っているのです。

こういう場合は、シートを全面保護して、入力する部分はテキストボックスなどを別に設けて、そこに入力させて、ボタンで新規に加入させればよいのだと思います。

しかし、私は、そもそも、なぜ、リスト・オブジェクトであるのか、という点にも着目されます。その点から考え方を変えなくてはなりません。リスト・オブジェクトとは何か、どういう機能か、それを自分で実現できるかどうかという検討をすべきだと思います。

数式のコピー自体で、リスト・オブジェクトであり続けようとするなら、それは、ナンセンスな選択です。フィルター機能だけなら、リスト・オブジェクトである必要はありません。リスト・オブジェクトは、Excelの絶対的な機能にはなりえないわけです。

基本的に仕様そのものを変えたいという質問は、ほとんど成功しません。

今回は、設定すら分からないということでしたので、残念ですが、これ以上、こちらが話を続けても難しいように思います。それに、基本的に、私は、イレギュラーなものは、お勧めしません。今のご質問者さんが、何が、レギュラーで、何がイレギュラーであるか、まだ分からないとは思いますが……。

お礼

回答ありがとうございました。
リストには直接入力させず、他で入力した内容を反映させる方法を考えてみたいと思います。
いろいろありがとうございました。

回答No.7

Wendy02

このまま尻尾をまいてしまうのは、一応、マクロの常連回答者としては、また、本意でもありません。このマクロは、空いている部分以外は、全部、セルがロックしてしまうものです。つまり、一旦入力したら修正が利きません。ご質問内容をそのまま反映すると、そういうことになるはずでず。

そのため、修正する場合は、LockedPr1というマクロが必要になります。必ず、最後のセルが空白が空くようになり、また、そのセルは、ロックがされていません。次に入力しようとすると、その入力したセルはロックされてしまいます。かなり、変則的なマクロです。

左側の列には数式があるという条件が必要だと思います。ワンセルずつ、間違いないように入力します。

'ThisWorkbook モジュール（必ず、この設定が必要です）
Private Sub Workbook_Open()
　With Worksheets("Sheet1") 'シートを指定
　 .Protect, UserInterfaceOnly:=True　　'マクロの実行を許可
　End With
End Sub

''シートモジュール
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　Dim r As Range
　Dim r1 As Range
　Dim col As Integer
　On Error Resume Next
　With Me.ListObjects(1)
　　Set r = Intersect(Target, .Range)
　　col = .Range.Columns.Count
　　Set r1 = .Range.Rows(.Range.Rows.Count)
　　Application.EnableEvents = False
　　Me.Unprotect
　　.Range.SpecialCells(xlCellTypeBlanks).Cells.Locked = False
　　.Range.SpecialCells(xlCellTypeConstants).Cells.Locked = True
　　.Range.SpecialCells(xlCellTypeFormulas).Cells.Locked = True
　　Me.Protect
　　Application.EnableEvents = True
　　On Error GoTo 0
　　If Not r Is Nothing And col = Application.CountA(r1) Then
　　　Me.Unprotect
　　　Application.EnableEvents = False
　　　.ListRows.Add
　　　Application.EnableEvents = True
　　End If
　　Set r = Nothing: Set r1 = Nothing
　　Me.Protect
　End With
End Sub

'おまけ
Sub LockedPr1()
　'保護と修正（トグル）
　If Me.ProtectContents Then
　　 Application.EnableEvents = False
　　Me.Unprotect
　　MsgBox Me.Name & " を保護を解除しました。", vbExclamation
　Else
　　Application.EnableEvents = True
　　Me.Protect
　　MsgBox Me.Name & " を保護しました。", vbInformation
　End If
End Sub

回答No.5

keithin

そうですね。。。
＞リスト内でも入力する列と計算式が入っている列があります。
＞入力して貰う項目以外は変更できない様にする方法は無いでしょうか

現時点では原則として，そういう方法は無いと考えてください。
例えばリストの自動延長や行削除に伴い，数式列には自動で式が投入されたり削除されます。そういう「望ましい形で式が記入されたセルが変更される」のと，あなたが恣意的に「望まない式の変更」を区別する方法を，いまの曖昧なご相談の中ではマクロにすることができません。回答の投げかけに対して，手抜きして適切なレスポンスも無いことも，間違いなく解決を遠くしている要因の一つです。

先の回答でも
＞使っていく内にまた違う+αの事がやりたくなるかもしれません。その時は適宜自力で改修してみてください。

と，事前にお話ししておきました。

実際の所先の回答も半分は，ご相談の事をとりあえず実現するだけでもこれだけ大変(イミフメイな事をしないとならない)ですよ，という事を実証するために提示したモノです。
ご自身で今後更に勉強を進め，将来のお仕事に役立てるようスキルアップしていけば，あなたが実際に目にしている片付けなければイケナイ仕事にきっちりと即した形であなたがご自分でマクロを検討することで，多分道は開けると思います。
それまでは今時点で実現出来た範囲で諦めるか，または全く手が出ないと全部を諦めて，とりあえず目前のお仕事を片付けてください。

回答No.4

Wendy02

#3の回答者です。

#3のコードで何も言わなくても分かるレベルの内容だと思いましたが、何もコメントがないようですから、使い方を書いておきます。ただ、このような初歩的な内容で、使用法まで書くというのは、私の本意ではありません。

//
リストの範囲に、クリックすれば、シートの保護は外れます。しかし、それ以外の部分を触ると、シートは保護されてしまいます。マクロに関しては、ProtectメソッドのInterFaceOnly モードにより、別途VBAから起動されるマクロが可動になっています。
//

一応、ご質問要件に満たしているかとは思いましたが、こちらの解釈しているものと、あまり違う反応を示すようでしたら、お答えできなくなることもあります。

補足

回答ありがとうございます、返事が遅くなってしまいました。
当方、マクロ、ＶＢＡは全くの初心者です。
年末に知人よりツールの作成を依頼され、仕事で少しは使ったことのあるExcelで作成を始めました。
マクロ、ＶＢＡは関連の本を数冊借りてきて読んでいるところです。
今回教えていただいたマクロの設定についても ANo.2の方の説明が無ければどこに設定すればいいのやら分からないところです。
正直、リストの内容についてもよくは分かりません。

教えていただいたマクロの設定を行ったのですが、リスト内へのデータ追加や修正は出来るのですが、触らせたくない列にはプロテクトがかからないようです。
これは設定が悪いのでしょうか、それとも無理なのでしょうか。（リスト内に保護項目を設けること）

回答No.3

Wendy02

必要に応じて、パスワードを入れてください。

'ThisWorkbook モジュール（起動時に設定）
Private Sub Workbook_Open()
　With Worksheets("Sheet1") 'シートを指定
　 .Protect, UserInterfaceOnly:=True　　'マクロの実行を許可
　End With
End Sub

'シートモジュール
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Object
　On Error Resume Next
　Set r = Intersect(Target, Me.ListObjects(1).Range)
　On Error GoTo 0
　If Not r Is Nothing Then
　　 Me.Unprotect
　Else
　　 Me.Protect
　End If
End Sub

回答No.2

keithin

シート保護すると，無条件にリストのいろんな機能が停止されますね。

＞リストへレコード追加やマクロ実行時が出来て、ユーザーには関係のない項目を触らせなくする

１．マクロを有効にしてエクセルとブックを開く
２．問題のシートのシート保護を停止する
３．シート名タブを右クリックしてコードの表示を選び，現れたシートに下記をコピー貼り付ける

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim O As ListObject
　Dim r As Range

　For Each O In ListObjects
　　If r Is Nothing Then
　　　Set r = O.Range
　　Else
　　　Set r = Union(r, O.Range)
　　End If
　Next

　On Error Resume Next
　If Application.Intersect(Target, r).Address <> Target.Address Then
　　Application.EnableEvents = False
　　Application.Undo
　　Application.EnableEvents = True
　End If

End Sub

ご質問でやりたかったリスト範囲に追加できます。リスト範囲外は編集できません。
簡易なマクロなので，使っていく内にまた違う+αの事がやりたくなるかもしれません。その時は適宜自力で改修してみてください。

＃シート上にリストが１つしかない限定条件なら，サンプルのマクロの前半部分は省略できます。勉強してみてください。

補足

回答ありがとうございます。
内容はよく分かりませんがやってみました。(^^;
リストへのデータの追加ができ、それ以外の項目に入力が出来なくなりました。
だた、リスト内でも入力する列と計算式が入っている列があります。
入力して貰う項目以外は変更できない様にする方法は無いでしょうか。

回答No.1

wret615

マクロの記録でシートの保護解除とかシートの保護とかやってみると、ヒントがつかめるんでね？「.Unprotect」とか「.Protect」とかな。