メッセージボックスを表示させるエクセルマクロ

質問

こんにちは。マクロ初心者です。
エクセル(Excel2003)でメッセージボックスを
表示させるマクロが思うようにいかず困っています。

Ｂ列に「○○会社」と入力されれば、
「取引先です。」 とメッセージボックスを表示させたいと思い、
次のとおりマクロを作成しました。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target Like "*会社" Then
MsgBox "取引先です。"
End If
End Sub
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

しかし、コピーなどで複数のセルを貼り付ける(入力)行為をすると、
「実行エラー'13'：　型が一致しません」と出てしまいます。
Worksheet_Change(ByVal Target As Range)を使っているので、
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 And Target Like "*会社" Then
MsgBox "取引先です。"
End If
End Sub
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
と、「If Target.Count > 1 Then Exit Sub 」を入れれば、
メッセージは出なくなるのですが、
これだと、Ａ列セルに、コピー＆ペーストで複数セルを貼り付けた場合、
「○○会社」があっても、マクロが効いてきません。

複数セルの貼り付けにも対応させるには、
どのようにすればよろしいでしょうか？
基本的なところが理解できていないのだと自覚しておりますが、
どうかご教授願います。
長々とわかりづらい文章ですみません。よろしくお願いします。

回答No.5

Wendy02 · ベストアンサー

こんばんは。

複数セルを貼り付けたときに、MsgBox "取引先です。"
なんていうメッセージが必要なのでしょうか。たぶん、質問とは別に実際は違うものがあるのだと思います。

.Value プロパティを付ける云々は、イベントマクロでは大きく問題にはなりません。インストラクターの田中亨氏(OfficeTanaka)が、付ける付けないについて、曖昧な書き方をしていたようですが、ちょっと違う意味があるようです。付けないで、Valueプロパティが返るので、付けない方法のことを、暗黙のプロパティと呼んでいます。しかしExcelの特殊な使い方によって、この暗黙のプロパティが働かないことがあります。その時に、エラーが発生します。だから、付けたほうがよいというのです。

>コピーなどで複数のセルを貼り付ける(入力)行為をすると、
>「実行エラー'13'：　型が一致しません」と出てしまいます。

Target.Count が、2　以上になっている場合は、Target.Value は、配列になって、複数の値が格納されています。

少し、勉強してみてください。
'------------------------------------
'Range形式

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim c As Range 'c は、cells の頭文字
　　If Target.Column <> 2 Then Exit Sub
　　For Each c In Target
　　　　If c.Value Like "*会社" Then
　　　　　　MsgBox "取引先です。"
　　　　End If
　　Next c
End Sub

'------------------------------------
'配列形式

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim c As Variant
　　If Target.Column <> 2 Then Exit Sub
　　For Each c In Target.Value　'←Value プロパティを入れる
　　　　If c Like "*会社" Then
　　　　　　MsgBox "取引先です。"
　　　　End If
　　Next c
End Sub

'---------------------------------------
'関数を使うと

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Column <> 2 Then Exit Sub
　　If WorksheetFunction.CountIf(Target, "*会社") Then MsgBox "取引先です。"
End Sub

お礼

Wendy02さん、こんにちは。

＞たぶん、質問とは別に実際は違うものがあるのだと思います。
ご指摘のとおりです。実際には、コードを組み合わせて複雑なことをしようと思っています。

If Target.Column <> 2 Then Exit Sub　は全然、思いつきませんでした。

Range形式、配列形式、関数使用のそれぞれコードを教えていただきまして、
たいへん勉強になります。（背景まで教えていただいて・・・）
関数でも作れるとは、全く頭にありませんでした。

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

回答No.4

xls88

以下のような感じでどうでしょうか。

Dim tr As Range
If Target.Column = 2 Then
　　For Each tr In Selection
　　　　If tr.Value Like "*会社" Then
　　　　　　MsgBox "取引先です。"
　　　　End If
　　Next
End If

回答No.3

imogasi

テストして、回答を見ると、＃２のご回答が出ていたが、
全コードは
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl
For Each cl In Target
If cl.Column = 2 And cl Like "*会社" Then
MsgBox cl.Row & " 取引先です。"
End If
Next
End Sub
でどうでしょう。
あるいは最初の行（左上のセル）だけ問題にして、あと無視とか
複数セルの貼り付けはエラーにするとか。
飛び飛びセルの貼り付けも上記で走るがよいのかな。
このようにChangeイベントは扱いが難しいと思う。
別には、CANCELキーなどの問題もあろう。

お礼

imogasiさん、再びこんにちは。

全コードまで載せていただいて、ありがとうございます。
たいへん助かります。
しかも、列名表示まで・・・至れり尽くせりです。

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

回答No.2

rukuku

はじめまして

ご質問の例ですと、「Target」が１つのセルになることも複数のセルになることもあると思います。
そのような場合は
For Each tCell In Target
を使って１セルごとに判断していけば解決すると思います。（"tCell"はFoe Each の中で使う変数ですので任意です）

お礼

rukukuさん、こんばんわ。

教えていただいた
For Each ～ In Targetを使えば、解決できそうです。

For Eachは、Targetにも使えるのですね。
たいへん参考になりました。
ありがとうございました。

回答No.1

mitarashi

まず、
Target Like "*会社"
という書き方が良くありません。正しくは、
Target.Value Like "*会社"
です。
さて、Targetが複数範囲の時、Target.Valueとは何でしょう？
Valueは単一セルの値を表しますので、この書き方は成立しません。
変更された一番上のセルだけ見れば良いのなら、
Target.Cells(1).Value Like "*会社"
とすればエラーにはなりません。（ご要求の機能を満足しているかは別として）

お礼

mitarashiさん、こんにちは。
早速、回答いただきましてありがとうございます。

＞正しくは、Target.Value Like "*会社"です。
基本的なところを理解してないんですね。反省です。

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