Excelシート内でのチェック機能構築についてお教えください

Question

お世話になっております。
現在、VBAを利用し始めてひと月足らずの者です。
上司から依頼を受けて新しいVBAの作成に当たっているのですが、
苦戦しており、ご質問させていただきます。

全社員が業務で使うExcelシートについて、各人の入力ミスが多いため
手入力する場所が間違っていたらエラー表記が出るようなマクロを作成してほしいとの依頼です。

具体的には、
①　所属部署、社員番号の入力ミスがあったらエラーになる
②　ある項目の割合を100％にする箇所があるのですが、
（例えば50％、30％、20％という形、こちらも手入力）
そちらが100％になっていなければエラーになる
③　とあるセル（例えばB４セル）に入力があった場合、
別のセル（例えばC４セル）に入力がなければエラーになる

といったマクロにしてほしいとのことです。

これまでにExcelシートの切り分けなどをしたことはあるのですが、
上の三つについては構想が思いつかず、調べてもなかなかほしい情報にヒットしません…

大変お手数ですが、もし解法あるいはヒントがありましたら、お教えいただけますと幸いです。

自分の書き方では十全な理解をしていただくこともままならないかもしれませんが、
何卒、よろしくお願いいたします。

Comments

[m.ts10806]

「初心者アイコン」を質問につけられますし質問タグもあるので
【VBA初心者です】や冒頭の挨拶はなくても良いかと思います。
見る方としては要件だけを淡々と書いてもらったほうがありがたいのです

[Y.H.]

2，3は条件付き書式、1は部署数・社員数にもよるけどデータの入力規則で要件は達成できそう

[jinoji]

①所属部署の一覧を別シートに用意し、入力欄の「条件付き書式」で、その一覧に一致するものがない場合はセル色を変える
または、所属部署の一覧を別シートに用意し、入力欄の「データの入力規則」でその範囲を指定する
②たとえばE1,E2,E3セルの合計が100%になる必要があるとしたら、
E1~E3の「条件付き書式」で、「=SUM($E$1:$E$3)<>1」の条件の時にはセル色を変える
③C4セルの「条件付き書式」で「=AND(NOT(ISBLANK($B$4)),ISBLANK($C$4))」の条件の時にはセル色を変える

[masajin]

m.ts10806様
ご指摘ありがとうございます。修正させていただきました。

[masajin]

Y.H.様、,jinoji様
コメントありがとうございます。
やはりマクロにこだわらずにExcel機能を活用すべきですかね…ありがとうございます！

[jinoji]

VBAを使ってもできますし、使わない方法もあります。
どの方法を使うのが適切なのかは、実際の利用シーンによって変わってくると思います。
たとえば、そのExcelシートを使う各社員が入力時/入力後にチェックするのか、
そのシートを回収して集計する業務の人が、集計前にチェックするのか、といった具合です。

全社員にExcelシートを配るとしたら、マクロありのファイルで配るのはやめた方が無難かもしれません。
「マクロを無効にする」の設定で開く人がいると、チェックが行われないからです。

一方、もしも今回の上司さんの指示の意図が「あなたがVBAに習熟できるよう、ひとつ業務っぽい課題を与えてみた」というものであれば、私が上に書いた「条件付き書式」などの方法では意味がないかもしれません。

[masajin]

jinoji様（先ほどお名前を間違ておりました、訂正し、お詫び申し上げます。
コメントありがとうございます。
なるほど、マクロを無効にする人も出てくる可能性がありますね…上司からは教育目的ではなくあくまで業務改善を喫緊の課題としている旨が伝わってきますので、やはり関数で解決するほうが総合的にはベターな解決策かもしれません。
ありがとうございます。

Answer 1

単純にチェックするということであれば、
２，３に関しては、ＶＢＡを使わずとも、関数だけでも実現できるかと思います。
１に関しては、何をもってエラーとするかが見えないので何とも言えないです。
部署や社員番号をどこにある何と比べてどう違ったらエラーとするかでやり方も変わります。
あとは、チェックが走るタイミングによっても違うかと思います。
まずは、本当にＶＢＡを使うことが必要なのかから考えたほうがよいでしょう。

Comments

[masajin]

yo_u様
ご回答、ありがとうございます。
私の頭が固く、上司からマクロという指定をそのまま受け取っておりました。
おっしゃる通り、関数、あるいは入力規則にも目を向けて解法を探したいと思います。
ありがとうございます。

Answer 2

値のチェックをする時には、Worksheet_Changeを良く使います。
下記のサンプルは、A1orA2セルへ数字のみ入力許可する物です。
こちらを参考にして作成されたら如何でしょうか。
コピペして動かすと感じが分かると思います。

’Test_Sample_Miniature
Private Sub Worksheet_Change(ByVal Target As Range)
    '
    '(確認セル特定)
    If 1 <= Target.Row And Target.Row <= 2 And _
       1 <= Target.Column And Target.Column <= 1 Then
        '
        '(値なしSkip)
        If Trim(Cells(Target.Row, Target.Column)) = "" Then
            Exit Sub
        End If
        '
        '(値確認)
        If IsNumeric(Cells(Target.Row, Target.Column)) = False Then
            MsgBox "このセルには数字を入れて下さい。"
            Cells(Target.Row, Target.Column) = Null
        End If
        '
    End If
    '
End Sub

Comments

[masajin]

tosi様
ご回答、ありがとうございます。
If構文でTargetを指定し、その範囲で条件付けをするイメージでしょうか。
最終的なチェック時に参考にさせていただきます。
ありがとうございました。

Answer 3

①　所属部署、社員番号の入力ミスがあったらエラーになる
別のsheetに所属部署や社員番号を控えておいてそれでif文検索でいいです。
セルの値が変更されたタイミングで反応させる場合はイベントを使用します。
反応系イベントはブックを開いた時や閉じた時などもあるので少し調べてみると良いでしょう。
ただ、イベント反応系はデバッグに苦労しますし正常に動いているのかも確認しづらいので必要最小限の方がいいです。

'subとして単体で使用
'ワークシートイベントなのでどのセルを指定しても一度はこのイベントが反応する。
Private Sub Worksheet_Change(ByVal Target As Range)
	'Cells(1,1)から1列目の入力がある末尾までの範囲をif文対応
    If Intersect(Target, Range(Cells(1,1),Cells(Cells(Rows.Count, 1).End(xlUp).Row,1))) Is Nothing Then
		'範囲外ならイベント終了
        Exit Sub
    Else
		'範囲内ならメッセージボックス表示
		'処理を行いたいならここでif文
        MsgBox "セルの値が変更されました"
    End If
End Sub

参考

また手入力にしなければ誤入力エラーも起きません。
所属部署や社員番号は頻繁に入れ替わったり追加することもありませんよね。
コンボボックスを埋め込んでおいて～みたいな方法もあります。
コンボボックスを先に仕込む方法とか考えないといけないので出来ればですが。
コンボボックス

②　ある項目の割合を100％にする箇所があるのですが、（例えば50％、30％、20％という形、こちらも手入力）そちらが100％になっていなければエラーになる。
単純にCellsで比較すればよいと思います。
msgboxでエラー表示させてもいいんですが入力では煩わしそうです。
Interior.Colorで背景色を変更してもいいかもしれません。

Sub main()
    Cells(1, 3) = Cells(1, 1) + Cells(1, 2)
    If Cells(1, 3) <> 100 Then
        '色の指定方法は.Interior.Colorで検索
        Cells(1, 3).Interior.Color = vbRed
    Else
        '合計100なら塗りつぶしなしにする
        Cells(1, 3).Interior.Color = xlNone
    End If
End Sub

③　とあるセル（例えばB４セル）に入力があった場合、
①と同様です。
空白は""で確認出来ると思います。

多分これである程度作れそうですが、実際の業務で使うマクロはここからのエラー対応が非常に時間がかかるので頑張ってください。
基本的にプログラムの都合ではなく入力者の都合に合わせて組まないといけないからです。

Comments

[masajin]

msuguru様
ご回答、ありがとうございます。
＞ただ、イベント反応系はデバッグに苦労します…
そうなのですね、腕に自信もありませんので、今回は控えようかと思います笑
①に関してはコンボボックスを、②に関してはすみません私の腕不足からか
書いていただいたマクロが上手く作動してくれませんので、別の方法を考えたいと思います…
ありがとうございました。

[退会済みユーザー]

ごめんなさい。
②はC#の文法になってました
分岐も追加して修正していますので見て頂けたら幸いです。

投稿者の解法は正しいと思います。
最近エクセルの機能を使わずプログラムで解決しようとしてましたが、関数で解決出来る部分はそうすべきですね。

ご存じかもしれませんがマクロやVBAはオートシェイプの図形をボタンとして使用出来る機能もあります。
入力後にcheck機能を作ったときは【最終確認】みたいなボタンを作っておくと運用でも安心です。
https://office-hack.com/excel/macro-button/

[masajin]

msuguru様
わざわざ修正までしていただき、大変ありがとうございます！
こちらのコードで動作の確認もできましたので、お教えいただいた通り
図形ボタンを用いてチェック機能に組み込もうと思います。
ありがとうございます。

Answer 4

添付図のような具体的なレイアウトを提示して質問したほうが、回答が付きやすくなります。
＞①　所属部署、社員番号の入力ミスがあったらエラーになる
具体的に、所属部署、社員番号の入力ミスをどのように判定するのかが問題です。
例　営業一課、営業二課、総務課、以外はエラーにする等
社員番号の入力ミスは、別途社員一覧の名簿があるならそれになければエラーにする等

＞②　ある項目の割合を100％にする箇所があるのですが、
＞（例えば50％、30％、20％という形、こちらも手入力）
＞そちらが100％になっていなければエラーになる
これは、具体的なセルを提示し、合計が１００（もしくは%表示なので合計は1）でなければエラー

＞③　とあるセル（例えばB４セル）に入力があった場合、
＞別のセル（例えばC４セル）に入力がなければエラーになる
Ｂ４セルが空白以外で、Ｃ４が空白ならエラーにする。

エラーの表示をどうするかも決めた方が良いです。
１．エラーのセルを赤色表示
２．メッセージボックスに表示
３．エラーを表示する列を作り、「ＸＸＸがおかしい」のようなエラーをそのセルに表示
等が考えられます。

又、チェックをするタイミングですが、
1案　全て入力した後で、一括チェックする。
2案　入力の都度、チェックする。
がありますが、１案の方が簡単かと思います。

Comments

[masajin]

tatsu99様
ご回答、ありがとうございます。
マクロと関数を併せて用いることになりそうですが、
入力後に一括チェックする機能を設けて確度を高めたいと思います。MsgBoxがベターかなと思っております。
ありがとうございました。