テキストボックスの色変更

質問

自動でテクストボックスの色変更をしたいのですが、

前回こちらにて質問をさせていただき大変助かったのですが、
さらに、問題に突き当たりました。

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("R8: S341 ")) Is Nothing Then Exit Sub
If Cells(Target.Row, "R").Value < -10 Then
c = 10
Else
Select Case Cells(Target.Row, "S").Value
Case Is > -89
c = 17
Case Is < -100
c = 10
Case Else
c = 12
End Select
End If
With Sheets("sheets2").Shapes("テキスト " & Target.Row)
.Line.ForeColor.SchemeColor = c
.TextFrame.Characters.Font.ColorIndex = c - 7
End With
End Sub

にて、色と文字色の変更はできたのですが、
Ｒ列、Ｓ列を関数で読み込んでくると自動では変わりません。
手動で打ち込むと変わるのですが
一緒に文字サイズの指定もできるのでしょうか？

宜しくお願いいたします。

回答No.5

merlionXX · ベストアンサー

考えたんですが、3つ飛ばしでやるということはテキストボックスの番号も3つ飛ばしで作るんですよね。これ面倒じゃないですか？

いっそテキストボックスは1～112と連番で作って、参照するセルを、
テキストボックス 1→8行目
テキストボックス 2→11行目
テキストボックス 3→14行目　にしたらいかがでしょう？

n = (i - 1) * 3 + 8　で、1から連番の i を、8から3つおきの n に変換してみます。
ためしていませんがこんな感じかな。

Private Sub Worksheet_Calculate()

For i = 1 To 112

n = (i - 1) * 3 + 8
If Cells(n, "R").Value < -10 Then
c = 10
Else
Select Case Cells(n, "S").Value
Case Is > -89
c = 17
Case Is < -100
c = 10
Case Else
c = 12
End Select
End If

With Sheets("Sheet2").Shapes("テキスト " & i)
.Line.ForeColor.SchemeColor = c
.TextFrame.Characters.Font.ColorIndex = c - 7
End With

Next i

End Sub

お礼

有難う御座います。
これで今後の作業が大変楽になります。

最後と言いながらもうひとつ質問です。

現在テキストボックスはＴ列を書式で参照しています。
Ｒ・Ｓどちらかが０ならテキストボックスに「ＮＧ」と表示、
それ以外の場合は、Ｔの数値を表示するようにしたいのですが、
可能でしょうか？
テキストボックスの枠・文字は赤にしたいのです。

回答No.6

merlionXX

> 現在テキストボックスはＴ列を書式で参照しています。

「書式で参照」という意味がわかりません。
T列のセルの値を＝で参照しているんじゃないんですか？

> Ｒ・Ｓどちらかが０ならテキストボックスに「ＮＧ」と表示、
> それ以外の場合は、Ｔの数値を表示するようにしたいのですが、

それなら、Tの数値をＲ・Ｓどちらかが０なら「ＮＧ」となるように数式を組めばいいのではないですか？マクロでテキストボックスの参照を変えようとするとまた作動時間が増えますよ。T列の数値を数式に変えるのがやっかいなら、別の列にＲ・Ｓどちらかが０なら「ＮＧ」そうでないならTの数値をもってくるような式をいれてそのセルを参照させればいいのでは？

テキストボックスの線や文字の色の変更方法はもうお分かりですよね。

お礼

申し訳ありません。

一応、いろいろと試してみたのですが、
わかりませんでした。

現在、Ｔに
=IF(D8="","",IF(Q8=""," "&G8&"
"&S8&"/"&R8,"["&Q8&"]"&G8&"
"&S8&"/"&R8))
と、数式が入っています。
３段に分かれている状態です。

いろいろな場所に
IF(R8=0,"NG")
を入れてみましたが、うまくいきません。

宜しければ、ご教授いただけませんでしょうか？

補足

有難う御座いました。

色々と悩みましたがなんとかなりました。

素人の質問に付き合って頂き感謝しております。

回答No.4

merlionXX

For i = 8 To 341 を
For i = 8 To 341 Step 3　に変えてみてください。
３個づつ飛ばすことになります。

回答No.3

merlionXX

> 実行時エラー'2147024809(80070057);
> 指定したアイテムがみつかりません。

> With Sheets("Sheet2").Shapes("テキスト " & i)
> がやはり黄色く塗りつぶされています。

"テキスト " & i　の i には8～341の数値が代入され、テキスト 8からテキスト 341を指定しています。
指定したアイテムがみつかりませんということは、該当する番号のテキストボックスがたとえひとつでも見当たらないと、そういうエラーになります。

コードが黄色く塗りつぶされているとき、そこのiにカーソルを持っていくと何番かわかると思います。

お礼

有難う御座います。
なんとか動かすことが、できました。

これで最後にできればいいのですが、
実際のＲ列・Ｓ列は３つのセルが結合されています。
Ｒ８・Ｒ９・Ｒ１０で１つ、次はＲ１１・Ｒ１２・Ｒ１３で１つになっています。
Ｓ列も同じです。
テキストボックスを９・１０も作っておけばいいのですが、
結構面倒なので、３個づつ飛ばしてといったことはできるのでしょうか？

宜しくお願いいたします。

回答No.2

merlionXX

> With Sheets("sheets2").Shapes("テキスト " & i)
> の部分が黄色く塗りつぶされています。

だからシート名が違うのでは？
テスト用で、シートの名前を変えていないのならSheets("Sheet2")じゃないんですか？（sheets2ではsが余分じゃないですか？）

お礼

すいません

シート名、今気づきました。
書き込もうと思ったら、返事が来ていました。
すいません。

次は、違うエラーが出ました。

実行時エラー'2147024809(80070057);
指定したアイテムがみつかりません。

と表示されます。

デバッグを押すと
With Sheets("Sheet2").Shapes("テキスト " & i)
がやはり黄色く塗りつぶされています。

ボケボケですいません。

回答No.1

merlionXX

http://dospara.okwave.jp/qa4702676.html の続きですね。
前回の前提がわからないと回答はつきにくいと思いますよ。

Sheet1のS列R列の数値によりSheet2のオートシェープのテキストボックスの色を変えるのですね？
で、Sheet2のオートシェープのテキストボックスは8～341まであって、この番号が、それぞれSheet1のS列R列の行番号に一致しているのですよね？

> Ｒ列、Ｓ列を関数で読み込んでくると自動では変わりません。

Ｒ列、Ｓ列の数値をどこかのセルが関数で読み込むのではなく、Ｒ列、Ｓ列が他のセルを参照しているのですよね？正しく書いてください。

以上の前提で回答します。
Worksheet_Changeイベントですから変わらなくて当然です。
前回の回答はSheet1に手動で打ち込み、Sheet2のテキストボックスに反映させるのですから計算式で読み込んだだけではWorksheet_Changeイベントは生じません。
関数で持ってきた結果を反映させたいなら、Worksheet_Calculateイベントを使うことになります。
ただし、この場合、Targetという概念がなくなるのでSheet1に計算が生じた場合、Sheet2のすべてのテキストボックスにつき一個一個、Sheet1のＲ列、Ｓ列の数値をみていくことになり多少時間がかかります。

Sheet1のモジュールに以下のように記述してみてください。

Private Sub Worksheet_Calculate()

For i = 8 To 341
If Cells(i, "R").Value < -10 Then
c = 10
Else
Select Case Cells(i, "S").Value
Case Is > -89
c = 17
Case Is < -100
c = 10
Case Else
c = 12
End Select
End If

With Sheets("sheets2").Shapes("テキスト " & i)
.Line.ForeColor.SchemeColor = c
.TextFrame.Characters.Font.ColorIndex = c - 7
.TextFrame.Characters.Font.Size = 10
End With
Next i

End Sub

でも、この　With Sheets("sheets2").Shapes("テキスト " & i)　って、シート名は合ってますか？

なお、フォントサイズも変えたいなら
.TextFrame.Characters.Font.Size = 10
のようにします。

お礼

何度も有難う御座います。
試してみたのですが、エラー表示が出ます。

実行時エラー'9';
インデックスが有効範囲にありません。

と表示されます。
デバッグを押すと、

With Sheets("sheets2").Shapes("テキスト " & i)

の部分が黄色く塗りつぶされています。

ためしにエクセルの新規にて、
R8のセルに　=A1、S8のセルに　=B1　として参照させ
A1のセルに数値を入力したところこうなりました。

シート名は実際のときに変更します。

宜しくお願いいたします。