エクセルで特定の数字を入力すると特定の値に自動変換する方法

質問

エクセルのマクロを使用して簡単に入力できるようにしたいです。
（ある特定の範囲内だけ、数字を入力すると特定の文字に変換。）
例）数字の1を入力すると、早
　　数字の2を入力すると、日
　　　　　　　　　　　　　　　等
現状入力する際、ドロップダウンリストをクリックして入力しています。
テンキーで数字を入力して、入力時間を少なくしたいです。

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

回答No.7

imogasi · ベストアンサー

#6の補足に関して
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err1
Application.EnableEvents = False
If Intersect(Target, Range("D8:AH28")) Is Nothing Then
Else
Target = WorksheetFunction.VLookup(Target, Worksheets("Sheet2").Range("a1:B7"), 2, False)
End If
Application.EnableEvents = True
Exit Sub
err1:
Application.EnableEvents = True
End Sub
VLookup(Target, Worksheets("Sheet2").Range("a1:B7"), 2, False)
の("a1:B7"),の部分は適当に変えてください。
Intersectを使うことがポイントです。

お礼

いろいろとアドバイスありがとうございました。
頂いたアドバイスで、状況にあったファイルが作れそうです。

回答No.6

imogasi

#3です。
＞力したいパターンは20くらいあります。
全く心配ありません。私の例ではSheet２は２行で終わってますが、早、日に当たる同類文字列を下に追加してください、１００個でも、対応表を１度作る手間が増えるだけです。
VLOOKUP関数のよいところです。
＞VBAの方でもう少し噛み砕いて教えていただければありがたいです
２つあって
（１）VBAでプログラムを動かす方法
（２）プログラムでやっている中身
（１）は入力しようとしているエクセルのワークシートのメニューのツール-マクロ-VisualBasicEditorを順にクリック
出てきた画面の多分左のフレームにVBAProjectと見えるところのMicrosoftExcelObjectの下のSheet1（入力するシート名のもの）をダブルクリックすると、主要部分が何もない画面が出ます。
そこへ＃３のコード部分を貼り付けます。
（操作）Sheet1へ戻って、A列に１や２を入れ、Enterを押すと、即座に早や日に置き換わってセットされます。
（２）システムがTargetという引数に、操作者が入力したデータを入れてくれています。
Application.EnableEvents = False
これはプログラムで早をセットしたときまた値の変化のイベントになり、ぐるぐる回りになるので、イベント機能を一時的にとめてます。
次の入力に備えて、最後に元に戻してます。
VLOOKUPはエクセル間数をVBAで使ってます。
その関数の引数（（）内）は
入力したデータ（１や２）
検索する表
早や日のある列の左から何番目か（私の例ではSheet2のB列ー＞第２列）
VLOOKUP関数のFALSE型（一致した数や文字列を引くもの）（TRUEの場合は数値範囲から文字列や数を返す型）

補足

たびたびすみません。

ＶＢＡを参考にさせていただき、Ａ列では実行可能になりました。

ワークシートの全てで変換されても困るので、Ｄ8からＡＨ28の範囲だけＶＢＡプログラムを実行する方法はないでしょうか？

重ね重ねよろしくお願いいたします。

回答No.5

Wendy02

こんばんは。Wendy02 です。

＞　”早”とマクロに入力するのではなく、A１に入力した値を先ほどの”早”と同じように変換することはできないでしょうか？

つまり、A1 に1 と入力してあるものを、「早」と変換することですか？

今、A1 には既に設定されているので、B1 で試してみました。

例えば、マウスボタンの左のダブルクリックすれば、数字は、文字に替わります。
Range("B1") は、Range("A1:A10")　に換えれば、その範囲で稼動します。
If Not ～ End If を取り去れば、シート全部で適用されます。ほとんど、メモリ上の負担もありません。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'変換
If Not Intersect(Target, Range("B1")) Is Nothing Then
　Select Case Target.Value
　　Case 1
　　 Target.Value = "早"
　　Case 2
　　 Target.Value = "日"
'　　Case 3
'　　 Target.Value = "遅"
　End Select
End If
'
End Sub

Case 3 のように、二行を入れれば、増やすことが出来ます。

この変換のコードの部分を使えば、まとめて変換も出来ます。

＞・このマクロでは何通り”早””日”のように記録できるのでしょうか？

既存のショートカットにぶつかりさえしなければ、キーボードの数だけ出来ます。
　　何もないキー
　　コントロールキー(Ctlr)
　　アルトキー(Alt)、
　　Alt+ Ctrl　

の組み合わせは確認しましたので、テンキー(0-9)だけで、１０　×４＝４０通りの組み合わせは出来るはずです。

{97} が、テンキーの'１を指します。2が、{98}, 3 が、{99} ... となります。

Application.OnKey "{97}", "ThisWorkbook.OnePr"

OnePr ... この名前に決まりはありませんが、分りやすい名前にするとよいです。

例:

If Not Intersect(Target, Sh.Range("A1:A100")) Is Nothing Then
　　 '設定されます。
　　 Application.OnKey "{97}", "ThisWorkbook.OnePr"　'何もつけない '1
　　 Application.OnKey "^{97}", "ThisWorkbook.CtrOnePr" 'Ctrl+'1
　　 Application.OnKey "%{97}", "ThisWorkbook.AltOnePr" 'Alt + '1
　　 Application.OnKey "^%{97}", "ThisWorkbook.AltCtrOnePr" 'Ctrl+Alt+'1
　 Else
　　 'こちらは、解除される方です。上と対にさせます。
　　 Application.OnKey "{97}"
　　 Application.OnKey "^{97}"
　　 Application.OnKey "%{97}"
　　 Application.OnKey "^%{97}"
End If

Private Sub OnePr()
　　ActiveCell.Value = "早"
End Sub
Private Sub CtrlOnePr()
　　ActiveCell.Value = "遅"
End Sub
Private Sub AltOnePrPr()
　　ActiveCell.Value = "休"
End Sub
Private Sub AltCtrlOnePrPr()
　　ActiveCell.Value = "忌"
End Sub

補足

ご回答ありがとうございます。
イメージとしては
ワークシートの上のほうに
　 A B C D E
1 早 日　遅
2　1 2 3
のように入力番号（B1）と変換希望文字列（A1)としたいです。
　（現況変則で変更が多いため、今後修正可能としたいためです）
4
5　　　8/1　2　3　4
6名前　早　早　日
7名前　日　日　
8名前
　上記のような勤務表に番号を入力しタブで次の日に
　全てを入力したらエンターを押し次の方の入力という形がベストです。

わがままばっかりですみませんがよろしくお願いいたします。

回答No.4

imogasi

＃３です。すみません、下記訂正します。
＞入力する範囲の一番上のセルに
入力する範囲の中の一番上のセルの隣列に

回答No.3

imogasi

Sheet2のA1:B2に
1 早
2 日
と対応表を作っておく
Sheet1に戻り
入力する範囲の一番上のセルに
（例としてA列なら）B1に
=IF(A1="","",VLOOKUP(A1,Sheet2!$A$1:$B$2,2,FALSE))
と入れて、B１０までとか、式を複写する。
入力が終わったらA列を非表示にする。
VBAなら
Sheet1のチャンジイベントに下記を貼り付ける。
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err1
Application.EnableEvents = False
If Target.Column = 1 Then '入力がA列なら
Target = WorksheetFunction.VLookup(Target, Worksheets("Sheet2").Range("a1:B2"), 2, False)
End If
Application.EnableEvents = True
err1:
Application.EnableEvents = True
End Sub

補足

VBAの方でもう少し噛み砕いて教えていただければありがたいです。
ちなみに入力したいパターンは20くらいあります。
よろしくお願いいたします。

回答No.2

Wendy02

こんばんは。

＞テンキーで数字を入力して、入力時間を少なくしたいです。

Worksheet_Changeのイベント・ドリブン型のマクロは、Enterに対するものですから、違いますね。

今、こんな風に考えてみました。必ず、ThisWorkbookモジュールに入れてください。以下は、テンキーのみの設定です。複数のシートでも、設定が可能です。

ただし、その変換で入力した文字は、Ctrl+Z（元に戻る）は利きません。

'<ThisWorkbook モジュール>
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
　 '設定シート（複数登録可)
If Sh.Name = "Sheet1" Then
　 'A1:A100 が、変換範囲
　 If Not Intersect(Target, Sh.Range("A1:A100")) Is Nothing Then
　　 Application.OnKey "{97}", "ThisWorkbook.OnePr"
　　 Application.OnKey "{98}", "ThisWorkbook.TwoPr"
　 Else
　　 Application.OnKey "{97}"
　　 Application.OnKey "{98}"
　 End If
End If
End Sub
Private Sub OnePr()
　　ActiveCell.Value = "早"
End Sub
Private Sub TwoPr()
　　ActiveCell.Value = "日"
End Sub

お礼

イメージしていたものでした。お早いお返事ありがとうございます。補足につきましてもよろしくお願いいたします。

補足

早速のお返事ありがとうございます。

欲を言うようですが、下記の点についてもお答え頂ければありがたいです。
・よく勤務時間帯と名称が変わります。
　”早”とマクロに入力するのではなく、A１に入力した値を先ほどの”早”と同じように変換することはできないでしょうか？
・このマクロでは何通り”早””日”のように記録できるのでしょうか？

重ね重ね出すみませんがよろしくお願いいたします。

回答No.1

pbforce

>　例）数字の1を入力すると、早
>　　数字の2を入力すると、日

１と入力したらその１を早と変換させればいいのですよね？
単語登録しちゃうのが早いと思いますが、いかがでしょうか？

補足

その方法も考えましたが、数字を入力して変換という２つの作業が出ます。そうなるとドロップダウンと同じ作業になってしまうので、できれば別の方法でお願いします。