EXCELでコロン「：」を使用した時にシリアル値にならないようにしたい

質問

EXCEL2007（他ヴァージョンは分かりません）にて、コロンを使用した時刻を入力すると、勝手に書式が変わって、表示は時刻のままですが、値がシリアル値になってしまいます。これが機能しないようにしたいです。
元々数式が入力されているセルで、必要に応じて時刻を手入力しているので、書式を文字列にするのは不可です（手入力後はマクロで元の数式を入れてます）。
要は通常は数式を使用し、コロン「:」を使用して入力した時は文字列としたいのです。
コロンを使用しないで、例えば15:00を1500のように入力すれば、目的は果たせるのですが、他の（EXCELの知識がない）作業者が使用するファイルのためどちらの入力方法でも稼働するようにしたいのです。同じ理由でシングルクォートの使用も不可です。
条件付き書式やTEXT関数を使用しても、シリアルへの変換後に機能するためかうまくいきませんでした。

回答No.13

end-u · ベストアンサー

こんばんわ。
もう解決してるかもしれませんが、ちょっとだけ失礼します。

通常は数式で運用して、入力した時には文字列として扱いたい場合、
どんな値を入力しても構わないなら、（aとかbとかもあり？）
試してもいいかもしれないかなーっと思うのは

With Range("A10")
　　.NumberFormat = "general"
　　.Formula = "=A1&A2"
　　.NumberFormat = "@"
End With

こんな感じで書式を「標準」にして数式を設定したら、
その後、「文字列」にしておく事です。
[f2][enter]などとしてしまうとちょっと...ですが、仕様として耐えれるならこれ簡単です。

さて、以下余談です。
回答#3のokormazdさんのコードを参考にされる場合、
>If isect Is Nothing Then End
この部分のEndステートメントには気をつけてください。
何か理由があるのか知りませんが、使用の際はヘルプ参照の事。
VBEのヘルプはクイックアクセス機能があります。
Endにマウスキャレットをあてて[F1]キーです。
それに
>Application.EnableEvents = False
は
>If isect Is Nothing Then End
この行の下に持ってってください。

>では最後に、回答頂いたコードが「うまく動かない」「目的を果たせない」
>と判断された場合にはこちらはどのようにアクションすればよいか教えてください。
質疑応答を重ねる事で解決できます。
「うまく動かない」状況、「目的を果たせない」状況をできるだけ詳しく書いてください。
どこが悪いのかわからないのは質問者の方にはよくある事です。
調べる方法さえもわからない方もいらっしゃるでしょう。
その時、回答側が『経験者』であれば、その辺の機微を察し、
適確な質疑を重ねる事でどこに問題があるか探り出す事ができます。
イベントコードの貼り付け先が違ったりする事ってよくある事ですし、
EnableEvents=Falseのままだったりする事もありがちです。
今回のように、そのような可能性がないか回答側から問いかける事で大抵は解決するでしょう。
また、デバッグのやり方を提示して、質問者の方自ら確認できるようにアドバイスする回答者もいるでしょう。
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_030.html

文字だけのコミュニケーションってあいかわらず難しいですね。
お互い、言わんとするところがなかなか噛み合わず、ずれてしまう事も多々あります。
なもんで『質疑応答を重ねて試行錯誤する事を続ける事』でしか解消できないと思いますよ。
もうちょっと肩の力を抜けばいいのに。
...って思ったりなんかしたりする事もないではない...かな :D

お礼

お世話になります。
回答のコードの使い方ですが、
> =IF(A1=191,QRCD!D14&QRCD!D15,"")　を
> 手入力後はマクロで元の数式を入れてます。
と書いてますので、このマクロに追加すると言うので良いのでしょうか。
今自宅ですので、実際のファイルが手元にないので試せませんが、なんとなくそれでできるような気がします。
明日試してみて結果ご連絡します。

余談の件ですが、クイックアクセス、自宅PC（2002です）では「インストールされていません」と出てしまいました。そもそも＃３さんのコードが動かないみたいなので、これも会社のPCで試してみます。

> 質疑応答を重ねる事で解決できます。
なのですが、当初の＃３さんの回答に対する補足はそのつもりで書いたつもりだったのですが、どうやら嫌なようにとられてしまって困惑していました。＃２さんの回答は、こちらで一生懸命説明しているのに、それは無視され、あるべき姿を押し付けられたような感じがして、ちょっとでした。
今回の質問のやり取りをしているときに、全く別の質問の回答をしました。こちらでは初めの質問の希望どおりにできているのですが「できない」と回答があり、正直少しばかりムッとしました。その後原因は質問者が間違えて設定していることが予想できたのでそれ以上は突っ込みませんでした（質問も締め切られてしまったのでコメントできませんでした）。これも質問者の「できない」は悪気はなかったと思います。ですが、同じようなことを自分もしてしまっているんだと気づきました。
＃２さんも＃３さんも、初めの私の補足で多分カチンと来てしまったのでしょう。そのせいで話がややこしくなってしまったようです。
今後は補足やお礼の内容に気をつけるようにしたいと思います。
本来の回答の他に、アドバイスまで頂きましてありがとうございました。

補足

お世話様です。
補足とお礼が逆になってしまいましたが…
教えていただいたコードでばっちりでした。
ActiveCell.FormulaR1C1 = "=IF(R[-22]C[-3]=191,QRCD!R[-9]C&QRCD!R[-8]C,"""")"
の代わりに、
With Range("D23")
.NumberFormat = "general"
.Formula = "=IF(R[-22]C[-3]=191,QRCD!R[-9]C&QRCD!R[-8]C,"""")"
.NumberFormat = "@"
End With
としただけで、私のしたいことがイメージ通りクリアできました。とても簡単で、すっきりしていますし他のコードへの影響もありません。
大変助かりました。

ENDステートメントについてですが、ヘルプの解説に書かれている内容についての注意でしょうか。確かにプログラムが強制終了し、初期化されてしまうようなので、他のマクロと合わせて使う場合は気をつけないといけないということですね。ありがとうございました。

これにてこの質問は締め切ることにします。
ありがとうございました。

回答No.12

okormazd

#3です。
「ヤレヤレ・・・」慨嘆です。
「新しいブックのThisbookWorkへ貼り付け」じゃ動くわけないや。
「ThisWorkbook」のことだろうけど。
ThisWorkbookのイベントに「Worksheet_Change」イベントなんて無いもんね。自分で作れば別だが。
「Worksheet_Change」はSheetのイベントだから、Sheet1とかのコードモジュールに貼り付けなければダメでしょう。

お礼

いやはや、大変失礼しました。
ThisWorkbookのWorkbook_SheetChangeイベントとごっちゃになっていました。（それと確かにThisbookWorkではなく、ThisWorkbookでした。）
と言うか、こういったイベントはThisWorkbookで使うものとばかり思っていたもので…手持ちの参考書の事例では、どれも「ThisWorkbookを選択し…」という記載があったもので。これはあくまで一つの方法だったのですね。
早速、試してみたところ、新しいブックに適用rangeをA10のみとし、A10に"=A1&A2"、A1に"1"、A2に"2"を入力。A10には"12"が表示されている状態で、A10に"10:00"を入力すると、所定通り文字列の10:00がでました。
ただ、実際のファイルへ適用してみると、他のマクロとの動作に影響が出てしまうこと、他にWorkbook_SheetChangeイベントを設定しているせいか、競合というのでしょうか？うまく動きませんでした。これについてはこちらサイドの問題ですので、コードを見直して対応したいと思います。
色々とありがとうございました。

回答No.11

okormazd

#3です。
わたしは、#3で回答した後、コメントを控えていました。それは、#3の回答に対するコメントで、「あ～この質問者はバカだ、放っておけ」ということからでした。しかし、Wendt02さんの回答で、「回答頂いたコードが「うまく動かない」「目的を果たせない」
と判断された場合にはこちらはどのようにアクションすればよいか」
というにいたって、放っておけ、というわけにいかないと思いました。
「回答頂いたコードが「うまく動かない」「目的を果たせない」
と、どういう風に判断するのですか。回答者はあなたに回答のコードの評価を依頼しているのではありません。一般にこのようなサイトでは悪意で無ければ、まともなコードを提示するでしょう。悪意のコードでもよいということになれば、こんなサイトは成立しないのではないですか。だとすれば、回答されたコードは質門者の期待にいくらかでも沿うように回答されるはずです。それをほとんど根拠も無く、「うまく動かない」「目的を果たせない」というのはどういう根拠なのでしょう。
自分が勝手に間違った操作をして、期待通りの結果を得られなかったからといってそのコードはダメだとするより先に、自分の操作が間違っていたかもしれないとどうして思わないのでしょうかね。

お礼

お世話様です。
ご指摘の件ですが、もちろん自分の操作が間違いないかの確認はしています。他のファイル（マクロ）の影響がないか、セキュリティの設定は・・・等。コードはコピーして使用していますので、ミスはありません。適用rangeの変更のみです。それでも二回以上試して動かないので、事実を書いたものです。回答者を批判、評価する意思など毛頭ありません。
根拠を問われましても、上記の通りですし、回答の内容に
> 理由がいまいち不明なので、これでいいかどうかは、わかりません。
や
> 即席で作りましたので、細かい部分は見ていませんが、
等と書かれていますので、「動作確認してないのかな？」とか「バグあって当然ということかな？」という認識でいました。
通常の会話でなら、コードを教えててくれた人へ（あげた人から）、「動かないよ」と言うのは（言われるのは）全く普通のことと思いますが、ネット上の文章になりますので、互いの誤解の発生により気分を害してしまうこともあるかと思います。書き方（マナー？）について至らなかった件は誤ります。申し訳ありません。

前の回答#11のお礼に記載していますが、「3#で教えていただいたコードをコピーして、新しいブックのThisbookWorkへ貼り付け。適用レンジは「A1:A10」を「A10」へ変えている」ことで正常稼動しない（こちら側の）原因として何が考えられるでしょうか。
お分かりでしたらお教え頂きたいと思います。

回答No.10

okormazd

#3です。
「適用rangeをA10のみとし、A10に"=A1&A2"、A1に"1"、A2に"2"を入力しました。A10には"12"が入力されますが、A10に"10:00"を入力すると、やはり文字列ではなくシリアル値に変更（表示は10:00）されてしまいました。」
あなたの言うことが理解できません。私のコードは、ちゃんと動くはずですが。あなたの言うように実行すれば、（表示は10:00）ではなくて、10:00:00になるはずですが、rangeだけでなく、
mystr = Format(Target.Text, "hh:mm")に換えましたか。そうすれば、表示は10:00になるでしょう。それはいいのですが、表示が10:00ではいけないのですか。どのように表示されればいいのですか。
この表示は、シリアル値ではなくて、文字列のはずですが、これがシリアル値だというのはどうしてわかったのですか。
Application.EnableEventsの場所にも間違いはありません。
もしかすると、このコードを間違えて、実行が途中で止まったりしませんでしたか。そうすると、Application.EnableEvents=Falseのままなので、次に実行しても、イベントが働かないので、シリアルのままになります。その場合は、Application.EnableEvents=Trueをイミディエイト　ウインドウで1度実行します。
10:00がシリアル値か文字列かの簡単な判定は、セルの書式を「パーセンテージ」にすれば、シリアル値なら41%とかになり、文字列なら10:00のままです。
「「うまく動かない」「目的を果たせない」と判断された場合には」自分のやり方が間違っているかもしれないと少しは考えてみることも必要ではないですか。「目的を果たせない」というなら、回答者は質問者の質問の内容から判断して回答しているので、もしそれが違っているなら、回答者が理解できるように説明していただかないとわかりません。とにかく、「何をどのように表示」させたいのですか。「10:00」ではダメで、何にしたいのでしょう。

補足

お世話様です。
せっかく回答頂き、「うまく稼働しませんでした」という書き方に気を悪くされてしまったかもしれません。悪気はありませんでした。申し訳ありません。
ですが、もう一度試してみてもうまくいきません。そちらでは正しく稼働しているようなので、やはり何かが間違っているのだと思います。
こちらでは、教えていただいたコードをコピーして、新しいブックのThisbookWorkへ貼り付けただけです。適用レンジは「A1:A10」を「A10」へ変えています。（初めもそうでしたが）セキュリティも「低」に設定し他のファイルも開いていませんので、その他の影響は無いと思います。
この状態でA1へ「10:00」を入力すると「10:00」の表示になります。「10:00:00」ではありません。もちろん「mystr = Format(Target.Text, "hh:mm:ss")」のままです。またシリアル値かどうかの判断ですが、B10セルへ「=A10」を入れ表示形式を標準にしたところ、シリアル値の「0.416667」が表示されましたのでそう判断しました。
試しにA10,B10の書式をパーセンテージにしてみたら、やはり両方42%になってしまいます。
何が悪いのか教えていただければ幸いです。
よろしくお願いします。

回答No.9

Wendy02

こんばんは。

ご質問者さんは、一度、お金を払ってでも、実際に他のプロの方に相談されたほうが良いと思います。掲示板では、プロも素人の方も、自称では話にならないし、また、掲示板では、限界があります。

=IF(A1=191,QRCD!D14&QRCD!D15,"")

もともと、この数式とそれぞれのQRCD!D1　とQRCD!D15の内容を公開はしていません。
たぶん、QRCD!D14&QRCD!D15　ここが時間値になるというわけでしょう。

片方が、Hour で、もう片方が、Minute というところでしょうか？

おそらく、Excelには、文字列というのは、明示的に、書式「@」や、書式文字列「'」を入れて不活性化させることであって、おそらくは、テキスト値だけの話だと思います。

そして、そこに「：」が入っては困るという話になっているのでしょうけれども、それは、書式で処理すればよいだけの話で、テキスト値に、VALUE関数にして、書式を「hmm」とでもすれば、「：」なしのデータは作れます。

=IF(A1=191,(QRCD!D14&":"&QRCD!D15)*1,"") とし、ユーザー定義の書式を「hmm」または、「hhmm」などとします。書式で、「：」を抜いたものにしておいて、入力の際には、「：」を入れればよいのです。

>これが何らかの理由で修正が必要な場合があるので、同じセルへ入力しないと、作業的におかしな感じがします。

表示の狂いはありません。ただし、数式は消えてしまいますが、数式を消したくなければ、このようにすればよいです。

=IF(A1=191,IF(E1="",(QRCD!D14& ":" & QRCD!D15)*1,E1),"") で、E1に時間値を入れたら、修正するという方法も可能です。

入力規則はあくまでも、入力規則です。その程度のことは事前に見当されてほしいものですが、私の考える範囲では、この件では、入力規則自体も簡単ではないと思います。

>コロンを使用した値が入力できなければ目的は果たせます。

これについては、検討しましたが、簡単ではありません。文字列で入れていれば別ですが、そのような判定を入力規則では出来ないと思います。文字列で入れているという前提では、本末転倒になってしまいます。数字に「：」を入れて、再び数字を入れれば、概ね、瞬間に少数点の数字に変わります。その中身には「：」はありません。単なる数字だけです。私自身で解決方法もありますが、それなら、こちらの考えたVBAを使えばよいのです。

>同様に「違う値が入力できない」コードを作成いただいたようですが、入力規則の方がスマートですし、

スマートとか、いただいたようですとか、感覚的な発言は、慎重にしてください。まったくのVBA初心者の方が、VBAのコードを見ただけで、ダメだしするようことは、どこの掲示板でもマナー違反です。何のために時間を割いて回答を書いているのか分からなくなります。

VBAをお使いになるなら、もう少し、VBAを勉強されることをお勧めしたいと思います。そうそう簡単には、できるようにはなりません。せっかくの貴重な回答をもらったものドブに捨てかねないものです。単にVBAの回答者の回答は、ありきたりの道具を提供しているわけではないことをご理解になると思います。私も、これで終わりにさせていただきます。

お礼

お世話様です。
> たぶん、QRCD!D14&QRCD!D15　ここが時間値になるというわけでしょう。
その通りです。ですが、その点については
=IF(A1=191,QRCD!D14&QRCD!D15,"")
の式を出した時点で説明もしております。

回答頂けるのはありがたいのですが、私の補足をちゃんとお読み頂いているでしょうか。
> 入力の際には、「：」を入れればよいのです。
この点についても、社内全工程で「：」を使わないで統一しているので、この工程でだけ「：」を使うわけにはいかない（表現は異なりますが）と説明しています。

> =IF(A1=191,IF(E1="",(QRCD!D14& ":" & QRCD!D15)*1,E1),"") で、E1に時間値を入れたら、修正するという方法も可能です。
それくらいならレベルの低い私でも考えました。が、時間を入力するセルが複数あるのはおかしいと思いませんでしょうか。その為私のその方法を採用しませんでした。

入力規則に疑問をもたれているようですが、入力値の制限を整数の１～2359にしています（これも記載しています）。
> 数字に「：」を入れて、再び数字を入れれば、概ね、瞬間に少数点の数字に変わります。その中身には「：」はありません。単なる数字だけです。
そうです、「：」を使えば小数点を含んだ数値に変わりますので、整数ではない。つまりけられます。もちろん「24:00」や「48:00」を入力してしまえば入力されてしまいますが、元々発生頻度の少ない入力ミスに対しこれらを入力してしまうといった状況は、きわめて可能性が低いと考えられますので、この手段が有効と判断しました。「フールプルーフなのだから100％でなければならない」と言われるかもしれませんが、何事も100％は難しいものです。

> まったくのVBA初心者の方が、VBAのコードを見ただけで、ダメだしするようことは、どこの掲示板でもマナー違反です。
そうでしたか。それは大変失礼しました。
では最後に、回答頂いたコードが「うまく動かない」「目的を果たせない」と判断された場合にはこちらはどのようにアクションすればよいか教えてください。
よろしくお願いします。

回答No.8

Wendy02

こんにちは。

>社内において全工程でコロンを使わない（15:00を1500と表す）方法で統一していますので、一か所だけ異なると混乱を招きます。とは言え、派遣などで新しく入ってきた人の場合「時間を現すのはコロンを使う」のが当たり前のはずですから、慣れるまでにコロンを使って入力してしまいこともあるでしょう。

補足を読ませていただいた限りでは、私の話は見えてこないでしょうね。そういう問題というのは、入力者がどうであろうとも、最終的な出力の問題は、シリアル値であろうが、なかろうが、統一すればよい話ですから、本来は、シリアル値に統一させたほうがよいのです。出力データのエラー・フリーにするのは、まったく別問題なのです。もう少し、VBAが上達すれは、何だこんなことか、と分かっていただける話です。ExcelはExcel用のデータに合わせるほうが良いのです。出力用には別にするのです。

>これで誤った値が出力されるのを防止したいので、今回の質問をするに至りました。

おっしゃっている話というのは、見かけ上の問題だと思います。その程度でなら、どうにでもなります。
即席で作りましたので、細かい部分は見ていませんが、違うデータが入ると削除されてしまいます。
ただし、セミコロン(;)までは、受けられます。

'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
　　On Error GoTo EndLine
　　Dim r As Range
　　Dim myDat As String
　　'-------------------------------------------
　　Const IN_COL As Integer = 1 '1列目 ;対象列
　　'-------------------------------------------
　　Set r = Intersect(Target, Columns(IN_COL))
　　If r Is Nothing Then Exit Sub
　　If Target.Count > 1 Then Exit Sub
　　If Target.Value = "" Then Exit Sub
　　Application.ScreenUpdating = False
　　Application.EnableEvents = False
　　If InStr(1, Target.Value, ";", 1) > 0 Then
　　　　myDat = Replace(Target.Text, ";", ":", 1, , vbTextCompare)
　　End If
　　If IsDate(Target.Text) And myDat = "" Then
　　　　sTimeChange Target
　　ElseIf myDat <> "" Then
　　　　sTimeChange Target, myDat
　　ElseIf IsNumeric(Target.Value) Then
　　　 Target.NumberFormat = "000"　'数値の場合
　　　 Target.Value = Target.Value
　　ElseIf IsNumeric(Target.Text) = False Then
　　　　Target.Clear
　　End If
EndLine:
　　Application.EnableEvents = True
　　Application.ScreenUpdating = True
End Sub
'サブルーチン
Sub sTimeChange(arg As Range, Optional myDat As String)
　　If myDat = "" And InStr(arg.Text, ":") > 0 Then
　　　　myDat = (arg.Value)
　　End If
　　myDat = Format$(myDat, "hhmm")
　　arg.Clear
　　arg.Value = myDat 'ゼロサプレスしないなら、Format$(myDat,"0000")
End Sub

'-------------------------------------------
ただ、入力値がDate型になるのを防ぐだけでも良いはずです。#6さんの日付値を判定する関数があったか忘れましたが、あれば、入力規則も良いかもしれません。

お礼

お世話様です。何度もありがとうございます。
> 出力データのエラー・フリーにするのは、まったく別問題なのです。
は分かっています。
> シリアル値であろうが、なかろうが、統一すればよい話ですから、本来は、シリアル値に統一させたほうがよいのです。
についても先にも書いていますが分かっています。
ですが、（これも書いてありますが）初めに使用されるデータが文字列なんです。「文字列→シリアル」への返還は不可欠です（理由は説明しました）。同じ数式を使用する限り、文字列を使用しなければなりません。するとシリアルで入力すると「シリアル→文字列→シリアル」と二重に変換が必要になります。これはばかげています。もちろん同じ数式を使わず、別数式を使用すればいいのかもしれませんが、これもスマートではありません。そこで初めの質問である「キャスティング無効化」が「簡単にできるのであれば」と思いここで質問させて頂きました。結果的に簡単ではないことが分かりましたが。
真の目的は「誤出力の防止」です。そのための手段としてキャスティング無効化を考えましたが、別の手段として#6さんにアドバイス頂いた入力規則が有効と思いました。コロンを使用した値が入力できなければ目的は果たせます。
同様に「違う値が入力できない」コードを作成いただいたようですが、入力規則の方がスマートですし、エラーメッセージも簡単に出せるのでこちらを採用しようと思います。
色々と面倒おかけしました。

回答No.7

ASIMOV

結局、
１．15:00であろうが1500であろうが、文字として入って欲しい
２．後で、VBAで「式」を入れるので、書式は「文字列」では困る（標準にする）

という事ですよね

で、VBAを考えてみました
15:00と入れた時点で、EXCELが勝手に書式を「時間」にするのはどうしようもないので、VBAで強制的に戻します

書き込むセルを仮に「A2」としています
Changeイベントに記述します
---------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Address = "$A$2" And Cells(2, "A") < 1 Then
　　　　Application.EnableEvents = False
　　　　mt = Hour(Cells(2, "A"))
　　　　mm = Minute(Cells(2, "A"))
　　　　Range("A2").NumberFormatLocal = "@"
　　　　Cells(2, "A") = mt & mm
　　　　Range("A2").NumberFormatLocal = "G/標準"
　　　　Application.EnableEvents = True
　　End If
End Sub
---------------------------
２行め後半で、書式が「時間」に変わったかどうかを判定してますが、ちょっと怪しいです
シリアル値が24:00までは１以下であることを利用していますが、24:00以上の値を入れるとミスります
あと、書式は、一旦「文字列」にして書込んでから「標準」に変えてます
これによって、「1500」が文字として書き込める様です

お礼

回答ありがとうございます。
試しに新しいブックで使ってみましたが、うまく動きませんでした。
ＶＢＡ技術乏しいもので、何が悪いのかさっぱり(^_^;)
それと、申し訳ないのですが、ワークシートチェンジイベントは、他の動作で使用し（ＱＲコードを読み込んだ後、所定のセルへカーソル移動）、動作後無効化しているので、使えません（と思います）。
ありがとうございました。

回答No.6

sekkii

知識の乏しい私が口をだすのはおこがましいのですが…
「データの入力規則」を使ってはどうかな？と思いました。
見当違いでしたらごめんなさい。。

お礼

なるほど、そうですね！
入力値を整数にして１～2359にしてやれば、コロンを入力した値を入力してもメッセージ出して、誤入力の防止できますね。
発想の転換です。
関数使って、隣のセルへメッセージ表示させようかと思っていたところですが、入力規則使った方がスマートです。
ありがとうございました。

回答No.5

Wendy02

#2の回答者です。

>ミス防止や効率化のためにEXCELの関数とVBAで自動化したものです。

補足された内容で、残念ながらシングルクォート不可の因果関係自体は理解出来ませんでしたが、ただ、なんとなく事情は想像できます。

ちょっと長くなりますが、私の経験談から書かせていただきます。何かの参考になれば幸いです。

まったくExcelを知らない人に使わせようという前提なら、今のスタイルは、まったく違うだろうと思います。特に、中高年の人に使わせようとすると、まったく想定できない使い方に出会うことがあります。それで、それを教えようと、入力を見ていたりしても、プライドを傷つけるというのか、怒り始めるので、手が付けられないのです。それを急ぐので若い人に頼んだら、仕事を奪うのか、と言い始めます。それも、1週間程度、パソコン教室に通ってもらっても、おまけに、私の使っていた設計用のテキストを無断で持ち出しても、その程度でした。それなのに、自分の言葉が、東京の方言でさえ気がつかないので、IMEが変換できないと言うレベルの人でした。本当に困ってしまいました。

若い人は、パソコン教室に行くこともなく、1週間、毎日、PCの前でなんとなく遊んでいるなって放っておいたら、もう、1年やっていた人よりも、上手にできるようになっていました。私は、その人があまりに早く覚えてしまい、怖くなってしまいました。

ご質問者さんの抱いているイメージと、Excelを使わせるというイメージとにギャップがあることなんだろうと思います。私は、十数年も、こんなQ&A掲示板のサポートで、時々、表計算の機能を使わせない、というリクエストを言う人に時々出会うことがあります。キャスティングは、Microsoft 系の表計算としての、利点であり欠点でもあります。この機能は、1980年代からの機能ですから、今更、しょうがないです。

もし、VBAのある程度の技術力があるなら、数式との組み合わせ、という方式は、完全にやめたほうが良いようです。入力アプリケーション・システムという形にして、Excelのセルやコマンドは、ほとんどさわらせないという方式にします。

UserForm にするのか、#4さんでもサンプルが出ていますが、埋め込み型のテキストボックスや他のコントロールに入力する方法で作ったほうがよいです。場合によっては、Excelのメニューなどは、一切隠して、ボタンで保存、終了まで作ってしまいます。もちろん、シートタブも隠して、ボタンのクリックでシートの切り替えを作ります。

本来、Excelを使う人が、1500 と 15:00 自体を分からないというのでは、まったく話にならないと思います。しかし、1500で入れようが、15:00で入れようが、どちらでも「シリアル値」になるという条件自体は、ある程度マクロの技術があれば簡単なことですが、「シリアル値」自体を不可というのでしたら、それは、基礎的な誤解だと思います。

#2で書いたように、シリアル値であろうがなかろうが、セルに表示している限りは、VBAでは関係ありません。表示していない、というなら、表示自体を時間書式にしなければなりません。

これは、VBAの技術の低い人とトラブルになったことですが、ユーザーが入力した値を、完全、エラー・フリーで、その値をデート型に代入して設計できるかどうかは、技術力の差そのものです。こんなことは、最初は教わらなくては自分で開発するのは容易なことではありません。これには、多くの先人たちが、ああでもないこうでもないとした技術なのですから。

今のご質問から予想されるスタイルは、まったく新しくしないと、Excelを知らない人には使えるようなアプリケーションにはならないと思います。それを、どうするかは、ご自身がお決めなってください。こういう限られた掲示板では、手取り足取りということは出来ません。

#3さんのマクロをダメだししたようですが、少し直せば、こうしたQ&A掲示板での回答としては、目的自体はご質問者さんの満足するものが得られるように思います。また、入力の間違いに対して、補正できるようにするか、エラー表示するかレベルなら、簡単だと思います。ただし、VBAを扱うなら、最低限、シリアル値不可などという話はやめていただきたいです。時間表示している以上、文字であろうが、シリアル値であろうが、そんなことは、まったく関係ないのですから。

>VBAは、自動マクロ記録を中心に参考書をめくりながら作成するレベルです。

それは難しいかもしれません。私自身は、設計自体の問題を言っているので、考え方が違います。Excelを使えない人を対象にするのなら、ある程度の範囲で、たかがExcelでも、クラスの設計から始めないといけないようです。それでも、Excelを使う限りは、十分だと言えるものはありえないかもしれません。

お手本となる材料は、お手元にないのだろうと思います。ここの掲示板のどこを探してもないと思います。ここは設計自体を教えることはありません。いくつかのVBA専門掲示板のダウンロードサイトなどには、VBEがプロテクトせず公開してくれているものがありますから、それを参考にされてもよいと思います。目からウロコのコードの流れが分かります。

=IF(A1=191,QRCD!D14&QRCD!D15,"")

これの程度のものは、VBAで簡単に組み込めるはずでず。どの程度、私が想定できているか分かりませんが、これが、シリアル値がどうとかいう話と結びつけてしまったことが、話をややこしくしているように思います。たぶん、VBAでは、シリアル値でも、そうでなくても、そんなことは関係ないということは、理解されていないと思います。いくら、言葉を尽くしても、技術的なものですから、経験的には、この種の内容で理解してもらったことはないので、あまり深追いはしません。この種の質問に対して、仮にピンポイントの解答をしても、あまり意味がないように思います。

具体的な内容が出せないのは、今のご説明では、せっかくしていただいたにも関わらず、回答者として、理解できていないということです。しかし、それは、たぶん今の流れでは、こちらが納得するのは無理のようですから、それについてはご容赦願います。

補足

丁寧な回答ありがとうございます。
当方VBAに技術力ありませんので、UserForm等は現状考えておりません。いずれは身につけようと思ってはいます。

> VBAを扱うなら、最低限、シリアル値不可などという話はやめていただきたいです。時間表示している以上、文字であろうが、シリアル値であろうが、そんなことは、まったく関係ないのですから。

とのことですが、やはりこちらの説明が不足しているようです。
本来の使い方であれば、おっしゃる通りだと思います。
しかし、今回私が作成したファイルで初めに入力するロット情報ですが、日時がシリアル値ではなく、文字列情報なのです。これをシリアルに変換して経過時間を算出しています。ロット情報そのものを変えればいいのかもしれませんが、親会社の作成したQRコード内の情報のため変更することができません。
QRコードの情報でも、手入力した際の時刻でも、同じ数式で経過時間を算出するために（初めの質問の目的が果たせないなら、ここを変えることになると思いますが）、キャスティングを無効にしたいのです。
時刻入力に全てコロンを使用すれば解決すると考えられるかもしれませんが、社内において全工程でコロンを使わない（15:00を1500と表す）方法で統一していますので、一か所だけ異なると混乱を招きます。とは言え、派遣などで新しく入ってきた人の場合「時間を現すのはコロンを使う」のが当たり前のはずですから、慣れるまでにコロンを使って入力してしまいこともあるでしょう。これで誤った値が出力されるのを防止したいので、今回の質問をするに至りました。
どうやら、難しいようなので、別の手段を考えてみることにします。
ありがとうございました。

回答No.4

ASIMOV

こんなのはどうでしょう．．．

図参照
その１
コントロールツールボックスからテキストボックスを D2 の辺りに貼り付けています
コードの表示で、
changeイベントに
Private Sub TextBox1_Change()
　　Cells(2, "A") = "'" + TextBox1.Text
End Sub
とします

その２
作業セル（B5とC5）を使って、時と分を分けて入力してもらいます
A5 には =B5&":"&C5 と入れておきます

お礼

回答ありがとうございます。
作成いただいたサンプルその１で、A2に相当するセルへ
=IF(A1=191,QRCD!D14&QRCD!D15,"")
が入力されており、これが通常、ロットの時刻情報を現しています。
これが何らかの理由で修正が必要な場合があるので、同じセルへ入力しないと、作業的におかしな感じがします。

その２ですが、
時と分を分けて入力するのは効率が悪い気がしますし、上と同じ理由でうまくありません。

こちらの目的が不明瞭なようで、面倒おかけします。#5の回答へ補足しておりますので参照ください。
別の手段を考えようと思います。ありがとうございました。

回答No.3

okormazd

「手入力後はマクロで元の数式を」
ということなので、VBAはいくらか使えるのだと思います。
で、
何でこのようにしなければならないのか理解に苦しみますが、
どうしてもというなら、
下記のようにすればよろしいのではと思います。
理由がいまいち不明なので、これでいいかどうかは、わかりません。
そのシートモジュールに、イベントプロシージャ、
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range, mystr As String, c As Integer
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A1:A10"), Target)
If isect Is Nothing Then End
c = InStr(Target.Text, ":")
If c > 0 Then
Target.NumberFormat = "@"
mystr = Format(Target.Text, "hh:mm:ss")
Range(Target.Address) = mystr
End If
Application.EnableEvents = True
End Sub
適用rangeは適宜変更のこと。

補足

理由については、下の回答に補足しましたので参照ください。
VBAは、自動マクロ記録を中心に参考書をめくりながら作成するレベルです。系統だって勉強したわけではないので素人に毛が生えた程度とお思いください。
ですので、お書き頂いたコードについてなんとなくしか分かりません。
ワークシートのチェンジイベント使って、入力したセルの値に合わせて型を変更している様ですが、Application.EnableEventsのtrueとfalseの位置が悪いのでしょうか？うまく稼動しません。
試してみたのは、適用rangeをA10のみとし、A10に"=A1&A2"、A1に"1"、A2に"2"を入力しました。A10には"12"が入力されますが、A10に"10:00"を入力すると、やはり文字列ではなくシリアル値に変更（表示は10:00）されてしまいました。