excel vba worksheet 簡単・解決！

スポンサードリンク

worksheet

excel vbaの「worksheet」を簡単に説明しました。

excelのvbaをマスターしましょう！
excel vba 簡単・解決！ Homeへ
worksheet

excelのvbaで WorkSheet は、作業シートのオブジェクトです。

まず、EXCELのプログラムは、特定のセルをアクティブにすることから開始されます。
Range("A1").Activate でA1がアクティブになります。
しかし、当然、そのセルがあるワークシートが最初にアクティブになっている必要があります。
したがって、多くのVBAプログラムは、次のように開始されます。

Sub Start_Prog()
　　Dim intI As Integer
　　WorkSheets("Sheet1").Activate ' ●どのシートのセルに対して操作することを明確にします。
　　Range("A1").Activate
intI = ActiveCell.Value
End Sub

１．対話的にワークシートを使用するためのイベント
　　このイベントは、VBAによって知ることが出来ます。
　　シートタブの上でマウスの右ボタンを押しメニューから「コードの表示」を選択します。
　　シートのコードウィンドウが開きます。

code

上図のように、左上のコンボボックスから　Worksheet を選び、右上のコンボボックスを開くと「イベント」の種類が分かります。
イベントを一つひとつ選んだ結果のVBAリストを次に示しました。
（１）ブック、ワークシート、グラフシート、または埋め込みグラフがアクティブになったときに発生します。
Private Sub Worksheet_Activate()

End Sub
（２）ワークシートの特定のセルをダブルクリックする前に起動されます。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
　　　Target.Row でセルの行番号、　Target.Columnで列番号を知ることが出来ます。
　　　したがって、操作したセルの値を取出すことや他のセルに代入することが出来、自動化に役立ちます。
End Sub
（３）特定のセル上でマウスの右ボタンを押す前にイベントが発生し、このサブルーチンを起動します。
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
　　　Cancel = True 　　'とすることでイベントをキャンセルできます。
End Sub
（４）ワークシートを再計算した場合発生します。　次の例は、再計算があったとき、セル範囲 A1:A100 を並べ替えます。
Private Sub Worksheet_Calculate()
With Worksheets(2)
　　　　　.Range("a1:a100").Sort Key1:=.Range("a1")
End With
End Sub
（５）ユーザーまたは外部リンクにより、ワークシートのセルが変更されるときに発生します。
Private Sub Worksheet_Change(ByVal Target As Range)
　　　Msgbox Target.Address ' 変更されたセルの絶対アドレスが表示されます。
End Sub
（６）シートが非アクティブになったときに発生します。
Private Sub Worksheet_Deactivate()
　　　MsgBox " This sheet was Deactive. "
End Sub
（７）ワークシートのハイパーリンクをクリックすると発生します。
　　次の使用例は、作業中のワークシートから表示されたすべてのリンクの一覧または履歴を保存します。
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With UserForm1
.ListBox1.AddItem Target.Address
.Show
End With
End Sub
（８）ピボットテーブルレポートがワークシート上で更新された後で発生します。
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
　　MsgBox "ピボットテーブル接続が更新されました。"
End Sub
（９）ワークシートで選択範囲を変更したときに発生します。
　　次の使用例は、選択範囲がウィンドウの左上隅になるまで、ブックのウィンドウをスクロールします。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveWindow
.ScrollRow = Target.Row
.ScrollColumn = Target.Column
End With
End Sub

２．ワークシートをアクティブにする方法
　　Worksheets(2).Activate 左から２番目のワークシートがアクティブになります。
　　複数のシートを同時に選択する場合、次のように２つのシートを選択できます。
　　Worksheets(2).Select
　　Worksheets(3).Select False

３．前後のワークシートを選択する方法
　　Worksheets(2).Previous.Select １番左のワークシートを選択できます。
　　Worksheets(2).Next.Select ２番の左のワークシートを選択できます。　左から３番目のシートが選択されます。

４．ワークシートの名称を取得・設定する方法
　Worksheets(1).Name = "TEST" として、左端のワークシートの名称を設定できます。
　For Each objW In Worksheets
　　　Msgbox objW.Name
　Next
　このVBAプログラムにより、アクティブBookのシート名を全て知ることができます。

５．ブックに含まれるワークシートの数を知る方法
　　Msgbox Workbooks(1).Worksheets.Count　により１番目になっているブックのワークシート数を返します。

【使用例１】　「繰り返し入力を簡単にする方法」

次の例は、A1:A15に入力した文字列を、「ダブルクリック」すると、D1セルに転記し、カーソルをB1に移動させます。
（１）シートのタブ上でマウスの右ボタンを押します。
（２）メニュから「コードを表示」を選びます。
（３）Worksheetコードウィンドウが開きます。
（４）そのコードウィンドウの左上(General)▼ボタンを押し、コンボボックスからWorksheetを選択します。
（５）右上のコンボボックスから　BeforeDoubleClick を選びます。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub
上記のプログラムがVBAにより自動的に作成されます。
（６）このコードの中に次の赤い部分のプログラムコードをコピーします。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Cells.Column <> 1 Then Exit Sub
Cells(1,4).Value = Target.Value
Cells(1,2).Select
End Sub

【使用例２】「日付を自動的に入力する方法」

次の例は、入力した同じ行に日付を自動的に入力する方法です。
入力は、A2:A100にします。
日付は、データをA2:A100に入力したとき、B列に自動的に入力されます。
入力されない場合、日付は表示されません。　しかし、スペースが入力されると、表示されます。
※　使用例１と同様に　赤い部分のコードをWorksheet_Changeイベントルーチンにコピーします。

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If

End Sub

【使用例３】　入力規則のリストへ入力データを自動的に追加・更新」

入力規則の基になっているリストを自動的に更新する方法
この例は、A1:A5にリストが入力してある、と仮定します。
更新用データは、D1セルに入力します。
●この例は、EXCELの自動再計算とイベントが使用され、VBAのすばらしさを体験できます。
（１）Sheet1のA1:A5にリストのデータがあると仮定します。
A-001
A-002
A-003
A-004
A-005
（２）「データ」－「入力規則」からA1:A5に名前をつけます。　　　MyNames、としました。
（３）参照に次の数式を入力します。
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
この式の意味は、$A$1セルを基準として、０行０列シフトしたセルに、つまり、基準の$A$1を基に、
高さがCOUNTA(Sheet1!$A$A)つまり、A列のセル数
幅が１
のオフセット参照を設定します。
(●この参照は、「自動再計算」されます。)
（４）D1セルに入力規則を定義します。
　　１）データの種類は、「リスト」です。
　　２）D1の元の値は、=MyNames　を入力します。
　　３）エラーメッセージが出ないように、「エラーメッセージ」タブを選択し、「エラーメッセージを表示」のチェックを外します。
　　４）OKボタンを押します。
（５）ワークシートタブの上にマウスを乗せ、右ボタンを押し、メニューから「コード表示」を選択します。
（６）コードウィンドウ左上のコンボボックス(General)▼ボタンを押し　Worksheetを選びます。
（７）コードウィンドウ右上のコンボボックスから　Chagneを選択します。
Worsheet_ChangeイベントサブルーチンがVBAにより自動的に作成されます。
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
（８）上記のサブルーティンに次のVBAコードをコピー＆ペーストします。
Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$D$1" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target
End If
End If
End If

End Sub
（９）　D1へ追加する新しいデータを入力し改行します。
（１０）　MsgBoxから次のメッセージが表示されます。

EVENT