午後わてんのブログ

ベランダ菜園とWindows用アプリ作成(WPFとC#)

WithEvents練習、アクティブセルやシート、ブックの変更時に動作させたいものがあるとき

エクセルExcel2007 #ソフトウェア エクセルVBA
アクティブセルが変更された時を取得するにはWorksheetイベントに用意されているSelectionChangeを使うのが便利
だけどこれをワークシートごと全てに書くのはめんどくさいし
ブックが変更された時も取得したいときはムリすぎる
これを解決するWithEvents
 
Userformのテキストボックスにクリックしたセル(アクティブセル)の値を表示する
イメージ 1
こんな感じの
 
デザイン画面

f:id:gogowaten:20191018130041p:plain

ユーザーフォームにテキストボックスを追加しただけ
 
プロシージャ

f:id:gogowaten:20191018130052p:plain

 
Private WithEvents mySheetE As Worksheet
 
Private Sub UserForm_Initialize()
    Set mySheetE = ActiveSheet
End Sub
 
Private Sub mySheetE_SelectionChange(ByVal Target As Range)
    Me.TextBox1.Value = ActiveCell.Value
End Sub
ユーザーフォームの中に書いたのはこれだけ
1行目の
Private WithEvents mySheetE As Worksheet
が重要
Worksheet型の変数を宣言しているところだけどイベント付きで宣言している
普通なら
Private mySheetE As Worksheet
だけど
Private WithEvents mySheetE As Worksheet
でWihtEventsを変数名の前につけるとイベントつきになるみたいで
こうした変数を宣言すると

f:id:gogowaten:20191018130106p:plain

他のコントロールに混じってオブジェクトの一覧に出てくる
これを選択すると今度は
 

f:id:gogowaten:20191018130118p:plain

右上の方に用意されているイベントの一覧が表示されるようになる
 
イベントの一覧から使いたいものを選択すると

f:id:gogowaten:20191018130130p:plain

必要な物が書き込まれるので
このイベントが起きた時に実行したいことを書けばいい状態になる
 
今回実行したいのはアクティブセルの値をテキストボックスに表示するだから
Me.TextBox1.Value = ActiveCell.Value

f:id:gogowaten:20191018130142p:plain

イベントのところはOK
 
次は宣言したイベントつきのWorksheetが変数mySheetEにActiveSheetを入れる
Set mySheetE = ActiveSheet
これをいつ実行するか
ユーザーフォームを起動するときにしてみた

f:id:gogowaten:20191018130152p:plain

起動の時のイベントはInitializeだから
Userformを選んでからInitializeを選ぶと赤枠の文章が入るので
ここに
Set mySheetE = ActiveSheet

f:id:gogowaten:20191018130206p:plain

これでユーザーフォームの方はできた
次はこのユーザーフォームを表示するプロシージャ
これは標準モジュールに書く
 

f:id:gogowaten:20191018130216p:plain

Sub UFShowユーザーフォーム表示()
    UserForm1.Show vbModeless
End Sub
UserForm1.Show vbModeless
この vbModelessを書いておくとセルとかシートをクリックできる状態で
ユーザーフォームが表示される

これで全部準備ができたので、あとはユーザーフォームを表示させる
Sub UFShowユーザーフォーム表示()
を実行するだけ
イメージ 12
ただこれだけだとシートを変更したりブックを変更したりすると機能しなくなってしまう
これはイベントつき変数のmySheetEに入れたのはユーザーフォーム起動時のActiveSheetで、それ以外のシートは無関係だから
なのでシートを変更した時、新たにActiveSheetになったシートをmySheetEに入れ直してあげればいい
そうすると今度はシートを変更したっていうのを取得する必要がある
これもDeactiveっていうWorksheet用のイベントが用意されていて
これは非アクティブになった時に発生するもの
これでシート変更した瞬間がわかる
同じようにブックを変更した時にも対応するには
WorkBook型変数にもDeactivateイベントが用意されているので同じように使える
 
 
シートやブックの変更に対応したのがこれ
Private WithEvents mySheetE As Worksheet
Private WithEvents myBook As Workbook

'ブックが変更された時
Private Sub myBook_Deactivate()
    Set mySheetE = ActiveSheet
    Set myBook = ActiveWorkbook
    Call CellValue2TextBoxセルの値をテキストボックスに表示
End Sub

'シートが変更された時
Private Sub mySheetE_Deactivate()
    Set mySheetE = ActiveSheet
    Call CellValue2TextBoxセルの値をテキストボックスに表示
End Sub

'セルが変更された時
Private Sub mySheetE_SelectionChange(ByVal Target As Range)
    Call CellValue2TextBoxセルの値をテキストボックスに表示
End Sub

Private Sub UserForm_Initialize()
    Set mySheetE = ActiveSheet
    Set myBook = ActiveWorkbook
    Call CellValue2TextBoxセルの値をテキストボックスに表示
End Sub

Sub CellValue2TextBoxセルの値をテキストボックスに表示()
    Me.TextBox1.Value = ActiveCell.Value
End Sub
 

f:id:gogowaten:20191018130234p:plain

Deactivateイベントの時にその時のActiveSheetやActiveBookを入れなおしている

 
以前はクラスモジュールを使って遠回りしていたけどこれだけでよかった
WithEventsって便利だなあ

これを使って作ったのが
エクセル2007アドイン作成その27、色の並べ替えと、パレットの色の枠の表示変更 ( ソフトウェア ) - 午後わてんのブログ - Yahoo!ブログ
これの午後のパレットのアクティブセルの色と同じ色がパレットにあったら枠を表示するっていう動作
セルやシート、ブックが変更された時に動作させたいものがあるって時に使えるんだと思う

ダウンロード