数字「0」の入力を文字数としてカウント

質問

入力した文字数が7桁でないとエラーメッセージを表示する処理（下記）を作成しました。
入力した文字の先頭が「0」の場合、または「00」の場合に、「0」「00」を含む文字数をカウントし、
エラーメッセージを表示させ、更に表示書式を”000-0000”（郵便番号）としたい。
ご教示をお願い致します。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wCellVal As String

With Worksheets("Sheet1")
wCellVal = .Cells(Target.Row, Target.Column).Value End With

If Target.Address(False, False) = "A1" Then

If Len(wCellVal) <> 7 Then '文字数チェック
MsgBox "7桁で入力してください。", vbOKOnly + vbExclamation, "入力エラー"

Exit Sub
End If
End If

End Sub

よろしくお願い致します。

回答No.3

keithin · ベストアンサー

アタマゼロのチェックとハイフン表示を両立する方法は無いと、お話ししてあります。
またご相談の事をやるには文字列書式にするしかないとも、お話ししました。

＞数字「0」の入力を文字数としてカウント

標準、数値、郵便番号の書式設定のセルでは、エクセルは「アタマにゼロを添えて入力された」と知る事が出来ません。なので文字列書式にします。

準備：
Ａ列の書式設定を文字列にしておく

シートモジュールに記載する：
private sub Worksheet_Change(byval Target as excel.range)
　dim h as range
　dim buf as string
　on error resume next

　for each h in application.intersect(target, range("A:A"))
　　if h <> "" then
　　　h.numberformat = "@"
　　　buf = replace(h.value, "-", "")
　　　if len(buf) <> 7 then
　　　　msgbox "N/A" & vblf & h.value
　　　　application.enableevents = false
　　　　h.clearcontents
　　　　application.enableevents = true
　　　else
　　　　application.enableevents = false
　　　　h = format(buf, "000-0000")
　　　　application.enableevents = true
　　　end if
　　end if
　next
end sub

その他機能については、随意に自力で追加してください。

お礼

ご指摘いただいていたにも拘らず、理論的に考える能力が足りず、申し訳ありませんでした。

お手数をお掛けし、感謝し尽くせません。
本当にありがとうございました。

回答No.2

chie65535

＞入力した文字の先頭が「0」の場合、または「00」の場合に、「0」「00」を含む文字数をカウントし、エラーメッセージを表示

郵便番号には「001-0010」と言うのが存在しますよ。

http://www.post.japanpost.jp/cgi-zip/zipcode.php?pref=1&city=1011020&id=101

セルに「数値で入力している場合」には「0010010」と打ち込むと「10010」になっちゃいます。

なので「７桁かどうか」では正しく判定できません。

北海道は、頭の３桁が001～099になっているので、それを考慮して下さい。７桁縛りにしてはいけません。

＞更に表示書式を”000-0000”（郵便番号）としたい。

「セルの書式」の表示形式の「その他」の所に「郵便番号」がありますよ。

最も簡単なエラー判定は「入力した数値が10000～9999999の範囲にあるかどうか？」です（もしセルに数値で入力するなら）

OKの範囲が「0～9999999」じゃない理由は「頭の３桁の範囲が001～999」だからです。

因みに、後ろの４桁は「0000から9999まで、何でもあり」なので、チェックしてはいけません（「上記に記載がない場合」の郵便番号は、後ろ４桁が「0000」になります。また、事業所の個別郵便番号は、後ろ４桁が「9010」など大きい数字になります）

お礼

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

回答No.1

kybo

wCellVal = .Cells(Target.Row, Target.Column).Value

を

wCellVal = .Cells(Target.Row, Target.Column).Text

に

If Len(wCellVal) <> 7 Then

を

If Len(wCellVal) <> 8 Then

に変更すればよろしいかと。

Textは表示形式を適用して取得。Valueはセルの数値のみ取得。
なので、Textの場合、ハイフンも取得するので、8桁あるかで判断。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wCellVal As String

With Worksheets("Sheet1")
wCellVal = .Cells(Target.Row, Target.Column).Text
End With
If Target.Address(False, False) = "A1" Then

If Len(wCellVal) <> 8 Then '文字数チェック
MsgBox "7桁で入力してください。", vbOKOnly + vbExclamation, "入力エラー"

Exit Sub
End If
End If

End Sub

補足

早速、ありがとうございます。
”01234567”と入力した時、目論見では、文字数オーバーなので、エラー表示して欲しいのですが、先頭の「0」を除いて処理されて、”123-4567　”と表示され、エラーも出ません。
言葉足らずで申し訳ありませんでした。
”01234567”の「0」をカウントして、入力エラーにしたいのですが、何か方策ありますでしょうか？
お手数をお掛け致しますが、よろしくお願い致します。