Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
7173 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【40686】Formが開いている間に別のシートが選択さ... リョウタ 06/7/20(木) 13:58 質問[未読]
【40691】Re:Formが開いている間に別のシートが選択... ハト 06/7/20(木) 14:42 回答[未読]
【40693】Re:Formが開いている間に別のシートが選... リョウタ 06/7/20(木) 15:29 質問[未読]
【40701】Re:Formが開いている間に別のシートが選... ハト 06/7/20(木) 16:48 回答[未読]
【40729】Re:Formが開いている間に別のシートが選... リョウタ 06/7/21(金) 16:08 お礼[未読]
【40695】Re:Formが開いている間に別のシートが選択... Kein 06/7/20(木) 15:36 回答[未読]
【40696】Re:Formが開いている間に別のシートが選... リョウタ 06/7/20(木) 16:06 回答[未読]
【40698】Re:Formが開いている間に別のシートが選... Kein 06/7/20(木) 16:34 発言[未読]
【40730】Re:Formが開いている間に別のシートが選... リョウタ 06/7/21(金) 16:19 お礼[未読]
【40686】Formが開いている間に別のシートが選択さ...
質問  リョウタ  - 06/7/20(木) 13:58 -
引用なし
パスワード
   初めて投稿させていただくものですが、質問の答えがお分かりの方はお教えいただければ幸いです。

現在 Form を利用した VBA アドインを作成しているのですが、Form の起動時にアクティブ シートの使用中の列と行を
ActiveSheet.UsedRange.Columns(.Columns.Count).Column
などとして取得しています。この Form には実行ボタンを貼り付けてあり、実行ボタンを押すと、アクティブシートのデータを元にプログラムが実行されます。

しかし、Form 起動時のアクティブシートに必ずしもプログラムを実行させたいデータが存在するとも限らないので、ユーザーが Form 起動後に別のシートを選択した場合、その際に Worksheet_Activate イベント (または別のシートが選択されたと分かるイベント) を発生させて Form 起動時と同じように使用中の列と行を取得したいと思っています。

Worksheet_Activate イベントプロシージャは、Excel の Visual Basic Editor を開いて
VBAProject
　- Microsoft Excel Objects
　　 - Sheet1 (Sheet1)
などのシートのコードエリアに直接書き込むことによってイベントを発生させることはできるようですが、開かれた xls ファイル内のシート群を元に dynamic にイベントを発生させることは可能なのでしょうか?

長々と分かりにくい説明になって申し訳ありませんが、お知恵を拝借させていただければ幸いです。よろしくお願いします。
【40691】Re:Formが開いている間に別のシートが選...
回答  ハト  - 06/7/20(木) 14:42 -
引用なし
パスワード
   おはようございます

▼リョウタ さん：
>初めて投稿させていただくものですが、質問の答えがお分かりの方はお教えいただければ幸いです。
>
>現在 Form を利用した VBA アドインを作成しているのですが、Form の起動時にアクティブ シートの使用中の列と行を
>ActiveSheet.UsedRange.Columns(.Columns.Count).Column
>などとして取得しています。この Form には実行ボタンを貼り付けてあり、実行ボタンを押すと、アクティブシートのデータを元にプログラムが実行されます。
>
>しかし、Form 起動時のアクティブシートに必ずしもプログラムを実行させたいデータが存在するとも限らないので、ユーザーが Form 起動後に別のシートを選択した場合、その際に Worksheet_Activate イベント (または別のシートが選択されたと分かるイベント) を発生させて Form 起動時と同じように使用中の列と行を取得したいと思っています。
>
>Worksheet_Activate イベントプロシージャは、Excel の Visual Basic Editor を開いて
>VBAProject
>　- Microsoft Excel Objects
>　　 - Sheet1 (Sheet1)
>などのシートのコードエリアに直接書き込むことによってイベントを発生させることはできるようですが、開かれた xls ファイル内のシート群を元に dynamic にイベントを発生させることは可能なのでしょうか?
>
>長々と分かりにくい説明になって申し訳ありませんが、お知恵を拝借させていただければ幸いです。よろしくお願いします。

VBAProject
　- Microsoft Excel Objects
　　 - ThisWorkbook

ThisWorkbookにて
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

これでイベントを取得できるはずです
【40693】Re:Formが開いている間に別のシートが選...
質問  リョウタ  - 06/7/20(木) 15:29 -
引用なし
パスワード
   >VBAProject
>　- Microsoft Excel Objects
>　　 - ThisWorkbook
>
>ThisWorkbookにて
>Private Sub Workbook_SheetActivate(ByVal Sh As Object)
>
>これでイベントを取得できるはずです

ハトさん、返信どうも有難うございました。

VBAProject (Book1)
　 - Microsoft Excel Objects
　　 - ThisWorkbook
のコードエリアに
------------------------------------------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

　 MsgBox (Sh.Name)

End Sub
------------------------------------------------------
などと入れて試してシート選択時にイベントが発生していることを確認しました。どうも有難うございました。

ただ、現在僕の方で VBA アドインを作成しているので、
VBAProject (<アドインファイル>.xla)
　 - Microsoft Excel Objects
　　 - ThisWorkbook
のコードエリアにコードを入力して、どのファイルを開いても同じようにシートが変更した時にイベントを発生させたいのですが、全く動きませんでした (見当外れなことをしていたらすいません)。つまり、もしユーザーの環境でこの xla を読み込んでいる設定になっているのならば、ユーザーがどの xls ファイルを開いてもこのイベントを発生させて必要なデータをアクティブシートから取得したいのです。

これって実現可能でしょうか?
【40695】Re:Formが開いている間に別のシートが選...
回答  Kein  - 06/7/20(木) 15:36 -
引用なし
パスワード
   >この Form には実行ボタンを貼り付けてあり、実行ボタンを押すと、
>アクティブシートのデータを元にプログラムが実行されます。
>しかし、Form 起動時のアクティブシートに必ずしもプログラムを実行させたい
>データが存在するとも限らないので
ならば実行ボタンを押した直後に、実行ボタンのマクロで全シートをループして
>プログラムを実行させたいデータ
が存在しているシートを Findメソッド 等で検索し、変数に入れる(例↓

Dim WS As Worksheet, Sh As Worksheet
Dim FR As Range

For Each WS In Worksheets
　 Set FR = WS.Cells.Find("XYZ", , xlValues)
　 If Not FR Is Nothing Then
　　　Set Sh = WS: Set FR = Nothing: Exit For
　 End If
Next
If Sh Is Nothing Then
　 MsgBox "処理対象のシートが見つかりません", 48
　 Exit Sub
End If

というコードにすれば良いと思います。なお、シートを変数に入れるのは、
いちいちアクティブにしなくても処理が出来るからです。
【40696】Re:Formが開いている間に別のシートが選...
回答  リョウタ  - 06/7/20(木) 16:06 -
引用なし
パスワード
   >ならば実行ボタンを押した直後に、実行ボタンのマクロで全シートをループして
Kein さん、返信どうも有難うございました。

すいません、説明が足りませんでした。現在作成中のアドインでは、Form 起動時にアクティブシート上で使用されている列数や行数を取得し、Form 上のドロップダウンリストなどのコンポーネントにそれらの取得したデータを反映しています。例えば、そのドロップダウンリストの一つに、ユーザーに A、B、C などの列 (カラム) を選択してもらうものがあるのですが、大概そこで選択される行はシート上で使われている一番右端の列なので、取得したアクティブシートの使用中の列数に基づいてデフォルト値を設定しています。また、A 列から使用されている一番右端の列の間に存在する全ての列をそのドロップダウンリストの選択肢に設定しているので、やはり別のシートが選択されたら、そのシートに基づいて Form 上のデータも更新したいと思っています。

こういった理由で現在シートが選択されたときにイベントを発生できないかご質問させていただいていますが、この条件で何か良い方法があればお教えください。よろしくお願いします。
【40698】Re:Formが開いている間に別のシートが選...
発言  Kein  - 06/7/20(木) 16:34 -
引用なし
パスワード
   そもそも「どのシートがアクティブであるか」ということに
依存するようなコードが間違っているのです。そうではなくて
「目的のデータが入力されているシートを探し、それを使う」
という発想に改めないといけません。そうすれば後は、タイミングの
問題だけになります。それは
>Form 起動時に
ということなら、Private Sub UserForm_Activate()
というタイトルのマクロで、先にUPしたようなコードを実行すれば
良いのです。当然、アクティブシートに相当するシートは Sh という
変数に格納されるため、その後は Sh で処理しますが、もし他のイベントでも
それを使う必要があれば、モジュールレベル変数として宣言しておけば
良いでしょう。即ちモジュールの先頭に Private Sh As Worksheet とします。
【40701】Re:Formが開いている間に別のシートが選...
回答  ハト  - 06/7/20(木) 16:48 -
引用なし
パスワード
   ▼リョウタ さん：
>>VBAProject
>>　- Microsoft Excel Objects
>>　　 - ThisWorkbook
>>
>>ThisWorkbookにて
>>Private Sub Workbook_SheetActivate(ByVal Sh As Object)
>>
>>これでイベントを取得できるはずです
>
>ハトさん、返信どうも有難うございました。
>
>VBAProject (Book1)
>　 - Microsoft Excel Objects
>　　 - ThisWorkbook
>のコードエリアに
>------------------------------------------------------
>Private Sub Workbook_SheetActivate(ByVal Sh As Object)
>
>　 MsgBox (Sh.Name)
>
>End Sub
>------------------------------------------------------
>などと入れて試してシート選択時にイベントが発生していることを確認しました。どうも有難うございました。
>
>ただ、現在僕の方で VBA アドインを作成しているので、
>VBAProject (<アドインファイル>.xla)
>　 - Microsoft Excel Objects
>　　 - ThisWorkbook
>のコードエリアにコードを入力して、どのファイルを開いても同じようにシートが変更した時にイベントを発生させたいのですが、全く動きませんでした (見当外れなことをしていたらすいません)。つまり、もしユーザーの環境でこの xla を読み込んでいる設定になっているのならば、ユーザーがどの xls ファイルを開いてもこのイベントを発生させて必要なデータをアクティブシートから取得したいのです。
>
>これって実現可能でしょうか?

アドインファイルに記述ですか
本当にどのブックでもイベントを発生させたいのでしたら
↓の様に記述を変更してみてください

Private WithEvents myApp As Application

Private Sub Workbook_Open()
　　
　　Set myApp = Application

End Sub


Private Sub myApp_SheetActivate(ByVal Sh As Object)

　　MsgBox Sh.Name
　
End Sub
【40729】Re:Formが開いている間に別のシートが選...
お礼  リョウタ  - 06/7/21(金) 16:08 -
引用なし
パスワード
   ハトさん、ご返事どうも有難うございました。

xla の ThisWorkbook のコードエリアにコードを入力して、全てのワークシート上でイベントを発生させてしまうのはさすがに問題があると気付きましたので、お教えいただいたコードを
------------------------------------------------------
Private WithEvents myApp As Application

Private Sub myApp_SheetActivate(ByVal Sh As Object)

　　MsgBox Sh.Name
　
End Sub

Private Sub UserForm_Initialize()
　
　 ...
　 ...
　 Set myApp = Application
　
End Sub
------------------------------------------------------
のように UserForm のコードエリアに入力すれば、自分の思惑通りに機能させることができました!!どうも有難うございました。これで、myApp_SheetActivate 内に UserForm_Initialize で呼び出しているいくつかの Function を実行すれば、シートが選択されるたびにアクティブシート上の必要なデータを Form に反映させられます!!

長い間保留にしていたので、とても助かりました。
また何かご質問させていただくことがあるかと思いますが、その時はまたよろしくお願い致します。

リョウタ
【40730】Re:Formが開いている間に別のシートが選...
お礼  リョウタ  - 06/7/21(金) 16:19 -
引用なし
パスワード
   Kein さん、ご指摘どうも有難うございました。

Kein さんとハトさんに今回お助けいただきましたが、まず自分の知識がお二人の域に達していないことが分かりました。

>そもそも「どのシートがアクティブであるか」ということに
>依存するようなコードが間違っているのです。そうではなくて
>「目的のデータが入力されているシートを探し、それを使う」
>という発想に改めないといけません。

上記のお言葉を参考にして今後は VBA や VB でのプログラミングをしていきたいと思います。また何かこのサイトでご質問させていただくことがあるかと思いますが、質問の回答以外でもプログラミング方法などお気付きの点がありましたらまたご教示お願い致します。

どうも有難うございました。

リョウタ
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
7173 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free