『入力されてた文字の入力規制について』（たつお） こんにちは、私は今勤務表を作成しており 分からないことがありますので質問させてください。 氏名　１２３・・・３１　備考 田中　早早遅休　　　　　　泊　　　　　　　 山田　早休日　　　　　　　遅 高橋　泊明休　　　　　　　早遅 左から・氏名・日付・備考（＝これはその行の氏名の人ができない勤務内容でありその行には入力できない為のものです） 日付のセルにはあらかじめ入力規制でリストを設定しております 【泊・明・早・遅・休】 たとえば、田中さんの２日の勤務を入力した際に、【泊】をリスト選択すると メッセージが表示され入力をキャンセルする方法はありますか？？ ＜ 使用 Excel：Excel2013、使用 OS：Windows8 ＞ ---- こんにちわ。 何がしたいのか質問の意味が分かりません？ 何故、田中さんの2日目は、泊を入力出来ないようにする必要があるんですか？ 入力できる時と、出来ない時の違いは何ですか？ そこらへんのルールを詳しく説明して下さい。 （sy） 2016/10/29(土) 14:38 ---- 備考にあらかじめ入力してある勤務内容を入力できないようにしたいのですが… （たつお） 2016/10/29(土) 14:53 ---- ＞日付のセルにはあらかじめ入力規制でリストを設定しております ＞【泊・明・早・遅・休】 入力規則を既に「リスト」として使ってしまっているのであれば、 以下の方策がありえます。 　 (1)各人ごとの入力可能なリストを定義しなおす 　 または、 (2)「リスト」ではなく、「ユーザー定義」で入力不可項目を弾けばよいでしょう。 　　備考1,備考2の列を作っておけば、共通の式にできると思います。 　（空白を無視するのチェックは、外して下さい。） 　　この難点は、（リストではないので）逐一入力しないといけないことです。 　 または、 (3)マクロで入力不可項目が入力されたかどうかをチェックすることです。 　(Changeイベントプロシージャを使います） （γ） 2016/10/29(土) 15:19 ---- ＞備考にあらかじめ入力してある勤務内容を入力できないようにしたい そう書いてもらえた方が分かりやすいですね。 ただリストで選択出来る項目から該当項目を省くんですよね。 作業列を使えば可能ですが、使えないならマクロになります。 式も複雑になるので(途中空白の項目があっても良いなら簡単な式に出来ますが)、 マクロで使える項目を文字連結して、入力規則を再セットする方が簡単そうですね。 質問者さんは、マクロは分かるんですか？ （sy） 2016/10/29(土) 15:32 ---- 条件付き書式ではだめなの？エラーは出ないけど色が付くのでわかりやすいと思う。 A B C D … AF AG AH 1 1 2 3 … 31 備考1 備考2 2 田中 早 早 遅 … 泊 3 山田 早 休 日 … 遅 4 高橋 泊 明 休 … 早 遅 B2からAF3選択 条件付き書式の数式で =COUNTIF($AG2:$AH2,B2) 書式の塗りつぶしからなるべくきつめの色を選ぶ （bi） 2016/10/29(土) 15:38 ---- なるほどですね。条件付き書式は思いつかなかったですね。 　 # 「遅」って、遅刻も許される人がいるのかと勘違いしたがwww、 # 早出、遅出なんですなあ。そりゃそうだ。 （γ） 2016/10/29(土) 15:44 ---- みなさまありがとうございます。 なるほど条件付き書式ですね。 マクロはそこそこわかります。 Changeイベントプロシージャだとどのようになりますか？ （たつお） 2016/10/29(土) 16:32 ---- Changeイベントでエラーメッセージを出す方法では無いですけど、 SelectionChangeでリストを再設定する1例です。 AG列が備考の列と仮定しています。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim buf1 As String, buf2 As String Dim s As Variant Dim i As Integer Dim r As Range For Each r In Target If Not Application.Intersect(r, Range("B2:AF10")) Is Nothing Then buf2 = "" buf1 = r.EntireRow.Columns(33).Value s = Array("泊", "明", "早", "遅", "休") For i = 0 To 4 If InStr(buf1, s(i)) = 0 Then buf2 = buf2 & "," & s(i) Next i buf2 = Mid(buf2, 2) With r.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=buf2 End With End If Next r End Sub 因みに作業列で数式処理ですと以下です。 AH2 =MID("泊明早遅休",AGGREGATE(15,6,1/(COUNTIF($AG2,"*"&MID("泊明早遅休",ROW($A$1:$A$5),1)&"*")=0)*ROW($A$1:$A$5),COLUMN(A1)),1) AL列までと下にオートフィル 入力規則の式 =OFFSET($AH2,0,0,1,5-LEN($AG2)) （sy） 2016/10/29(土) 17:24 ---- あっ！　すいません。 列選択とかしたら飛んでも無い事になりますね。。。 以下でお願いします。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim buf1 As String, buf2 As String Dim s As Variant Dim i As Integer Dim r As Range If Intersect(Target, Range("B2:AF10")) Is Nothing Then Exit Sub For Each r In Intersect(Target, Range("B2:AF10")) buf2 = "" buf1 = r.EntireRow.Columns(33).Value s = Array("泊", "明", "早", "遅", "休") For i = 0 To 4 If InStr(buf1, s(i)) = 0 Then buf2 = buf2 & "," & s(i) Next i buf2 = Mid(buf2, 2) With r.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=buf2 End With Next r End Sub （sy） 2016/10/29(土) 17:42 ---- 入力規則で入力ではなく ダブルクリックで　"泊"　-> "明"　-> "早"　-> "遅"　-> "休" -> 空欄 と 変わってゆくようにしておけば入力が簡単かと思うが そうすれば備考蘭の記載とチェックも出来飛ばすことも可能だし （？） 2016/10/29(土) 18:40 ---- 2016/10/29(土) 17:24 に syさんがお書きになったように、 各人の入力可能勤務形態を入力規則に設定するのがよいと思います。 ただし、セル選択が変わる都度実行しなくても良いかなと感じます。 月に一度（またはAG列に変更があった都度）実行すればよろしいかと思います。 ユーザーからすれば、「選択させておいてダメ出しされるのは、 精神衛生によくない」ので、上記のとおり、 選択可能なものだけを選ばせるのが、筋でしょうね。 勉強のための参考までに、Changeイベントプロシージャを使って、 入力不可の警告を出す例を書きます。 （33列目(AG列）に入力不可項目が入力されているものとしています。） Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim buf As String If Intersect(Target, Range("B2:AF10")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each r In Intersect(Target, Range("B2:AF10")) If Not IsEmpty(r.Value) Then buf = r.EntireRow.Columns(33).Value If InStr(buf, r.Value) > 0 Then Target.Select MsgBox "その勤務形態は選択できません。" r.ClearContents End If End If Next Application.EnableEvents = True End Sub syさんのコードをお借りしています。 （γ） 2016/10/29(土) 19:20 ---- みなさん いろいろた案を出して頂きとても参考なりますまた勉強にまなります 本当にありがとうございます🎵 （たつお） 2016/10/29(土) 20:58 ---- すいません 現在これでは入力したところにすでにほかの勤務形態が入力されていた場合だと それまでクリアされるのですが… すでに入力されていた勤務形態はそのまま残す場合はどうなりますか？？？ （例） 　田中さんの１日に”泊”を入力したらメッセージが表示し 　その後は”泊”を入力する前にあった”早”に戻る 　としたいです。 Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim buf As String If Intersect(Target, Range("B2:AF10")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each r In Intersect(Target, Range("B2:AF10")) If Not IsEmpty(r.Value) Then buf = r.EntireRow.Columns(33).Value If InStr(buf, r.Value) > 0 Then Target.Select MsgBox "その勤務形態は選択できません。" r.ClearContents End If End If Next Application.EnableEvents = True End Sub （たつお） 2016/10/29(土) 21:47 ---- こんなことでしょうか？ Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range Dim r As Range Dim buf As String Set myRange = Intersect(Target, Range("B2:AF10")) If myRange Is Nothing Then Exit Sub Application.EnableEvents = False For Each r In myRange If Not IsEmpty(r.Value) Then buf = r.EntireRow.Columns(33).Value If InStr(buf, r.Value) > 0 Then Target.Select MsgBox "その勤務形態は選択できません。" Application.Undo Application.EnableEvents = True Exit Sub End If End If Next Application.EnableEvents = True End Sub 複数セルを同時に、どこかからコピペイストし、 セル毎にOKとNGが混在しているときは、OKの更新もせずにUndoで元に戻ることになる。 まあ、レアケースだし保守的なほうに倒すんだからいいでしょう。 　 （γ） 2016/10/29(土) 22:19 ---- ＞（例） ＞田中さんの１日に”泊”を入力したらメッセージが表示し ＞その後は”泊”を入力する前にあった”早”に戻る どうしても分からない点を聞かせて下さい。 入力規則で選べないようにして、そのその入力自体させないようにする提案を出させて頂きましたが、 何故わざわざ入力したくない項目をリストで選べるようにして、間違えたらエラーメッセージを出すことに拘るのでしょうか？ 操作者に2度手間をさせるだけと思うのですが？ 入力規則の再設定だけでは何か不具合があるのかな？ 参考までに無駄な入力をさせてまでエラーメッセージに拘る理由をお聞かせ下さい。 （sy） 2016/10/30(日) 10:24