Excel2007のデータ入力規則について

質問

文字数で管理しようとしたのですが、0が先頭についてしまうと上手くいきません。

ABC-1234-5　であれば、エラーはしません。
しかし、
ABC-0001-2　のように入力する数値の先頭に0が入るとエラーとなります。
000012345と入力するとABC-1234-5と表示されるので、
どうも、0を無視されているようです。先頭に0がついても文字数としてカウントしてくれる方法はありますか？

なお、入力の手間を省きたいので、5つの連続した数字を入力するだけにするべく、「ABC」と「-」はセルの書式設定の
ユーザー定義で　"ABC"-0000-0　としています。

データの入力規則の条件設定は以下の通りです。
入力値の種類：文字列(長さ指定)
データ：次の値に等しい
長さ：5

良い解決方法があればご教授の程、よろしくお願い致します。

回答No.7

tom04 · ベストアンサー

たびたびごめんなさい。

前回のコードではA1セル（結合セル）をDeleteで空白にした場合はエラーになりますので、
↓のコードに変更してください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim str As String
Range("A1").NumberFormatLocal = "@"
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Range("A1") = "" Then Exit Sub
Application.EnableEvents = False
If IsNumeric(Target) And Len(Target) = 5 Then
str = Target
Target = "ABC-" & Left(str, 4) & "-" & Right(str, 1)
Else
MsgBox "入力値が不正です"
Target = ""
Target.Select
End If
Application.EnableEvents = True
End Sub 'この行まで

尚、
＞因みに、列全てに適応させるようにするコマンドは『:A』で合ってますか？
に関しては

A列すべてであれば
＞Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
のように、
A～C列すべてであれば
＞Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
列が飛び飛び（A・C・E列であれば）
＞Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then Exit Sub
のようにカンマで区切って範囲を指定します。

何度も失礼しました。m（_ _）m

お礼

こちらこそ何度もお手数をお掛けしましてありがとうございました。

回答No.6

tom04

No.3・5です。

＞A1とB1とC1のセルを結合しているセル一つに対して、命令を実行させようとした場合で・・・

の件に関して、
A1～C1セルを結合した場合はそのセル番地はA1となります。

前回のコードの2行
＞Range("A:A").NumberFormatLocal = "@"
＞If Intersect(Target, Range("A:A")) Is Nothing Or Selection.Count <> 1 Then Exit Sub

を
＞Range("A1").NumberFormatLocal = "@"
＞If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

に変更してみてください。
これでA1～C1セルが結合されている場合はそのセルだけが変更の場合マクロが実行されます。

※　一度エラーになってしまうと（仮にA列すべてを削除する等）
ちゃんとマクロが実行されなくなりますので、
前回のコードを一旦すべて削除して
新たにコードをコピー＆ペーストし訂正してみだくさい。m（_ _）m

お礼

ありがとうございます。

回答No.5

tom04

No.3です！
前回は質問内容を取り違えていたようです。

結局入力する文字数は0を含めて必ず5文字（頭から0があってもそれを文字数とする）
それ以外は入力できないようにしたい！というコトですよね？

VBAになってしまいますが、一例です。
仮にA列に入力するとします。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてA列にデータを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim str As String
Range("A:A").NumberFormatLocal = "@"
If Intersect(Target, Range("A:A")) Is Nothing Or Selection.Count <> 1 Then Exit Sub
Application.EnableEvents = False
If IsNumeric(Target) And Len(Target) = 5 Then
str = Target
Target = "ABC-" & Left(str, 4) & "-" & Right(str, 1)
Else
MsgBox "入力値が不正です"
Target = ""
Target.Select
End If
Application.EnableEvents = True
End Sub 'この行まで

※　セルの表示形式・入力規則等の設定は一切不要です。

お役に立ちますかね？m（_ _）m

お礼

ありがとうございます。意図する通りに表示されました！！

そこで、一つ分らない事が見つかったのですが、教えて頂けますか？
入力例ではA列すべてに適応されていますが、複数のセルを結合させた場合はどのようにRange(　)の中に入力すれば良いでしょうか？
たとえば、A1とB1とC1のセルを結合しているセル一つに対して、命令を実行させようとした場合でお願いします。

因みに、列全てに適応させるようにするコマンドは『:A』で合ってますか？

回答No.4

chonami

ご自分が扱っているデータが数値（数字ではなく）であることを認識されてますか？

数値ですと、前にどんなに0を入れてもそれは無視されます。
現実に「000123」という数は存在しませんね。それは「123」の事です。
ユーザー定義で"ABC"-0000-0と設定して、「00001」と入れてABC-0000-1などと表示されるのは、
前の0が認識されているからではありません。
1と普通に入れても同じ結果になります。
この場合入力文字数は1です。

単純に5つの数字を扱いたいのでしたら、データは文字列とする必要があります。
ですが文字列ですと、表示形式で任意の場所で区切ることはできません。

入力セルと表示セルを分けて関数で数字を抜き出して組み合わせる感じになるのでは？
それなら、入力セルに５文字の制限をかけておけば、ご希望の事が可能になると思います。

お礼

なるほど、数字と数値は違うんですね、勉強になりました。ありがとうございました。

回答No.3

tom04

こんにちは！
仮にA列に入力するとします。

A列すべてを範囲指定　→　データ　→　入力規則　→　「ユーザー設定」　→　数式欄に
=LEN(A1)<=5
としてみてはどうでしょうか？

必ず5文字であれば
=LEN(A1)=5
のようにしてみます
ただし、この場合4文字以下の場合は入力できません。
（ABC-0012-3　のような表示が出来なくなります）

※　数式内のA1部分は範囲指定した一番最初（複数列の場合は一番左上）のセル番地にしておきます。

参考になりますかね？m（_ _）m

お礼

VBAの方法を教えて頂き、本当にありがとうございました。上記の方法も今後使える方法だと感じます。勉強になりました。

回答No.2

aokii

データの入力規則の条件設定は整数の0～99999までにし、
セルの書式設定をユーザー定義はそのままにしてみて下さい。
"ABC"-0000-0

お礼

今回の意図とする回答ではありませんでしたが、勉強になりました。ありがとうございました。

補足

早速の回答ありがとうございます。
しかしながら、No.1の方と同様の結果でした。
No.1の補足に追記しました通り、文字数での制限を掛けたいのです。

回答No.1

MackyNo1

現在の書式で入力規則を設定したいなら入力値の種類を「整数」にして「次の値以下」にして「99999」を最大値に設定してください。

お礼

今回の意図とする回答ではありませんでしたが、勉強になりました。ありがとうございました。

補足

早速の回答ありがとうございます。
確かに、見かけ上は0を先頭に来ても表示されるようになりました。
しかしながら、上記の方法ですと、文字数による制限を掛ける事が出来ず、また、結局のところ入力した0が無視されてしまっています。ex)00005と5つ入力しても、5と1つ入力するのと同じになっていました。入力する文字数は決まっているので、X>でもX<でもダメなのですX=であって欲しいのです。