ExcelVBAでテキストボックスの数式バーに文字を入力したい

質問

ExcelVBAに関する質問です。

シート上にある表とグラフがあり、グラフ上にテキストボックスで表の合計値を表示させています。
その方法は以下の通りです。

1.表の[合計値]を求めるセルを作る。
2.シート上の見えない部分(例.グラフの裏側など)で、合計値に単位を付加する。
(例.セルC15に[=C10&"kwh"]と入力)
3.テキストボックスの数式バーに上で単位を付加したセルを入力する。
(例.数式バーに[=C15]と入力)

表とグラフを含めて75行で１日分となるので、毎日75行分をコピー＆ペーストして当日分の入力欄を作成しているのですが、表内の合計欄などはコピーすると参照セルがコピー先のセル番地に反映されるのですが、テキストボックス内のセル番地は反映されません。

これを一つ一つ直すのは大変なので、マクロなどを使ってボタン一つで直す(またはそのようなテキストボックスを作成できる)様にしたいのですが、VBAでテキストボックス内の数式バーをコントロールすることは可能なのでしょうか？

また同様にグラフのデータ範囲もコピー先に自動的に反映されないので、これもコントロールできる方法があれば教えて頂きたいと思います。

拙い知識で考えられる方法としては、

１．グラフ裏の見えないセルに、テキストボックスの参照先となるセル番地を記入(=[前日の参照番地]+75)
２．1日分のデータ範囲をコピーすると自動的に参照先のセル番地もコピー先に反映される
３．マクロを起動するとテキストボックスの数式バーに【"="&[当日の参照先セル]】と入力される
(または上記の様に入力されたテキストボックスを作成する)

グラフの方の対処方法は思いつきません。
ただグラフについてはボタン押しでグラフを作成する様にすればいいと思うのですが、シート上の体裁が崩れてしまうのでやりたくないのが実情です…

識者の方、よろしくお願い致します。

回答No.3

end-u · ベストアンサー

（ANo.1補足へのレス）
『グラフ上のテキストボックス』とは、
もしかしたら、グラフの上に独立したテキストボックスを単純に重ねたものではなくて、
チャートエリア内に作成するテキストボックスですか？
だとすると、ちょっと厄介な感じですね。
コピー後、一旦テキストボックスを削除して作成し直したほうが良さそうです。
（Excelのバージョンによるのかもしれませんが、コードでの設定だと不具合あり）
詳しく調べる時間が無かったので、作成し直す方向でコードを組んでみました。

Sub try2()
　　Dim r　As Range 'セル範囲格納用変数
　　Dim ch As Chart
　　Dim i　As Long
　　Dim arg

　　'コピー後範囲の左上セルをA1とした時のリンクセル相対アドレスを _
　　 TextBoxの数量分記述しておく
　　arg = VBA.Array("C1", "C2", "C3", "C4")
　　If TypeName(Selection) <> "Range" Then MsgBox "セル範囲選択要": Exit Sub
　　Set r = Selection.Offset(75)
　　Selection.Copy r
　　Set r = r.Resize(74, 2)
　　With ActiveSheet
　　　　Set ch = .ChartObjects(.ChartObjects.Count).Chart
　　End With
　　ch.SetSourceData Source:=r
　　ch.TextBoxes.Delete
　　For i = 0 To UBound(arg)
　　　　'TextBox設置位置を変数iを使って変動配置する
　　　　'...Add(Left,Top,Width,Height)
　　　　With ch.TextBoxes.Add(ch.ChartArea.Width - 50, 10 + i * 20, 40, 15)
　　　　　　.Border.ColorIndex = 0
　　　　　　.Formula = r.Range(arg(i)).Address(external:=True)
　　　　End With
　　Next

　　Set r = Nothing
　　Set ch = Nothing
End Sub

また、チャートエリア内テキストボックスの場合、
『グラフ裏の見えないセル』にリンクすると、
バージョンによっては不具合が出る可能性があります。
http://excelfactory.net/excelboard/exgeneral/excel.cgi?mode=all&namber=86471&rev=0
少なくとも私の環境（win2000sp4/xl2000sp3）では発生しました。
気をつけた方が良いかもしれません。

回答No.2

mshr1962

テキストボックス新規作成で良いなら
コピーしてから、Shiftを押しながら「編集」「図のリンク貼り付け」でリンク付きのテキストボックス（大きさはセルのサイズ）になります。

上記すら面倒なら、ツールバーのユーザー設定でコマンドのツールにある
"カメラ"をツールバーに設定してください。
セル選択して"カメラ"を押せばリンク付きのテキストボックスになります。

補足

コメントありがとうございます。

今回はコピーする方法がなんとか見つかりそうなのでコピーで行きたいと思います。

ご意見ありがとうございました。

回答No.1

end-u

こんにちは。
シート上の体裁が崩れないように（マクロ）ボタン押しでグラフ作成する事も可能ですが、
ご質問内容に添うと
『VBAでテキストボックス内の数式バーをコントロール』
Sheets(1).TextBoxes(1).Formula = "C15"

『グラフのデータ範囲もコピー先に自動的に反映されないので、これもコントロール』
Sheets(1).ChartObjects(1).Chart.SetSourceData Source:=データ範囲
...などとする事で可能です。

例題として
　　Ａ　　Ｂ　　Ｃ
１　項目　数値　（参照番地）
２　
：　　　　　　　（グラフ）
75　合計

のようなレイアウトで、コピーする範囲が A1:J75
グラフ範囲が項目名含めてA1:B74の74行2列
TextBoxにセットするアドレスがA1起点で1行目3列目（つまりC1）
...というケースを考えてみると、
最初にコピー元の A1:J75 セル範囲を『選択した上で』下記マクロ実行。

Sub try()
　　Dim r As Range 'セル範囲格納用変数

　　If TypeName(Selection) <> "Range" Then MsgBox "コピー元範囲選択要": Exit Sub
　　Set r = Selection.Offset(75)
　　Selection.Copy r
　　Set r = r.Resize(74, 2)
　　With ActiveSheet
　　　　.ChartObjects(.ChartObjects.Count).Chart.SetSourceData Source:=r
　　　　.TextBoxes(.TextBoxes.Count).Formula = r.Cells(1, 3).Address
　　End With
　　Set r = Nothing
End Sub

...という感じだと、コピーも一緒にできます。
グラフデータ範囲やTextBoxにセットするアドレスについては、
実際の位置関係によって修正が必要です。
わからない場合は補足お願いします。

補足

早速のご返答ありがとうございました！
グラフに関しましては求めていた通りの動作が可能となりました。

テキストボックスについてなのですが、実はグラフ上にテキストボックスが複数個存在するのですが、それぞれの参照先セル番地の相対位置を記述するにはどのようにしたらよろしいのでしょうか？
またそれぞれのテキストボックスのナンバーはどの様にして調べたらよいのでしょうか？
(グラフなどのオブジェクトのナンバーを知る方法はわかったのですがテキストボックスでは上手くいきませんでした…)

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