エクセルのユーザー定義で

質問

エクセルのユーザー定義で、打ち込む数値の途中にアルファベットが含まれる場合アルファベットはそのまま表示させたいのですが、無理なのでしょうか？
例えば、打ち込む数値が「1234561」のとき表示は「123456－1」
「12345B1」のときは「12345B-1」というふうには出来ないのでしょうか？

回答No.5

keithin · ベストアンサー

操作例：
Ａ列に0000"A"0-0のユーザー定義の表示形式を取り付けておく
シート名タブを右クリックしてコードの表示を選ぶ
現れたシートの内容を全て消去し、改めて下記をコピー貼り付ける

private sub worksheet_change(byval Target as excel.range)
　dim h as range
　on error resume next
　for each h in application.intersect(target, range("A:A"))　’実際に合わせて修正
　　if h <> "" then
　　h.numberformat = "0000A0-0"
　　if application.istext(h) then
　　application.enableevents = false
　　h = application.replace(h, len(h), 0, "-")
　　h = application.replace(h, len(h)-2, 0, "A")
　　application.enableevents = true
　　end if
　　end if
　next
end sub

ファイルメニューから終了してエクセルに戻る
Ａ列に123451や1234A1とデータを記入する。

手でやりますとお話ししたことを、代わりに機械がやってくれます。

お礼

ありがとうございます！
出来ました。
勉強してスキルを高めたいと思います。
ありがとうございました。

回答No.6

tom04

No.4です。

D列で操作したい！というコトなので、
前回のコードでOKであるという前提です。

Columns(1)を
Columns(4)　または　Range("D:D")
に変更してみてください。

尚、前回のコードの場合、頭が「0」から始まる場合はいくら7文字を入力しても
「入力値が不正」だとメッセージボックスがでますので、
D列の表示形式を「文字列」にして操作してみてください。

何とかご希望通りになれば良いのですが　m（_ _）m

お礼

ご回答ありがとうございました。
参考にさせていただきます。

回答No.4

tom04

こんにちは！
横からお邪魔します。

No.3さんの補足を読ませていただくと、最初の質問内容と状況が違ってきていますが・・・

＞実際はもう少し複雑で「1234511」で「12345A1-1」となるユーザー定義
＞「00000"A"0"-"0」を使っていて
＞「12345B1」で「12345AB-1」にしたいのですが

とありますので、一例です。
セルの表示形式で操作するのではなく、文字列データとしてみてはどうでしょうか？
A列でマクロが実行されるようにしていますので、A列のセルの表示形式は「標準」にしておいてください。

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

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

こんな感じではどうでしょうか？m（_ _）m

補足

ありがとうございます。
まだ実際に試してはいないのですが、
A列ではなくD列で使用するにはColumns(1)の数値を変えればいいのでしょうか？
素人で申し訳ないです。

回答No.3

keithin

一言目に回答した通り、まずユーザー定義じゃ出来ません。信じる信じないはご自由に。

＞以外の方法

具体的にどうしたら出来るのかは、既に回答済みです。他に方法はありません。

＃参考
Ｂのセルに「0000"B-"0」とユーザー定義を設定し12341と記入させ、
Ａのセルに「0000"A-"0」と異なるユーザー定義を設定し12342を記入させる、
そんなウマシカ的方法でも「出来ます」

＞無理なのでしょうか

スキルさえあれば、出来ます。

操作例：
Ａ列に00000-0のユーザー定義の書式設定を取り付けておく
シート名タブを右クリックしてコードの表示を選ぶ
現れたシートに下記をコピー貼り付ける

private sub worksheet_change(byval Target as excel.range)
　dim h as range
　on error resume next
　for each h in application.intersect(target, range("A:A"))　’実際に合わせて修正
　　if h <> "" then
　　h.numberformat = "00000-0"
　　if application.istext(h) then
　　application.enableevents = false
　　h = application.replace(h, len(h), 0, "-")
　　application.enableevents = true
　　end if
　　end if
　next
end sub

ファイルメニューから終了してエクセルに戻る
Ａ列に123451や1234A1とデータを記入する。

手でやりますとお話ししたことを、代わりに機械がやってくれます。

補足

わぁ、ありがとうございます！
実際はもう少し複雑で「1234511」で「12345A1-1」となるユーザー定義
「00000"A"0"-"0」を使っていて
「12345B1」で「12345AB-1」にしたいのですが、
先のコードでどこを修正したらよろしいでしょうか？
スキルがないので、何度もすみません。

回答No.2

Mikenuko

こんにちわ。

状況がよく分かっていないので的外れな回答になっているかもしれません。

入力された内容を文字列として認識するには
「@」
を使用します。

現在、手元に確認できる環境がないため憶測での回答になってしまい申し訳ないのですが
「ユーザー定義」->「種類」に

「@@@"-1"」

のような形式で可能ではないでしょうか。

意味合い的には文字列の末尾に「-1」を付加している状態です。ダブルクォーテーションで囲まれた文字は、文字列として認識できるようになります。

お礼

ご回答ありがとうございます。
「@@@"-1"」では「1234B11234B11234B1-1」となります。
「1234B1」で「1234B-1」にしたり「1234A2」で「1234A-2」したりもしたいのでだめです。
どうやらできないっぽいです。ありがとうございました。

回答No.1

keithin

数字だけ（数値）なら問題なくできることですね。
でも文字を含んだデータでは、そういう事は全くできません。

Ｂ込みのデータを記入する際に、手で-まで書き足して記入するしか、出来ることはありません。

補足

ご回答ありがとうございます。
ユーザー定義以外の方法でも無理なのでしょうか？