ＥＸＣＥＬでのＶＢについて

質問

ＥＸＣＥＬでＶＢのプログラムを作成していますが、２点ほどわからないことがありまして困っています。
１．ＥＸＣＥＬ等でデータを保存するとき、同じファイルがあれば、上書き確認のメッセージボックスが出ます。ＶＢのＯｐｅｎ文で保存 するとき、このメッッセージボックスを出したい。
２．コマンドボタンの機能をファンクションキー（たとえばＦ１）に割り付けたい。
以上、２点について、よろしくご教示ください。

回答No.11

KenKen_SP · ベストアンサー

Excel VBA ですよね？

このスレに記載された手順を自分で再度試してみましたが、動きましたよ。

・clsKeyUpEventHook の名前はあっていますか？
・Excel のバージョンは 2000 以降ですか？

お礼

やっと動作しました。
これからプログラムの中に組み込みます。
大変お手数をおかけしました。ありごうとうございました。

補足

動作しなかった原因は、名前を変更するところでした。Cassのプロパティが表示されていなくて、Class1上で右クリックで出てくるVBAProjectのプロパティに名前を入れたためでした。初歩的なミスでした。
これから、もっと勉強したいと思います。

回答No.10

KenKen_SP

こんばんは。

> 私のPRGではコマンドボタンをsheetに貼り付けていますが、ユーザー
> フォームとの関係がわかりません。

質問1.　PRG　は　プログラム？

質問2.　コマンドボタンを Sheet に貼り付け...ってのが気になりますが、
　　　　あなたの言っているユーザーフォームとは、シート上に貼り付け
　　　　た、ボタンやテキストボックスのことを意味してますか？

------＞私は、#1 ～ #3 への補足欄等を拝見して、それ以降の回答を書い
　　　　ています。もし、シート上に貼り付けたモノをユーザーフォームと
　　　　表現されているのであれば、根本的に回答内容が変わります。

　　　　私が前提にしているのは、VBE メニュー[挿入]-[ユーザーフォーム]
　　　　のことです。

補足

質問1.　PRG　は　プログラム？
そうです。プログラムのことです。
質問2.　コマンドボタンを Sheet に貼り付け...ってのが気になりますが、あなたの言っているユーザーフォームとは、シート上に貼り付け
た、ボタンやテキストボックスのことを意味してますか？
文書の中で使っているユーザーフォームとはVBE メニュー[挿入]-[ユーザーフォーム]で、出てくるユーザーフォームを意味しています。
今は、N88BASICからVBへの移行にトライしているところでして、自分で作ったプログラムではユーザーフォームを使用しておりませんでした。（とゆうか、使い方がわかりません）
以上、よろしくお願いいたします。

回答No.9

KenKen_SP

> VBE 1.で貼り付けたクラスモジュールのプロパティー欄でオブジェクト名を
> clsclsKeyUpEventHook に変更

これは、#8 で書きましたように clsKeyUpEventHook です。cls の部分が２重で
余計でした。

【手順】---------------------------------------------------------------

1.　[Alt]+[F11] で Visual Basic Editor （以下 VBE ）が起動
2.　VBE メニューで [挿入]-[クラスモジュール] クリック
3.　2. で開いたスペースに #5 の Option Explicit ～ 最後までをコピー＆ペースト
4.　VBE 画面左下に プロパティー が表示されています。そこに

　　(オブジェクト名)

　　という欄があります。現在は、 Class1 のような感じの名前になっています。
　　これを clsKeyUpEventHook に書き換えます。

5.　次にもう一度 2. の操作を行い、クラスモジュールを挿入します。
6.　5.で開いたスペースに #6 の Option Explicit ～ 最後までをコピー＆ペースト
7.　同じく名前を clsKeyUpEventHookChild に変更します。
----------------------------------------------------------------------

これで準備はできました。あとは、#8 のサンプルをみて、ご自分のフォーム
に必要なコードを書いて下さい。

正直クラスを使った方法は VBA 初心者には難しいと思います。しかし、ご希望
の動作を実装する場合、Excel VBA ではどうしてもクラスが必須なんですよ、、、

慣れるとなんてことない作業です。できるまで、サポートしますので、頑張って
下さい。

また、クラスは再利用可能ですから、今回にのみに限らず、他のフォームでも
使いたい場合、上記の作業を行えば、どんなフォームでも対応できます。

お礼

ご丁寧な説明ありがとうございます。
VBについて勉強中なので、この方法でがんばってみたいと思いますのでよろしくお願いいたします。

補足

わからない点があります。
ユーザーフォームから実行すると
'カスタムイベント使用を宣言
Private WithEvents Evt As clsKeyUpEventHook '※必須
が　コンパイルエラー
プロジェクトでなくユーザ定義型を指定してください
のエラーがでています。
対処法をお願いいたします。
私のPRGではコマンドボタンをsheetに貼り付けていますが、ユーザーフォームとの関係がわかりません。
ユーザーフォーム、標準モジュール、クラスモジュールとの関係がわかりません。詳しく書いた本などを紹介していただけませんでしょうか。
以上、よろしくお願いいたします。

回答No.8

KenKen_SP

#5 でタイプミスがありました。すみません。。。

clsclsKeyUpEventHook は clsKeyUpEventHook の誤りです。

それから、#1 の補足欄引用です。

> 2.については動作確認までしました。

失礼ですが、動作したんですか？

前も指摘しましたが、Userform の KeyDown、KeyUp、KeyPress の
イベントは、Userform 内にフォーカスを受け取るコントロールが
あると発生しないハズですよ、、

　--＞イベントは”フォーカスのある”コントロール（オブジェクト）
　　　で発生する

Userform に Label コントロールしか無いなら大丈夫ですが、、
それでは Userform を使う意味が、、、

試しに Userform に TextBox を1つ配置してから、

Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

　　If KeyCode = vbKeyF1 Then
　　　　MsgBox "Keydown [F1]"
　　End If

End Sub

を試してみて下さい。

補足

> 2.については動作確認までしました。
の意味は、プログラムを貼り付けて実行したところ、動作したことです。
自分のプログラムに組み込んでの動作確認はこれからです。
組み込み法を考えているところです。

回答No.7

KenKen_SP

その３

ご自分で作成されたフォームに下記の例をみて、追加書きして下さい。
RaiseEvent を使っていますので、Excel2000以上です。

応用するとその他のイベントも横取りできます。

' カスタムイベント使用を宣言
Private WithEvents Evt As clsKeyUpEventHook '※必須

Private Sub UserForm_Initialize()

　　Set Evt = New clsKeyUpEventHook '※必須 Initialize イベントで
　　Evt.BindUserform = Me　　　　　 '※必須 Initialize イベントで

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

　　Set Evt = Nothing '※必須 一応念のため QueryClose で

End Sub

' ※必須：MSForms のコントロールならフォーム内のどこにフォーカスが
'　　　　 があっても以下の KeyUp イベントが発生します。
'
Private Sub Evt_KeyUp( _
　　ByVal ObjectName As String, _
　　ByVal KeyCode As Integer, _
　　ByVal Shift As Integer)

　　Select Case KeyCode
　　　　Case Is = vbKeyF1
　　　　　　Call CommandButton1_Click　' <-- コマンドボタン1をクリック
　　　　Case Is = vbKeyF5
　　　　　　MsgBox "F5 キーが押されました"
　　　　Case Else
　　End Select

End Sub

回答No.6

KenKen_SP

その２

1.　VBE [挿入]-[クラスモジュール] で下記コードをコピペ。
2.　VBE 1.で貼り付けたクラスモジュールのプロパティー欄でオブジェクト名を
　clsKeyUpEventHookChild に変更

Option Explicit

Private mclsCreater　　As clsKeyUpEventHook
Private mstrObjectName As String

Private WithEvents FRM As MSForms.UserForm
Private WithEvents TXT As MSForms.TextBox
Private WithEvents CBO As MSForms.ComboBox
Private WithEvents LST As MSForms.ListBox
Private WithEvents CHK As MSForms.CheckBox
Private WithEvents CMB As MSForms.CommandButton
Private WithEvents OPT As MSForms.OptionButton
Private WithEvents TGL As MSForms.ToggleButton
Private WithEvents SPN As MSForms.SpinButton
Private WithEvents TBS As MSForms.TabStrip
Private WithEvents MTP As MSForms.MultiPage
Private WithEvents SCR As MSForms.ScrollBar

Private Sub Class_Terminate()
　　Set mclsCreater = Nothing
　　Set TXT = Nothing
　　Set CBO = Nothing
　　Set LST = Nothing
　　Set CHK = Nothing
　　Set CMB = Nothing
　　Set OPT = Nothing
　　Set TGL = Nothing
　　Set SPN = Nothing
　　Set TBS = Nothing
　　Set MTP = Nothing
　　Set SCR = Nothing
　　Set FRM = Nothing
End Sub

Property Let Creater(ByRef NewCreater As clsKeyUpEventHook)
　　Set mclsCreater = NewCreater
End Property

Property Let BindControl(ByRef Ctrl As Object)
　　Select Case TypeName(Ctrl)
　　　　Case Is = "TextBox":　　　 Set TXT = Ctrl
　　　　Case Is = "ComboBox":　　　Set CBO = Ctrl
　　　　Case Is = "ListBox":　　　 Set LST = Ctrl
　　　　Case Is = "CheckBox":　　　Set CHK = Ctrl
　　　　Case Is = "CommandButton": Set CMB = Ctrl
　　　　Case Is = "OptionButton":　Set OPT = Ctrl
　　　　Case Is = "ToggleButton":　Set TGL = Ctrl
　　　　Case Is = "SpinButton":　　Set SPN = Ctrl
　　　　Case Is = "TabStrip":　　　Set TBS = Ctrl
　　　　Case Is = "MultiPage":　　 Set MTP = Ctrl
　　　　Case Is = "ScrollBar":　　 Set SCR = Ctrl
　　　　Case Is = "Userform":　　　Set FRM = Ctrl
　　　　Case Else
　　End Select
End Property

Property Let Name(ByRef NewName As String)
　　mstrObjectName = NewName
End Property

Private Sub TXT_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub CBO_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub LST_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub CHK_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub CMB_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub OPT_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub TGL_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub SPN_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub TBS_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub MTP_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub SCR_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub
Private Sub FRM_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Call mclsCreater.RaiseKeyUp(mstrObjectName, KeyCode, Shift)
End Sub

補足

その１とは別のClassを開いてそこに、その２を貼り付けるのでよいのでしょうか。
ご教示下さい。

回答No.5

KenKen_SP

> 具体的にはどのようにすればよいでしょうか。

クラスモジュールでイベントフックの処理を行う...については、Excel VBA
では結構難しいと思います。できるだけ容易に使えるよう、ユーザーフォーム側
の記述が最小限になるように書いてみました。

長いのでソースコードを３分割します。

1.　VBE [挿入]-[クラスモジュール] で下記コードをコピペ。
2.　VBE 1.で貼り付けたクラスモジュールのプロパティー欄でオブジェクト名を
　　clsclsKeyUpEventHook に変更

Option Explicit

' カスタムイベント定義
Public Event KeyUp(ByVal ObjectName As String, _
　　　　　　　　　 ByVal KeyCode As Integer, _
　　　　　　　　　 ByVal Shift As Integer)

Private mclsK() As clsKeyUpEventHookChild

Private Sub Class_Terminate()
　　Dim i As Long
　　For i = 0 To UBound(mclsK)
　　　　Set mclsK(i) = Nothing
　　Next i
End Sub

Property Let BindUserform(ByRef objForm As Object)

　　Dim i　　　As Long
　　Dim lngCnt As Long
　　Dim Ctrl　 As MSForms.Control

　　If Not TypeOf objForm Is MSForms.UserForm Then
　　　　Err.Raise 13
　　　　Exit Property
　　Else
　　　　For Each Ctrl In objForm.Controls
　　　　　　' 除外コントロール数
　　　　　　If Not TypeName(Ctrl) <> "Label" Then
　　　　　　　　lngCnt = lngCnt + 1
　　　　　　End If
　　　　Next Ctrl
　　　　ReDim mclsK(objForm.Controls.Count - lngCnt - 1)
　　　　For Each Ctrl In objForm.Controls
　　　　　　' 除外コントロール判定
　　　　　　If TypeName(Ctrl) <> "Label" Then
　　　　　　　　Set mclsK(i) = New clsKeyUpEventHookChild
　　　　　　　　With mclsK(i)
　　　　　　　　　　.Name = Ctrl.Name
　　　　　　　　　　.Creater = Me
　　　　　　　　　　.BindControl = Ctrl
　　　　　　　　End With
　　　　　　　　i = i + 1
　　　　　　End If
　　　　Next Ctrl
　　End If

End Property

' カスタムイベント発生
Public Sub RaiseKeyUp(ByRef ObjectName As String, _
　　　　　　　　　　　ByVal KeyCode As Integer, _
　　　　　　　　　　　ByVal Shift As Integer)
　　RaiseEvent KeyUp(ObjectName, KeyCode, Shift)
End Sub

お礼

丁寧に教えていただきありがとうございます。
早速、会社で試してみたいと思います。

補足

初心者なのでわからない点があります。
VBE 1.で貼り付けたクラスモジュールのプロパティー欄でオブジェクト名をclsclsKeyUpEventHook に変更
の具体的な方法をご教示下さい。
よろしくお願いいたします。

回答No.4

KenKen_SP

> 具体的にはどのようにすればよいでしょうか。

質問２について、簡単にやるなら下記のようなコードです。

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　If KeyCode = vbKeyF1 Then
　　　　MsgBox "F1キーが押されました"
　　End If
End Sub

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　If KeyCode = vbKeyF1 Then
　　　　MsgBox "F1キーが押されました"
　　End If
End Sub

・・・・・

コントロール数が少ない場合は良いのですが、フォーム内の全てのコントロール
でイベントを書かなければならないので、コントロール数が多くなると同じ
ようなコードが「ずら～」っと長々続くことになるんです。

100のコントロールが在った場合、100個書くということですから。

お礼

丁寧に教えていただきありがとうございます。
早速、会社で試してみたいと思います。

回答No.3

KenKen_SP

Form_KeyDown イベントはフォーム内にフォーカスを受け取るコントロール、
例えばテキストボックスなどのコントロールが１つでもあると、発生しない
ですね、、、

この場合、ひとつひとつのコントロールでイベント処理するか、クラス
モジュールでイベントフックの処理を行うか、、、

もっと簡単にできるのかな？

お礼

お礼が遅くなってすみません。家のPCがクラッシュしてしまいました。
やっと回復しました。ありがとうございます。

補足

VBはまだ初心者なので内容が理解できません。具体的にはどのようにすればよいでしょうか。ご教示お願いいたします。

回答No.2

noname#22222

ちくっと、横から...

Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Select Case KeyCode
　　　　Case vbKeyF1
　　　　　　MsgBox "F1キーが押されました"
　　　　　　KeyCode = 0
　　End Select
End Sub

F1キーをキャンセルコードを加えればいいです。

お礼

お礼が遅くなってすみません。家のPCがクラッシュしてしまいました。
やっと回復しました。今日、会社で試してみます。ありがとうございます。

補足

2.については動作確認までしました。F1で”F1キーが押された”が出ますので組み込みを考えています。
”F1キーをキャンセルコードを加えればいいです。”の意味は
KeyCode = 0
のことですか？（VBはまだ初心者なので用語との意味がまだよくわかりません）

回答No.1

imogasi

１．に付いて
Ｏｐｅｎする前にＩｎｐｕｔｂｏｘででもファイル名を入れさせて、そのファイル名につき
Sub test01()
a = Dir("book18.xls")
If a = "" Then
MsgBox "ファイル無し"
End If
End Sub
のようにするのはどうでしょうか。
２．に付いて
http://kw.allabout.co.jp/glossary/g_pc/w006969.htm
http://allabout.co.jp/study/pcbasic/closeup/CU20030214A/index.htm
ＯＳやアプリでそのファンクションキーに機能を割り当てて入るケースが多いが、それを抑止して、自分の決めた昨日を割り当てるのですか。ショートカットキーの利用ぐらいでとめておくのはいかがでしょうか。
アクセスではオートキーマクロ
http://www.moug.net/tech/acopr/0110008.htm
http://www.tsware.jp/tips/tips_165.htm
エクセルユーザーフォームで
Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyF1
MsgBox "F1キーが押された"
　 End Select
End Sub
を実行すると、ｆ１を押すと「F1キーが押された」と出ます。
しかし応答しないで、再度Ｆ１を押すとヘルプ画面が出ました。
あとはよろしく。

お礼

お礼が遅くなってすみません。家のPCがクラッシュしてしまいました。
やっと回復しました。今日、会社で試してみます。ありがとうございます。

補足

1.についてはうまくいくようになりました。
2.については動作確認までしました。F1で”F1キーが押された”が出ますので組み込みを考えています。