エクセルで行挿入した際、自動的に計算式も入力される方法を教えて下さい。

質問

例えば、A列は単価を入力する欄、B列には数量を入力する欄、C列には=A1*B1(１行目の場合）の計算式が入っている表があるとします。
表の途中で行挿入すると、自動的にCのセルに数式が入力される方法はありますでしょうか？
複数の人に毎月この表（実際はもっと列が多い表）に入力して貰って集計等をしているのですが、各個人で表の途中で行挿入して行を増やした際、数式がコピーされてない人がいるため、行挿入すると自動的に数式が入力される方法がないかと思っております。
エクセルのバージョンは2000又は2003、行挿入は必要なので、行挿入を禁止する事はできません。
宜しくお願いします。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

Excel2003 ですと、その場所をリストに変更しておけば、自動的に数式は入ります。

Excelの2000の場合ですが、簡単に言えば、行の挿入のそれぞれのボタンにインスタンスを設ければよいわけですが、結構、これが複雑です。以下のようになります。（Excel2003でも可能です)

コマンドボタンにインスタンスを適用したので、他のブックにも影響します。出来れば、ブック単独でお使いになったほうがよいと思います。

ブックを開いた時に設定され、ブックを閉じた時に、解除されます。
単独で、起動する場合は、Auto_Open 、単独で解除する場合は、Auto_Close を実行すれば、行えます。

'----------------------------------------------------------
'以下Class1 モジュール

Private WithEvents myBtn As CommandBarButton

Public Property Set Btn(ByVal myNewBtn As CommandBarButton)
　　Set myBtn = myNewBtn
End Property

Private Sub myBtn_Click _
　　(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Dim c As Range
　　If Selection.Columns.Count <> Columns.Count Then Exit Sub
　　CancelDefault = True
　　Application.ScreenUpdating = False
　　Selection.Copy
　　Selection.Insert Shift:=xlShiftDown
　　Selection.PasteSpecial xlFormulas
　　For Each c In Selection
　　　If c.HasFormula = False Then
　　　　c.ClearContents
　　　End If
　　Next c
　　Application.CutCopyMode = False
　　Selection.Cells(1).Select
　　Application.ScreenUpdating = True
End Sub

'===================================================
'以下標準モジュール

Private myClass1() As Class1
Sub RowInsertButtonEvent()
　Dim myCtrl As CommandBarButton
　Dim i As Long
　On Error GoTo ErrHandler
　　　For Each myCtrl In Application.CommandBars("Row").Controls
　　　　　If myCtrl.Caption Like "挿入(&I)" Then
　　　　　ReDim Preserve myClass1(i)
　　　　　　　Set myClass1(i) = New Class1
　　　　　　　Set myClass1(i).Btn = myCtrl
　　　　　i = i + 1
　　　　　End If
　　　Next
　For Each myCtrl In Application.CommandBars("Worksheet Menu Bar"). _
　　　Controls(4).Controls
　　　If myCtrl.Caption Like "行*" Then
　　　　ReDim Preserve myClass1(i)
　　　　Set myClass1(i) = New Class1
　　　　Set myClass1(i).Btn = myCtrl
　　　　i = i + 1
　　　　Exit For
　　　End If
　Next
ErrHandler:
　If Err.Number > 0 Then
　　MsgBox "設定に失敗しました", vbCritical
　End If
End Sub

'----------------------------------------------------------
Sub Auto_Open
'起動時に設定
　Call RowInsertButtonEvent
End Sub
'----------------------------------------------------------
Sub Auto_Close
'終了時に設定解除
　Erase myClass1()
End Sub
'----------------------------------------------------------

お礼

ご回答、真にありがとうございました。
Excel2003で「リスト」を試したら、出来ました！
悩みが一気に解決！感激＆感謝です！
Excel2000でもこのような処理をしているので教えて頂いた方法でやりたいのですが、VBAは初心者のため、高度な記述はVBAをもっと勉強してから試してみたいと思います。
この度は本当にありがとうございました。
何とお礼を言ったら・・・という感じです。
ご親切に心より感謝申し上げます。

回答No.2

papayuka

行を挿入したらでは無いですが、、、
入力者にとってその計算式は無くても影響がないものなのでしょうか？
であれば、保存時に式を埋めてしまうとか。

例えば、ThisWorkbookモジュールに

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
　With Worksheets(1)
　　　 .Range("C1", .Range("A65536").End(xlUp).Offset(0, 2)).Formula = "=A1*B1"
　End With
End Sub

とすると保存時にA列最終行を判断してC列に式を入れます。

お礼

ご回答、真にありがとうございます。
入力者は数式が無くても構いません（今でも行挿入しても計算式をコピーしないで提出してくるぐらいですし）。
教えて頂いた方法、例の状態で試した所、出来ました！
ただ、そうするとC1の項目名まで数式になってエラーが出てしまいましたが、表自体には数式がコピーされているので、私の作業には問題ないと思います。
実際作業しているシートでも試して見ます。
本当にありがとうございました。

回答No.1

kamejiro

質問者さんの理想な回答かどうかわかりませんが、

例えば、A列は単価を入力する欄、B列には数量を入力する欄、C列には=A1*B1(１行目の場合）の計算式が入っている表があるとします。

では、この例の場合で、３行目と４行目の間に２行目の行の式を行挿入をしたい場合を想定します。

２行目をアクティブにして、右クリック→コピー(C)
４行目をアクティブにして、右クリック→コピーしたセル(E)を挿入

これで、計算式の入った行を入れ込むことができると思います。ただし、２行目のA列、B列に値がある場合、その値も貼り付けられます。

お礼

早速のご回答、真にありがとうございます。
行挿入操作をする人の手順というよりは、「誰かが行挿入をしたら、自動的に同じ列の計算式が入力される表（ファイル）を作る方法があれば知りたい（エクセルの機能、マクロ、VBA等）と思っております。
質問の書き方が悪くて申し訳ありませんでした。