表の行を増やすマクロの疑問

質問

見積書の表があります。
A1は欄外の空白セル、B1は内容欄、C1は数量欄、D1は単価欄、E1は金額欄です。

B2からE2は値を記入する欄としE2には数量＊単価の関数を埋め込みます。B3からE3までも形はそれと同じにし、E4が合計の値を記入する欄で関数は＝SUM(E2:E3)です。その上で第3行を「表示しない」とします。

必要なだけの行を随時追加してゆく為に、苦労して次のような動作をするマクロ記録を作りました。
A4にフォームのボタンを置き、A4セルをクリックした後にそのボタンをクリックすれば非表示になっている直ぐ上の行を表示させてコピーし、その場に表示行として新規挿入します。
つまりE4は繰り下がりE5になりますが、自動的に関数は＝SUM（E2：E4）となります。
そして新しく第4行を非表示としてやります。この繰り返しです。

記録されたマクロを見ると下のようになっています。

Sub 増行()
'
' 増行 Macro
' マクロ記録日 : ユーザー名 :
'

'
ActiveCell.Select
ActiveCell.Offset(-1, 0).Range("A1:E3").Select
Selection.EntireRow.Hidden = False
ActiveCell.Offset(0, 0).Range("A1:E1").Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Range("A1:E1").Select
Selection.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

このマクロはちゃんと機能するのですが、問題はこのシートの値を記入する場所以外を
保護しようとすれば「実行時エラー'1004'　RangeクラスのInsertメソッドが失敗しました」
と表示されます。
どうすればこれを避けて保護する事が出来るでしょうか。
よろしくお願いします。
なお私は超初心者なので自分でVBを記述したり意味を読み取る事はほとんど出来ません。

回答No.3

papayuka · ベストアンサー

#1です。
空行を挿入して、関数があるセルだけコピーすれば希望になると思います。
簡単に説明も入れておきましたので参考にして下さい。

Sub Test1()
Dim myRange As Range

　'アクティブシートに対して
　With ActiveSheet

　　'シート保護を解除
　　.Unprotect

　　'セル E65536 を選択してからCtrlを押しながら↑キーを押下
　　'で選択されるセルを変数 myRangeにセット（ようするにE列最終行）
　　Set myRange = .Range("E65536").End(xlUp)

　　'変数 myRange が含まれる行を選んで1行挿入と同じ意味
　　myRange.EntireRow.Insert

　　'変数 myRange から2つ上のセルをコピーして、変数 myRange から
　　'１つ上のセルに転送する
　　myRange.Offset(-2, 0).Copy Destination:=myRange.Offset(-1, 0)

　　'変数 myRange に SUM関数を再セット
　　myRange.Value = "=SUM(E2:" & myRange.Offset(-1, 0).Address(0, 0) & ")"

　　'シートを保護
　　.Protect

　End With
End Sub

お礼

超初心者の面倒なご質問に何度も丁寧にお答え頂き大変ありがとうございました。
お蔭様で希望通りの表を作る事が出来ました。

回答No.2

papayuka

#1です、出張中でしたので回答が遅くなりましたが、、、

> その行の合計欄が満たされた事をきっかけにしてシートの保護
> を回復してやるという手順なると思いますが

おっしゃる意味が良く解りません、、、
セルの書式設定で関数が入っているセルだけロックして、入力欄はロックを外して
シートを保護すれば良いだけに思います。
（例で言えば、E列だけセルをロックしてシート保護を掛ける）

こちらの解釈でサンプルを書きました。条件は下記の通りです。

　　B2:D2が入力用の欄（セルのロックはしない）
　　E2は関数 =C2*D2 を入力（セルをロック）
　　E3は関数 =SUM(E2:E2) を入力（セルをロック）
　　シートを 123 と言うパスワードで保護する

質問時の3行目（非表示行）を無くした状態で動くようにしたつもりです。
アクティブセルの位置も関係ありません。

Sub Test()
Dim myRange As Range
　With ActiveSheet
　　.Unprotect "123"
　　Set myRange = .Range("E65536").End(xlUp).Offset(-1, 0)
　　myRange.EntireRow.Copy
　　myRange.EntireRow.Insert
　　myRange.Offset(1, 0).Value = "=SUM(E2:" & myRange.Address(0, 0) & ")"
　　.Protect "123"
　End With
End Sub

補足

二度目のご回答ありがとうございました。

ご回答にあるサンプル表を作り、頂いた記述をコピペして実行して見ました。
アクティブセルはどこにあっても関係ありませんね、すごいです。一瞬びっくりした＝SUM(E2:E2)の変化態様も解りました。
しかし問題が一つあって疑問の解決に至っておりません。
私が非表示行を置いた意図をしっかりご説明しなかったのが悪かったのですが；

B2からD2までのセルに書き込むとします。例えば内容欄のB2＝鉛筆、数量欄のC2=12、単価欄のD2＝20と打ち込めば金額欄E2には240と表示されます。そこで次行の見積りを記入する為に空行(関数を打ち込んだセルを含む）を生成させようとA3にあるボタンをクリックすると、打ち込んだばかりの第2行が第3行にコピペされてしまい、それらのセルの値を削除しながら新規の値を打ち込むことになってしまいます。

実は非表示行（記入不可能な）を用意したのはこの問題を回避する為だったのです。
私には頂いた記述を改変して追加記述する力が未だありませんので、「関数を埋め込んだセルはロックされたままで、セルの値が記入されていない新しい空白行を合計行の直ぐ上に生成する」にはどうすればよいか、をSub～End Subの全体記述でお教え頂けると嬉しいです。シートのパスワードは不要です。

なお、お教え頂いた5行目の("E65536")は何を意味するのでしょうか。
実際に作りたい見積書はもうちょっと項目欄の数が多いので、この意味を知る事はポイントかなと思います。

何度も本当に申訳けありませんが、どうぞよろしくお願いいたします。

回答No.1

papayuka

シートを保護しているって事ならばエラーは当然ですよね。

マクロ記録でシート保護と解除を記録して、一番最初の行と
一番最後の行にコピペしてやれば良いのでは？

補足

ご回答有難うございます。
保護と解除をマクロに記録するのは良いアイデアですね、気付きませんでした。但しその時には増やした行への書き込みを終え、その行の合計欄が満たされた事をきっかけにしてシートの保護を回復してやるという手順になると思いますが・・・VBを独力で考える力は今の私にありません。

１）関数を埋め込んだセルは常に改変不可にして置きたい
２）関数を埋め込んだセルを含む行は随時増やしたい
この二つを同時に満たす事は出来るのでしょうか。