Excel VBA ﾏｸﾛ実行中のみテンキー操作できるようにしたい

Question

VBAに詳しい方、教えて下さい！
内容ですが、VBAでｽﾄｯﾌﾟｳｫｯﾁ機能を追加し決まったｼｰﾄに
その秒数を張り付けていきたいです。
その中で今困っていることが下記のところです。
【やりたいこと】
1　START/STOPﾎﾞﾀﾝを1回押すとB2:C3ｾﾙが時間観測を開始します
2　ENTERｷｰを押していくと作業内容の上側のｾﾙだけ秒数が入ります
　　（2行飛ばしで秒数が入っていきます）
　↑この実行はNo1の時間観測中だけ有効にしたいです。
　時間観測していないときはENTERｷｰを押しても処理しないようにしたいです。
3　{RIGHT}”→”ｷｰを押していくと６行目（作業内容1）の次の列に秒数が入ります
　これもNo2と同じようにNo1の時間観測中だけ有効にしたいです。
　時間観測していないときは{RIGHT}”→”ｷｰを押しても処理しないようにしたいです。

今現在のｺｰﾄﾞは下を参照下さい
お手数おかけしますがご教授願います。

Option Explicit

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private cnt As Range
Private blnStop As Boolean
Private blnStart As Boolean
Private dblTimer As Double
Private bLap As Double
Private cntLap, No, No2, i, j, k, S, F, X, Z, V, R As Long

Sub StartStop()
    If blnStart = True Then
        blnStop = True
        Exit Sub
    End If
    bLap = 0
    cntLap = 0
    blnStart = True
    blnStop = False
    dblTimer = Timer
    Do Until blnStop = True
        Cells(2, 2) = Int((Timer - dblTimer) * 100) / 100
        Sleep 1
        DoEvents
    Loop
    blnStart = False
    blnStop = False
End Sub

Sub Enter_set1()
    'キー割り当て用
    Application.OnKey "{ENTER}", "nui" 'テンキーのエンターキーのみ
    Application.OnKey "~", "nui"  'キーボードのエンターキーのみ
End Sub

Sub nui()
'X = 18
V = 6
R = Cells(Rows.Count, 1).End(xlUp).Offset(-6, 0).Row - 1
If 2 >= Z Then
Z = 2
If R >= No2 Then
      No2 = No2 + 2
      Sleep 1
     Cells(No2 + 4, Z) = Round(Timer - dblTimer, 2)

     Else
     Z = Z + 1
     No2 = 0
     No2 = No2 + 2
     Sleep 1
     Cells(No2 + 4, Z) = Round(Timer - dblTimer, 2)
End If
   Else
     If R >= No2 Then
      No2 = No2 + 2
      Sleep 1
        Cells(No2 + 4, Z) = Round(Timer - dblTimer, 2)
     Else
     Z = Z + 1
     No2 = 0
     No2 = No2 + 2
     Sleep 1
     Cells(No2 + 4, Z) = Round(Timer - dblTimer, 2)
   End If
  End If

End Sub
Sub RIGHT_set1()
    'キー割り当て用
    Application.OnKey "{RIGHT}", "RGT"  'キーボードのエンターキーのみ
   End Sub

Sub RGT()
Z = Z + 1
     No2 = 0
     No2 = No2 + 2
     Sleep 1
     Cells(No2 + 4, Z) = Round(Timer - dblTimer, 0)
End Sub
 
Sub LapSplitClear()
   S = 6
   F = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 2
   'F = 25
   Do While F >= S
    Range(Cells(S, 2), Cells(S, 16)).ClearContents
    S = S + 2
    Loop
    Cells(2, 2) = 0
    Z = 0
    No2 = 0
End Sub

因みに
ThisWorkbook　には下記ｺｰﾄﾞが入ってます
Private Sub Workbook_Open()
    Call Enter_set1
    Call RIGHT_set1
End Sub

Sheet1（Sheet1）　には下記ｺｰﾄﾞが入ってます

Private Sub Worksheet_Activate()
 Call Enter_set1
 Call RIGHT_set1
End Sub
 
Private Sub Worksheet_Deactivate()
 Application.OnKey "~"
 Application.OnKey "RIGHT"
End Sub

fujillin · Answer

こんばんは

文章を斜め読みしただけですが、「シートの保護」を利用すればよいのではないでしょうか？
記入させたくないセルだけロックして、「選択不可」にしてしまえば、入力はできなくなります。
こちらの方が、キー入力を制御するよりもはるかに簡単ですので。

マクロから値を記入しているような雰囲気なので、UserInterfaceOnly:=Trueにしておけば、保護がかかったままで、マクロからの値の変更等は可能になります。

以下の例は、Sheet1のB:E列のセルだけ選択不可にする例です。
（範囲はよくわからないので、サンプル用に適当です）
Sub Sample()
With Worksheets("Sheet1")
　.Cells.Locked = False
　.Columns("B:E").Locked = True
　.Protect UserInterfaceOnly:=True
　.EnableSelection = xlUnlockedCells
End With
End Sub

※ 手操作から対象セル範囲に入力等をすることがなければ、一度、保護をかけておきさえすれば、後はそのままでもよいと思います。

fujillin · Answer

No1です。

もう一回見たら、タイトルが
＞ﾏｸﾛ実行中のみテンキー操作できるようにしたい
でしたね。
逆かと思って勝手に勘違いしたみたいです。失礼しました。

とは言え、保護の機能を逆に利用すれば実現できると思います。

fujillin · Answer

No2です

コードを見てみたら、キーを拾っているのですね。
No1、No2の回答はちゃんと見ていませんでした。
どうやら寝ぼけていたようです。No1、No2は忘れてください。
大変失礼いたしました。

・・・で、ご質問を改めて確認すると
「StartStopのルーチンを実行中だけ、キー処理を有効にしたい」
ということと認識いたしました。

ご提示のコードでは、
ブックオープン時とシート表示時のイベントでキーを有効にして、シート非表示（＝別のシートに移動など）には無効になるようにしてあります。
その部分が、
＞因みに～
以下に挙げられている部分です。

ご質問の内容が、「当該シートだけで動作すれば良い」（＝他のシートではこの機能は利用しない）という意味と解釈してもよいのなら、まず、
・Private Sub Workbook_Open()
・Private Sub Worksheet_Activate()
・Private Sub Worksheet_Deactivate()
のイベント処理を無効にしてしまいます。

その上で、Sub StartStop()の最初の部分を

Sub StartStop()
If blnStart = True Then
blnStop = True
' キーを無効化
　Application.OnKey "{ENTER}"
　Application.OnKey "~"
　Application.OnKey "RIGHT"
Exit Sub
End If
' キーを有効化
　Call Enter_set1
　Call RIGHT_set1

に置き換えることで、処理が実行中のみ有効になると思います。

fujillin · Answer

No3です

＞ｷｰを押しても変化はなく、STARTしたままSTOPできなくなってしまいました。。。
STOPはきくようになったみたいですが、キーは有効にならないままですか？

No3の

' キーを有効化
　Call Enter_set1
　Call RIGHT_set1

の部分までちゃんと記述してありますでしょうか？
それで、キーが有効になるはずなのですけれど・・・？

Qchan1962 · Answer

横から失礼します

Do Until blnStop = True
Cells(2, 2) = Int((Timer - dblTimer) * 100) / 100
Sleep 1
DoEvents
Loop
実行中にキー操作による別VBA処理を実行させるのは無理ではないかと
タイマー表示（実行）部分を別インスタンスで実行すれば、、、と思いますが、やってみないと判りません。（例えば、javascriptでタイマーを作成しておきVBAから呼び出し表示、終了など）
ちなみにUserFormを作成してvbModeless実行しても、LabelやTextBoxに表示する場合もdo loopなどで更新しなければならないと思うのでキーによるVBA実行は出来ないと思います。（直接シートなどの操作は可能ですが）

＞時間観測中だけ有効にしたいです。
すでに回答にあるように、blnStartフラグを利用して設定、解除をすれば良いかと思います。

fujillin · Answer

No4です

No5様のヒントをいただいて思いつきましたが、
・現状ではStartStopを押した後は、キーが効かない
状態ということでしょうか？

うっかりと、現状はできていてるものを、「Stop後はキーを効かなくしたい」ものと解釈してしまっていました。
ご提示のコードがそれなりに複雑なので、テストをせずに、またまた失礼をいたしました。

現状で、キーが効かないのを効くようになさりたいのであれば、No5様の指摘の通りの原因だと想像されますので、
＞Do Until blnStop = True
＞Cells(2, 2) = Int((Timer - dblTimer) * 100) / 100
＞Sleep 1
＞DoEvents
＞Loop

のところを、OnTimeを利用して、VBAを開放してあげるような構成に変更すれば、多分、実現可能であろうと思います。

後で時間ができたときに試してみますね。
（今度はちゃんとテストいたします）

Qchan1962 · Answer

#5です
コードを読んだだけですので試していませんが、
Application.OnKeyでキー操作を設定していますが
Application.OnKey "{RIGHT}", "RGT"の場合、RIGHTでRGTプロシージャを実行しなさいと言う命令です。
すでにマクロが実行中の場合、VBEで実行ボタンがグレイアウトしている状態では、他のプロシージャを実行できないと思います。

blnStartフラグを利用して解除というのは、＃４さんの回答を参考に出来るのではと思いました。
例えば
Do Until blnStop = True
Cells(2, 2) = Int((Timer - dblTimer) * 100) / 100
Sleep 1
DoEvents
Loop
を
if blnStop = True then
Cells(2, 2) ="カウント中"
Call Enter_set1
Call RIGHT_set1
else
Cells(2, 2) ="待機中"
Application.OnKey "~"
Application.OnKey "RIGHT"
end if
とすれば、待機中のキー操作はデフォルトに戻っているのではと

Workbook_Openは必要ないかもしれませんが、Worksheet_Deactivateには、フラグ判別で処理を入れた方が良いと思います。

ちなみに、カウント中であってもキー操作の処理は行われていると思います
nui、RGTプロシージャが実行できないだけと思いますが、いかがでしょう
ちょっと複雑なので確認していませんが、 blnStart、blnStop　フラグの設定、判別が、、すっきり分かりませんので申し訳ないです。

＃５の
＞（例えば、javascriptでタイマーを作成しておきVBAから呼び出し表示、終了など）
2013あたりのExcelからシート上にWebBrowserオブジェクトを挿入できなくなったので代替え案としては不十分です

OnTimeに関しては1秒単位だったと記憶していますので表示的にどうかと言う疑問と表示するためにはやはり書き込む（更新）必要が出るのではないでしょうか。ステータスバー表示でも同様？
自分でもようわからんこと書き始めてしまいましたので、、参考まで

fujillin · Answer

No6です

何度も勘違いしまして、大変失礼いたしました。

OnTimeはNo7様の指摘にもあるように1秒単位なのですが、ご提示のタイマーは1/100秒単位で表示しようとしていますので、確かに若干問題があるかもしれないのですが…

試しに1秒単位で実行してみると、キーの制御も可能になりました。
そこで、0秒にして連続実行してみると、タイマーは1/100秒で動作するようになりますが、キーの割り込みを受け付けなくなり、ほぼ暴走状態になってしまいました。(汗）
（ということで、↑失敗でした！）

仕方がないので、ご提示とは別の方法になってしまいますが、キーを直接監視する方法に切り替えてみました。
すこしややこしいですが、以下の手順でお試しください。

1）コードの最初の
＞Option Explicit
の後に以下の1行を追加します。
（質問者様の環境は64ビット環境と推測して、書いております）
Declare PtrSafe Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Integer

2）ご質問文に最初にご提示の StartStop()のままのものに、以下のコードを追加します。
＞DoEvents
＞Loop
の間に2センテンス（改行しているので3行分）を追加します。
DoEvents
If GetAsyncKeyState(vbKeyRight) Then Call RGT
If GetAsyncKeyState(vbKeyReturn) Or _
　GetAsyncKeyState(vbKeySeparator) Then Call nui
Loop

以上で、ご質問の内容を実現できると思います。
（今回は実行確認しておりますので、多分、大丈夫だと思います。）

※ vbKeySeparatorはテンキーの側のEnterキーということになっていますが、私のキーボードではこちらもvbKeyReturnだったので、もしかすると不要かもしれませんが、一応、入れてあります。
※ 直接にキーを監視する方式に変えてしまったので、ご提示のOnkeyによる変更等は不要になります。
ご提示のうちの、
　Private Sub Workbook_Open()　～　End Sub
　Private Sub Worksheet_Activate()　～　End Sub
　Private Sub Worksheet_Deactivate()　～　End Sub
　Sub Enter_set1()　～　End Sub
　Sub RIGHT_set1()　～　End Sub
が不要になるという意味です。
（下の二つは残しておいても、呼び出されないだけなので、問題はありません。）

ただし、Onkeyの切り替えではキー操作が無効になっていたと思いますが、上記の方法ではキー操作は無効になりません。（例えば、「→」キーを押すと、選択セルが右に移動します）
それで問題なければ、そのままで宜しいと思います。
※ もしも、実行中に「→」キーなどだけは無反応（=本来のセル移動をしなくするという意味）にしたければ、No2の方法でOn、Offしてあげれば可能ですが、有効化の際に「何もしないサブルーチン」を呼び出すように変えておいたほうが無難です。
（キー操作による移動をしなくするだけなので…）

なお、お恥ずかしながら、No2にもタイポがありましたので、併せて訂正しておきます。
　誤：Application.OnKey "RIGHT"
　　　↓　↓
　正：Application.OnKey "{RIGHT}"

Qchan1962 · Answer

#5#7です。
＞Worksheet_Deactivateには、フラグ判別で処理とはどのような処理でしょうか？
Sub StartStop()でblnStartフラグにより制御されていて、Application.OnKeyで入力キーを設定している場合、他のシートを選択した場合などにApplication.OnKeyを戻す必要があるかなと考えました。（Stopをしなかった場合）
既に代替え案があるのでサンプルコードは割愛します。

＞理解できないところが多々あり
すみません。
おそらくDo~Loop処理が原因であるなら、このLoop処理を他のプログラムで実行させ　VBAからは、他のプログラムの起動、終了などだけを行えば、Do~Loopが不要になるのではと考えました。例で　javascript　としたのは、タイマーなどのソースコードは簡単に見つけられると思うのとシートにブラウザを（カウント表示の為）埋め込めたという記憶があった為です。

＃８さんの回答にあるように、Do~Loop内から実行コードを呼び出せば、上記の問題はなくなります。
すでにSleep Lib　WindowsAPIも使用しているようですし、GetAsyncKeyState Libをループ内で使用した方が、Application.OnKeyを設定する必要もなくなり良いかと思います。

少し気になるのは、全く実行コードを検証していないので不確かではありますが、VBAは遅いと言ってもループ、Call先プロシージャは高速と思います。
確かキーを押し続けてもGetAsyncKeyStateはtrueが返ってくるのでは、、なので複数回、Callが行われるのではないでしょうか、もしそうであれば、
GetAsyncKeyState　True＞＞False　で　Falseになるような追加フラグが必要な気がします。

fujillin · Answer

No8です

＞3～4回分、一気に値が入ってしまいました。何か問題があるのでしょうか？
＞すみませんが教えて下さい。
う～～ん。
No9様がすでにご指摘なさっている通りのことが起きていると思われますね。

Loopの中で重みを付けて、GetAsyncKeyStateを呼び出すタイミングを減らすとかすれば良いのでしょうかね。
でも、それだと感知機能そのものが落ちてしまうので、キーの検出だけはしておいて、処理ルーチンを呼び出すタイミングを調整した方が良さそうですね・・・

なんとなくのアイデアは思いつきましたが、今日は時間がないので、帰ってからやってみます。

Excel VBA ﾏｸﾛ実行中のみテンキー操作できるようにしたい

こんばんは

No1です。

No2です

No3です

横から失礼します

No4です

#5です

No6です

#5#7です。

No8です

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング