ダイアログボックスを表示させるマクロ

質問

こんにちは。
エクセルのマクロについて教えてください。

エクセルでＡ列に名前を入力する作業をしています。

入力作業中に、それまでに入力した名前と
重複した名前を入力しようとすると、
「重複しています」と
ダイアログボックスを表示するマクロは
どのように組めばよいのでしょうか？

わかりづらい文章ですみません。
よろしくお願いします。

　（Ａ列）
１　高橋
２　田中
３　佐藤
４　太田
５　田中　←←重複した名前を入力すると、
　　　　　　　「 重複しています」とダイアログボックスが表示される。

回答No.9

Wendy02 · ベストアンサー

こんにちは。

しばらく時間が経ってしまいすみません。
もう少し、きれいなコードに出来ると良かったのですが、思った以上に、あまり良い考えがまとまらなかったです。今年は、とても暑い!

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim c As Range
　　Dim TargetCell As Range
　　If Target.Column <> 1 And Cells(Target.Row, 1).HasFormula Then
　　　　If Target.DirectDependents.Column = 1 Then
　　　　　　Set TargetCell = Cells(Target.Row, 1)
　　　　　　If WorksheetFunction.CountIf(Columns(1), TargetCell.Value) > 1 Then
　　　　　　　　MsgBox "重複"
　　　　　　　　Application.EnableEvents = False
　　　　　　　　Target.ClearContents
　　　　　　　　Application.EnableEvents = True
　　　　　　　　Set TargetCell = Nothing
　　　　　　　　Exit Sub
　　　　　　End If
　　　　End If
　　Else
　　　　For Each c In Target
　　　　　　If Not c.HasFormula Then
　　　　　　　　If WorksheetFunction.CountIf(Range("A1", Target), c) > 1 Then
　　　　　　　　　　MsgBox "重複"
　　　　　　　　　　Application.EnableEvents = False
　　　　　　　　　　Target.ClearContents
　　　　　　　　　　Application.EnableEvents = True
　　　　　　　　End If
　　　　　　End If
　　　　Next c
　　End If
End Sub

お礼

Wendy02さん、こんばんわ。
お礼が遅くなりすみませんでした。

教えていただいたコードで思ったとおりに出来ました。
これまで悩んでいたことがすっきりしました。
コードの内容をすぐには理解できませんが、
じっくり勉強させていただきます。

Wendy02さんには、私のとりとめのない質問に
いつも親切にわかりやすく回答していただいて
感謝の言葉もありません。
ありがとうございました。

回答No.8

Wendy02

こんばんは。

>A列に「＝B＆C＆D」という式をいれると
この数式では、数式として入りません。確認しますが、数式は、以下のようなものですね。

例えば、A4 に
=B4&C4&D4

この状況では、そのような問題は発生しておりません。

Excelのバージョンは、2003以下ですか？

ループして、「重複」というメッセージが何度も出るということでしょうか？今回、数式があれば、メッセージは出ないようにしてあります。

もし、チェックするなら、
　　MsgBox "重複"
　　　　↓
　　MsgBox "重複 " & c.Address　　　　　　
または、
MsgBox "重複 " & c.Address & ", " & c.Formula

としたら、どれが問題になっているか分かります。

もう一度、シートタブを右クリックして、[コードの表示]をクリックして確認してみていただけませんか？#7のコードそのものですか？それとも、変えていますか？

もし変えているようでしたら、そのコードを教えてください。

お礼

Wendy02さん、こんばんわ。

＞今回、数式があれば、メッセージは出ないようにしてあります。
すみません。私大きな間違いをしていたようです。
私が作ろうとしていたのは、
A列に数式をあらかじめ入れておき、
その数式の答え(結果)が重複していれば、
「重複」と返すコードを組もうとしていました。

（=B4&C4&D4というような数式をA列に入れておく）
　　↓
　　Ａ列　　　　　B列　　　C列　　D列
　あいう　　　　　あ　　　　い　　　う
　かきく　　　　　か　　　　き　　　く
　あいう　　　　　あ　　　　い　　　う
　　↑
　「重複」と出る。

#7で回答していただいた中にも
「ループの中に、条件を入れてみました。数式ではないことと、セルが空でないこと。」
とあるのを、勘違いして認識していました。

＃７のコード（そのままコピーして使わせていただいています）ですと、
数式の重複はカウントしませんが、数式の答えが重複しても、「重複」と表示しません。
（ここを悩んでいました）

私の説明不足、表現不足を痛感しております。
ご迷惑をおかけしてほんとうに申し訳ありません。

＞この数式では、数式として入りません。確認しますが、数式は、以下のようなものですね。例えば、A4 に　=B4&C4&D4。
すみません、これも書き損じです。「=B4&C4&D4」との意味です。

＞Excelのバージョンは、2003以下ですか？
Excelのバージョンは、2003です。

長く付き合わせてしまう結果になり、ほんとうに申し訳ありません。
また、毎回、回答を寄せていただき、ほんとうにありがとうございます。

回答No.7

Wendy02

こんばんは。

>ダイアログボックスが表示され続けてしまいます。
>これを解消しようと試みいたのですが、うまくいきませんでした。

それは気が付きませんでした。本当ですね。

>これは、For Each ～ In 範囲 あたりのコードが関係してくるのでしょうか？

だとは思うのですが、無限ループが発生していますね。何がどうなのか、私も、さっぱり見当が付かなくなりそうな気がします。

ループの中に、条件を入れてみました。

数式ではないことと、セルが空でないこと。

If Not c.HasFormula And c.Value <> "" Then

イベントを一旦解除する、という命令を入れてみました。
（どこでイベントが発生しているかは、不明ですが、無限ループは解除されます。）
Application.EnableEvents = False

しかし、まだ問題点があるのは、数式の値の重複は許しています。そのためには、私が考えるのは、特殊な方法で、Target の範囲（A列）を広げないで作りますが、先々の問題は、まず、これで、当面の問題が解決するか様子をみてください。見た目は簡単ですが、なかなか、ここまで来ると、パズルのように難しいです。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
　　If Target.Column = 1 Then　　'A列
　　Application.EnableEvents = False
　　For Each c In Target
　　　　If Not c.HasFormula And c.Value <> "" Then
　　　　If WorksheetFunction.CountIf(Range("A1", Target), c) > 1 Then
　　　　　　MsgBox "重複"
　　　　　　c.ClearContents
　　　　End If
　　　　End If
　　Next c
　　End If
　　Application.EnableEvents = True
End Sub

お礼

Wendy02さん、こんばんわ。
お礼が遅くなってすみませんでした。
この２日間、いろいろと試行錯誤していました。

セル（A列）に関数を入れていると、思うようにうまくいきません。
（たとえば、A列に「＝B＆C＆D」という式をいれると
　無限ループはでないものの、数回ループしてしまいます。）
とっくに私の限界を超えているのですが、
原因が分からないところが、頭を悩ませます。

細かな質問に何度も何度も
丁寧な回答を寄せていただきありがとうございました。
初心者にもわかりやすく、たいへん感謝しています。
ありがとうございました。

回答No.6

WWolf

＃１です。遅くなりすいません。
早速ですが、参考に記述したコードはシートのコードですので、他のシートからとってくる場合は
If inp = Cells(i, 1).Value Then
↓
If inp = worksheets("飛ばす元のシート名").Cells(i, 1).Value Then
で解決すると思いますが、他のブックではブック名も必要です。
参考にして頑張って下さい。

お礼

WWolfさん、こんばんわ。
追加の質問にもお答えくださってありがとうございます。
教えていただいたコードを参考にマクロを組み直してみます。

回答No.5

Wendy02

こんばんは。

>　マクロの本を読んでもよくわかりませんでした。

そうですね。今回のような内容は、VBAの本には載っていませんね。

マクロの本は、入門用か最高度かで、中間値というか、実践的な内容が書かれている本が少ないようです。（最高度のものは、かなり減ってきているようですし、VB6の本も手に入らないので、今から、現在のVBAを覚えようとする人は、本当に大変です。この5年先をみて、VB.Net 型に移行を考えてもよいと思います。VB.Net は、現在、無料で手に入るはずです。VBAは、VB.Net スタイルになるといわれています。しかし、現在のVBAが、その時になくなることはないと思います。）

今回の内容は、だいたい、1年間ぐらいVBAの掲示板を、はしごして読んでいると知識として入ります。

本題に入ります。

最初から説明しないといけませんよね。
まず、Worksheet_Change というのは、セルを開けてEnter するときが引き金になります。（VBAを勉強すると、この内容が、後々疑問に感ずることが来るかもしれません。）

次に、

Private Sub Worksheet_Change(ByVal Target As Range)
　　　　　　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　このTarget というのは、Range オブジェクトです。

Rangeオブジェクトというのは、Cells オブジェクトと違って、1個でも2個でも、列でも、行の範囲も入ります。それを、処理する方法は、いくつかありますが、For Each ～ In 範囲 で、処理するのが一番簡単だと思います。

エラーで、
「型が一致しません」

というのは、

(1)
inp = Target.Value
'ここでは、Target.Value が、inpに複数の値でも入りますが、

If inp = Cells(i, 1).Value Then　　
'Cells(i,1) と一対一にならないことがあるのです。
'Target.Value には、複数の値が入っているからです。

(2)
Application.WorksheetFunction.CountIf(Range("A1", Target), Target)

こちらの場合は、第二引数（後ろ）の Target は、値が、複数が入らないようになっているのです。こういう関数の仕様は、公になっていないので、やってみなければ分からないことが多いです。ただ、今回は、第二引数は、1つの値しか入らないと思ってよいです。そうすると、二つ以上入れたら、エラーが返ってしまいます。

そこで、ひとつずつ、取る手段として、For Each ～ In 範囲 を使い、一つずつ、関数に入れてあげます。

まだまだ、ここの周辺には、難しい部分や公開されていない話が残っていますから、折に触れて質問してみると良いかと思います。

お礼

Wendy02さん、こんばんわ。

たいへんわかりやすく詳しい解説をしていただいて
ありがとうございます。
（やっぱり、初心者には難しいですね。）

自分の限界を超えていることは知りつつも、もうひとつだけお聞かせください。
コピーペーストの場合は、教えていただいたコードが機能したのですが、
コピーペーストではなくて、
関数のイコールで違うセルからデータを飛ばしてくるようにすると
（A列のセルにあらかじめ「＝B◯」と入れておいたりすると）、
ダイアログボックスが表示され続けてしまいます。
これを解消しようと試みいたのですが、うまくいきませんでした。
これは、For Each ～ In 範囲 あたりのコードが
関係してくるのでしょうか？
質問ばかりですみません。

回答No.4

Wendy02

こんにちは。

#2 のimogasiさんのコードをお借りして、このように換えればよいです。
ワークシート関数で使うのがベストですね。VBAのコードの処理するよりも、スピードが、10倍以上速いです。中でも、CountIf は、関数自体に、エラーを吐き出す可能性が低いので、これがよいと思います。

一般的には、入力規則の範疇ですが、コピー&ペーストでは、その範囲に対して入力規則が働きませんね。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
　　If Target.Column = 1 Then
　　For Each c In Target
　　　　If WorksheetFunction.CountIf(Range("A1", Target), c) > 1 Then
　　　　　　MsgBox "重複"
　　　　　　Target.ClearContents
　　　　End If
　　Next c
　　End If
End Sub

お礼

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

＞コピー&ペーストでは、その範囲に対して入力規則が働きませんね。
この点で悩んでいましたが、
教えていただいたマクロで、すっきり解決しました。

もしよろしければ、なぜこのマクロでは、
コピー&ペーストしたセルに対しても、
メッセージボックスが立ち上がってくるのか
教えていただければと思います。
（情けない話ですが、
　マクロの本を読んでもよくわかりませんでした。）
わがままな内容ですいません。

回答No.3

pacific231

入力規則を使うのが一番シンプルです。

A1セルを選択し、データ - 入力規則で設定タブ、入力値の種類をユーザー設定にし、数式に
=COUNTIF(A:A,A1)=1
を入れます。
意味はA列にA1の値が1つだけなら入力を許可するということです。

また、メッセージボックスの内容を変更するなら、エラーメッセージタブのエラーメッセージに 重複しています など入れればＯＫです。

入れ終わったらA1をコピーし、A2から下に貼り付ければ完了です。
A列に罫線などあるのなら、「入力規則」の形式を指定して貼り付ければ大丈夫です。

お礼

pacific231さん、こんばんわ。

入力規則でも出来るんですね。
入力規則はリストくらいしか使ったことがなかったので、
勉強になりました。

回答していただきありがとうございました。

回答No.2

imogasi

下記の方法が考えられます。
（１）IF文で全行繰り返し聞く（判別）
（２）Findメソッド利用法
（３）Countif関数利用法
（４）入力規則でもできるかも
（３）を掲げます
極く行数が少なくて済むのが特徴。
今打ち込んだ名前の行にカーソル復帰は省略。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Application.WorksheetFunction.CountIf(Range("a1", Target), Target) > 1 Then
MsgBox "重複"
　　Target = ""
End If
End If
End Sub

お礼

imogasiさん、こんばんわ。

回答していただきありがとうございました。
Countif関数を使うのは、目から鱗でした。

わがままな質問をさせてください。
別のシートからA列へ名前をとばしてくるマクロで、A列に名前（文字列）を入れると、

If Application.WorksheetFunction.CountIf(Range("a1", Target), Target) > 1 Then

のところで「型が一致しません」と
デバックがおきます。

これを解消する方法はあるのでしょうか？
当初の質問に付加した内容を質問してすみません。

回答No.1

WWolf

こんばんは
下記のサンプルは宣言は省略しています。
下記のサンプルの場合後から既存のデータを修正した場合など
考慮しておりません。
参考程度にお使い下さい。
Private Sub Worksheet_Change(ByVal Target As Range)
LR = Range("A65536").End(xlUp).Row
If LR <> 1 Then
inp = Target.Value
For i = 1 To LR - 1
If inp = Cells(i, 1).Value Then
MsgBox i & "行目と重複しています"
Target.Select
End If
Next
End If
End Sub

頑張って下さい。

お礼

WWolfさん、こんばんわ。

回答していただきありがとうございました。
◯行目まで表示させるのは、たいへん役に立ちます。

この場をお借りして、欲張りな質問をさせてください。
別のシートからA列へ名前をとばしてくるマクロで、A列に名前（文字列）を入れると、

If inp = Cells(i, 1).Value Then

のところで「型が一致しません」と
デバックがおきます。
（マクロ初心者なので、・・。）

これを解消する方法はあるのでしょうか？
当初の質問に付加した内容を質問してすみません。