エクセルのグラフの不動化

質問

お世話になります。

2017年にエクセルでグラフを常に画面の決まった位置に表示させたい、とお願いして下記のご回答いただいて複数のシートで便利に使わせて頂いているのですが、下記のコードでは新しいグラフが右に追加されるのでいつの間にかデータ入力時にグラフうを見ないようになってしまっています。（5年間でグラフの枚数が増えていました）

そこで
①グラフは新らしい方から左から右に並べて表示させたい。
もしグラフの最大枚数を決めた方が分かり易いなら10枚で余ります。
但しシートによってグラフの枚数は異なります。
②更に可能ならグラフタイトルで左から右に並ぶ順番を指定出来れば更に汎用性が出ると思うのですがハードルが上がるようなら不要です。

シートによって画面構成が違っているので
③グラフを縦に、新らしいグラフから上から下に並べて表示するように出来ないでしょうか？

これも作成順に上から順か、出来ればグラフタイトルで指定出来れば最高です。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
w = 600
d = 25

For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
.Top = ActiveWindow.VisibleRange.Top + d
.Left = ActiveWindow.VisibleRange.Left + w
w = w + .Width
End With
Next i

End Sub

回答No.9

HohoPapa · ベストアンサー

タイトルが数字の羅列の場合があるんでしたね。
直して再ポストします。

Sub Sample1() '横方向に並べる

　Dim r As Long '行番号変数
　Dim c As Long '列番号変数
　Dim j As Long 'グラフカウンター
　Dim w As Long
　Dim d As Long

　r = 20　'グラフタイトル一覧開始行番号
　c = 2　' グラフタイトル一覧格納列番号
　w = 600
　d = 25

　Do

　　If Cells(r, c).Value = "" Then Exit Do

　　For j = 1 To ActiveSheet.ChartObjects.Count
　　　With ActiveSheet.ChartObjects(j)
　　　　If .Chart.ChartTitle.Text = Cells(r, c).text Then
　　　　　.Top = ActiveWindow.VisibleRange.Top + d
　　　　　.Left = ActiveWindow.VisibleRange.Left + w
　　　　　w = w + .Width
　　　　End If
　　　End With
　　Next j
　　r = r + 1

　Loop

End Sub

Sub Sample2()　'縦方向に並べる

　Dim r As Long '行番号変数
　Dim c As Long '列番号変数
　Dim j As Long 'グラフカウンター
　Dim w As Long
　Dim d As Long

　r = 20　'グラフタイトル一覧開始行番号
　c = 2　' グラフタイトル一覧格納列番号
　w = 600
　d = 25

　Do

　　If Cells(r, c).Value = "" Then Exit Do

　　For j = 1 To ActiveSheet.ChartObjects.Count
　　　With ActiveSheet.ChartObjects(j)
　　　　If .Chart.ChartTitle.Text = Cells(r, c).text Then
　　　　　.Top = ActiveWindow.VisibleRange.Top + d
　　　　　.Left = ActiveWindow.VisibleRange.Left + w
　　　　　d = d + .Height
　　　　End If
　　　End With
　　Next j
　　r = r + 1

　Loop

End Sub

お礼

何度ものご回答ありがとうございます。

グラフは入力者が入力時に見えるようにするためだけが目的なので、年のみや品番の数字のみのグラフタイトルも実在しますが、これは2023年や正式品名（ABC123）とすれば回避できることは確認していますので 全く支障はないのですが、当然ながらこの完成版を使わせて頂きます。

これを過去に教えて頂いた、直近（下から）〇個データのグラフ化マクロと組み合わせたら完璧の壁！！！

回答No.8

HohoPapa

既に解が示されていますがポストします。　(._.)

Sub Sample1() '横方向に並べる

　Dim r As Long '行番号変数
　Dim c As Long '列番号変数
　Dim j As Long 'グラフカウンター
　Dim w As Long
　Dim d As Long

　r = 20　'グラフタイトル一覧開始行番号
　c = 2　' グラフタイトル一覧格納列番号
　w = 600
　d = 25

　Do

　　If Cells(r, c).Value = "" Then Exit Do

　　For j = 1 To ActiveSheet.ChartObjects.Count
　　　With ActiveSheet.ChartObjects(j)
　　　　If .Chart.ChartTitle.Text = Cells(r, c).Value Then
　　　　　.Top = ActiveWindow.VisibleRange.Top + d
　　　　　.Left = ActiveWindow.VisibleRange.Left + w
　　　　　w = w + .Width
　　　　End If
　　　End With
　　Next j
　　r = r + 1

　Loop

End Sub

Sub Sample2()　'縦方向に並べる

　Dim r As Long '行番号変数
　Dim c As Long '列番号変数
　Dim j As Long 'グラフカウンター
　Dim w As Long
　Dim d As Long

　r = 20　'グラフタイトル一覧開始行番号
　c = 2　' グラフタイトル一覧格納列番号
　w = 600
　d = 25

　Do

　　If Cells(r, c).Value = "" Then Exit Do

　　For j = 1 To ActiveSheet.ChartObjects.Count
　　　With ActiveSheet.ChartObjects(j)
　　　　If .Chart.ChartTitle.Text = Cells(r, c).Value Then
　　　　　.Top = ActiveWindow.VisibleRange.Top + d
　　　　　.Left = ActiveWindow.VisibleRange.Left + w
　　　　　d = d + .Height
　　　　End If
　　　End With
　　Next j
　　r = r + 1

　Loop

End Sub

回答No.7

NuboChan

イベントで縦、横のどちらかで並び替えするかだけを選択したいなら下記のコードが利用できます。

dimでいらない宣言があるので削除して下さい。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim w As Long, d As Long, c As Long
Dim i As Long, j As Long, r As Long
Dim Gyt As Long, houkou As Long

w = 600
d = 25

R2:
houkou = Application.InputBox(Prompt:="横方向に並べる？　１" & vbCrLf & _
"縦方向に並べる？　２", Title:="どちら？ 1 or 2", Type:=1)

If houkou <= 0 And houkou > 2 Then
MsgBox "方向nの指定は、１又は２です。 再入力　！！"
GoTo R2
End If
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
.Top = ActiveWindow.VisibleRange.Top + d
.Left = ActiveWindow.VisibleRange.Left + w
if houkou=1 then
w = w + .Width
else
d = d + .Height
end if
End With
Next i
End Sub

お礼

本当に何度もの丁寧なご回答ありがとうございます。

縦、横の指定はよ～く見たら気付きました！
これで解決しました。

仕事で大きな試験結果表に幾つものデータをどんどん入力していくのでいつかグラフが上にズレて見えなくなるので改善したい、が最初のお願いでした。

大きな表だと1回の入力作業で20個程度のデータ入力がありますので、セルを移動するたびに縦、横を選択する仕様は許容出来ません。（この前提が質問に無かったのは申し訳ありません）

よって、最初に横並びでご回答いただいたHohoPapaさんのご回答をBSにさせて頂きますのでご了承ください。

このような場合（HohoPapaさんの回答の縦バージョン）があると思ったので

＞HohoPapaさんの回答を待ちたい
としたのですが、これも言葉足らずで本当に申し訳ありませんがご了承ください。

回答No.6

NuboChan

＞セルイベントにするのが主旨で、自動で決めた動きにしたいのです。

先に発言があった、
「パターンが横と縦だけなので標準モジュールを使う方が賢いですね。」
これって標準モジュール想定ではないのですか　？

＞HohoPapaさんのコードの縦バージョンが欲しい

マクロコードを見てもらえれば直ぐ判りますが？

これが、横方向
Do
If Cells(r, 5).Value = "" Then Exit Do
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
If .Chart.ChartTitle.Text = Cells(r, 5).Value Then
.Top = ActiveWindow.VisibleRange.Top + d
.Left = ActiveWindow.VisibleRange.Left + w
w = w + .Width
End If
End With
Next i
r = r + 1
Loop

以下が、縦方向

Do
If Cells(r, 5).Value = "" Then Exit Do
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
If .Chart.ChartTitle.Text = Cells(r, 5).Value Then
.Top = ActiveWindow.VisibleRange.Top + d
.Left = ActiveWindow.VisibleRange.Left + w
d = d + .Height
End If
End With
Next i
r = r + 1
Loop

よーく見てください。
違うのは、１箇所だけです。

回答No.5

NuboChan

横、縦のどちらに並べるか指定できるマクロです。
並び替えは、HohoPapaさんのコードをそのまま利用させていただきました。

D1から下方にシート内のグラフのタイトル名が書き出されるので
E1に下方にどのような順番で並び替えするかを記載して下さい。
（D列を適当にコピペすれば全てタイプインするよりかは少しは、手抜きできます。
　　　改造の余地がありますが初心者なので上手く処理できていません。）

書き出すセルや記入するセルは、適宜変更して下さい。

Sub 並べる()

Dim w As Long, d As Long, c As Long
Dim i As Long, j As Long, r As Long
Dim Gyt As Long, houkou As Long

w = 600
d = 25

Call タイトル名表示

R2:
houkou = Application.InputBox(Prompt:="横方向に並べる？　１" & vbCrLf & _
"縦方向に並べる？　２", Title:="どちら？ 1 or 2", Type:=1)

If houkou <= 0 And houkou > 2 Then
MsgBox "方向nの指定は、１又は２です。 再入力　！！"
GoTo R2
End If

r = 1
If houkou = 1 Then
Do
If Cells(r, 5).Value = "" Then Exit Do
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
If .Chart.ChartTitle.Text = Cells(r, 5).Value Then
.Top = ActiveWindow.VisibleRange.Top + d
.Left = ActiveWindow.VisibleRange.Left + w
w = w + .Width
End If
End With
Next i
r = r + 1
Loop
Else
If houkou = 2 Then
Do
If Cells(r, 5).Value = "" Then Exit Do
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
If .Chart.ChartTitle.Text = Cells(r, 5).Value Then
.Top = ActiveWindow.VisibleRange.Top + d
.Left = ActiveWindow.VisibleRange.Left + w
d = d + .Height
End If
End With
Next i
r = r + 1
Loop
Else
MsgBox "処理をキャンセル。"
Exit Sub
End If
End If
End Sub

Sub タイトル名表示()

Dim strObjName() As String
Dim intObj As Integer
Dim i As Integer, k As Long
Dim ltext

'アクティブシートのChartObjects数をカウント
intObj = ActiveSheet.ChartObjects.Count

ReDim strObjName(1 To intObj)
ReDim ltext(1 To intObj)

'配列strObjNameにタイトル名を代入
k = 1
For i = 1 To intObj
strObjName(i) = ActiveSheet.ChartObjects(i).Chart.ChartTitle.Text

ltext(i) = k & " : " & strObjName(i)

Cells(i, 4) = strObjName(i)
Cells(i, 3) = k

k = k + 1
Next i

'タイトルを表示
MsgBox Join(ltext, (vbCr))

End Sub

お礼

早々のご回答ありがとうございます。

しかし、質問が言葉足らずで主旨が違ってしまいました。
これはセルイベントにするのが主旨で、自動で決めた動きにしたいのです。
セルを変えるたびに毎回条件を指定するような使い方では実用不可です。（これが抜けていたのは申し訳ありません）

HohoPapaさんのコードそのままで、縦のプロシージャがあれば必要に応じてシートごとに不要な方のVBAを削除して使うつもりです。

よってHohoPapaさんのコードの縦バージョンが欲しいのですが。

質問の不備の為に非常なお手間をお掛けしたこと申し訳ありませんが、HohoPapaさんの縦バージョンを待ちたいと思います。

回答No.4

HohoPapa

グラフのタイトルの一覧を適当なセル範囲に縦方向に入力して起き
この順番にグラフを横方向に並べるということでよければ
こんなコードはいかがでしょうか。
Sampleなコードは、Ｂ列の20行目からタイトル一覧を並べた例です。

課題シートにグラフはあるが、この一覧に無いグラフは無視しています。

直観的には、視野から外れる場所に移動するのがよさそうですが
どうしたいかを明示すれば、マクロで対応することも可能と思います。

Sub Sample()

　Dim r As Long '行番号変数
　Dim c As Long '列番号変数
　Dim j As Long 'グラフカウンター
　Dim w As Long
　Dim d As Long

　r = 20　'グラフタイトル一覧開始行番号
　c = 2　' グラフタイトル一覧格納列番号
　w = 600
　d = 25

　Do

　　If Cells(r, c).Value = "" Then Exit Do

　　For j = 1 To ActiveSheet.ChartObjects.Count
　　　With ActiveSheet.ChartObjects(j)
　　　　If .Chart.ChartTitle.Text = Cells(r, c).Value Then
　　　　　.Top = ActiveWindow.VisibleRange.Top + d
　　　　　.Left = ActiveWindow.VisibleRange.Left + w
　　　　　w = w + .Width
　　　　End If
　　　End With
　　Next j
　　r = r + 1

　Loop

End Sub

補足

HohoPapaさん
毎度です。
今回も一発回答でした。
グラフタイトルはコード中で指定するように思っていましたがシート中に入力の方が汎用性高く使い勝手が良いですネ。

マクロをセルイベントにして横表示完成です。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

出来れば縦表示もお願いできませんでしょうか？
縦、横をどこで規定するのか想像つかず。

追記：特に困ることは無いのですが、グラフタイトルが数字の時は動かないようです。
最初は適当に数字を入れていたので全く反応せず。
HohoPapaさんのコードで「全く」は有り得ないと文字にしたら動きました。
タイトルが数字のグラフは消えることなく、置いてきぼりでこれもGood！！

回答No.3

NuboChan

>3σ外れ等のアラーム

何のこと判りませんが、相談内容には直接関係なさそうなので考えないことにします。

「最新データを含んだグラフが見えるようにしたいです。」件は、
　　初心者の私には荷が重そうなのでパスさせて下さい。

　標準モジュールを利用として
　横配置及び縦配置を選択するコードも必要との理解で良いのですか？

並び順の指定は、「グラフタイトルを指定」とありますが
タイトル一覧を抽出するとして並び順は、どのように指定するのですか？

仕様は、事前にしっかり決めて置かないと後から違う条件が出てきて
又考えるのは無駄な努力となります。

例えば、以下の場合で
GHJ>KLM>JJK>ABC>KLM の順番ならば　2,3,5,1,4 と指定するとか？

|[A]|[B]
[1]| |タイトル
[2]| 1|ABC
[3]| 2|GHJ
[4]| 3|KLM
[5]| 4|TYU
[6]| 5|JJK

回答No.2

NuboChan

追加です。

それと、現在は、ワークシートにコードを記載していますが
シートが複数あって、横配置及び縦配置の２つが有りこれをどちらを選択するかのような
汎用型なら標準モジュールにコードを記載したほうがいいと思いますが？

又、複数の配置でデータ入力時にグラフを見ないようになってしまうのであれば
一つ提案するとすれば
ユーザーファーム内に単一グラフのみを表示して表示を切る変えるような形態に変更する等が
利便性は良くなると思います。
（昔懐かしい、紙芝居のような表示方法ですが、
　　VBA初心者の私には荷が重いので一つのアイデアとしての提案のみです。）

補足

何度も恐れ入ります。

パターンが横と縦だけなので標準モジュールを使う方が賢いですね。
当方最初の事例でマクロをシートモジュールで使い始めたので古いマクロはシートモジュールのまま各Bookにコピペで使用しています。

紙芝居方式よりも自動で最新データを含んだグラフが見えるようにしたいです。

複数の担当者が入力するシートなので自分の入力したデータが過去のデータと比較して異常で無いかを「見える化」するのが目的です。

データを入力するセルには条件付き書式設定で、3σ外れ等のアラームが出るようになしてあるのですがグラフには別の効果的な異常検出効果がありますので。

回答No.1

NuboChan

仕様で不明点があります。

①について
シート内のChartObjectsが10個を超える場合は、余ったChartObjectsはどうするのですか？
超える分については、無視するとか？
１０個並び終わったら、その下に同じように１０個配置するとか？

②について
並び順は、①の操作後に替えられますが
どのような方法で並び替えの順番を指定するのですか？
具体的な指定方法の希望があれば記載下さい。

③について
①、②が解決すれば並びの方向変更は難しくありません。

補足

早々のご回答ありがとうございます。

10個を超えることはない前提ですが、もし今後超えた場合は無視でも良いと思っていたのですが、よく考えるとグラフの作成順だけでは残したいグラフと、並び順の指定が不便になる事に気付きました。
並べたい順に従って同じグラフを作り直す必要が出てくる場合があることに気付きました。

よって、出来ればグラフタイトルを指定して並び順を規定することはできないでしょうか？
これで何枚でも簡単に順番が決められるし、不要なグラフは無視できます。（多くはグラフ1 or 2枚のシートですので今まで意識せずに使ってました）

グラフタイトルでの指定がハードルが上がるなら、グラフを作り直す手間は大したことはないので作成の「逆順」でも良いのですが、だんだん欲が出てきて、出来る事ならのお願いです。