Excelのこととか色々

マクロをどんな Excel ファイルにも実行できるように設定して、3秒かかる操作も1秒で完了。あといろいろ書いてみます・・・

【Excel/VBA のこと09...先頭シートに移動/各シートに移動】

【Excel/VBA のこと 09】

f:id:tuna-kichi:20200223230704p:plain

個人的に Excel を使用している分には、シートはあまり増やしませんが、

仕事ではシートの数が20以上になることがあります。

 

シート数が増えて一画面で表示しきれなくなった場合、左下の矢印(◀▶)が  

有効になりますが、小さいし、クリックしている間に

ポインタがずれてしまうことがあります。

 

マクロを作って、シート移動の大きいボタンをリボンに登録しましょう。

 

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

 

先頭シートに移動

マクロ

先頭シートに移動するマクロはこれです。

****************************************************************

Sub 先頭シートに移動

  Sheets(1).Select

End

****************************************************************

おぉ 改めてみると、とても簡単なコードですね。

シートは左から順番に番号が付けられていきます。

 

シート名が"Sheet39" であっても

内部では、一番左にあるシートが必ず "1" になります。

これをリボンに登録しましょう。

 

1.リボンの空白の所で右クリック

2.【リボンのユーザー設定】をクリック

3.左側の窓の【コマンド選択】から【マクロ】をクリック

4.作成した、登録したいマクロををクリック

5.右側の窓で登録したいマクロを表示させる場所をクリック
  (タブ、グループを選択する)

6.真ん中にある【追加】をクリック

7.右側の窓の登録したマクロを選択して、【名前を変更】

 

キャプチャ付きのもう少し詳しい手順は【Excelのこと02】で確認してください。

 

www.tuna-kichi.com

 


f:id:tuna-kichi:20200321094126p:plain

こんな感じに登録しました。

各シートに移動

移動方法①

各シートに移動する方法ですが、これが一番早くて、楽かな。

f:id:tuna-kichi:20200321101116p:plain

 

画面左下の所を右クリックすると、シート一覧のウインドウが表示されます。

ここから目的のシートを選択してからOKをクリック、または、ダブルクリック

するとそのシートを表示してくれます。

 

最近この機能を見つけてびっくりしました。

 

移動方法②

ショートカットキー

Ctrl + PageUp/Down

 

Ctrlキーを押しながら PageDown キーを押すたびに、

右側のシートを表示してくれます。

PageUpキーは左に移動します。

 

これなら、目線はそのままなので、内容を確認しながらシートを選択できますよ。

 

移動方法③(マクロ)

移動方法①を見つける前に、ほぼ同じ画面をマクロで作成して

シートを選択してました。

フォームを作ってシート名一覧を表示させます。

 

今まで紹介してきたマクロより少し複雑ですが、

備忘録も兼ねて、簡単に書いておきます。

 

フォームの追加

f:id:tuna-kichi:20200321111942p:plain

 

ユーザーフォームを追加すると、下の様な画面が表示されます。

 

f:id:tuna-kichi:20200501165351p:plain

 

「リストボックス」と「コマンドボタン」2つを適当に配置

f:id:tuna-kichi:20200321113945p:plain

 

マクロ

f:id:tuna-kichi:20200321161535p:plain

まずは簡単なところから。

フォームで追加した「キャンセル」のコマンドボタンをダブルクリックすると、

「キャンセル」をクリックしたときの VBA を記述するエディタが開きます。

そこに、以下のマクロを記述。

****************************************************************

Private Sub キャンセル

  End

End

****************************************************************

「End」この一言でプログラムが終了します。

ちょーかんたん!

 

【 リストボックスの初期化】

フォームを初期化の方が正しい言い方かもしれません。

フォームを起動させるときに、リストボックスに書かれていることを

初期化して一覧表を再作成して表示させます。

 

フォームの空いているスペースをダブルクリックして

フォームの動作のマクロを記述する VBE を開きます。

 f:id:tuna-kichi:20200321181927p:plain

 

VBE が開いたら UserFormの「Initialize」を追加して、

マクロを記述するところを追加します。

「UserForm_Click()」のところは不要なので削除しましょう。

 

Initialize・・・初期化 

 

f:id:tuna-kichi:20200321180439p:plain

 

そして、マクロを記述。

************************************************************************

Private Sub UserForm_Initialize()

  Dim SheetCnt As Long   'シートの枚数を格納する
  Dim SheetName As String  'シート名を格納する
  Dim i As Long       'for で使う

  SheetCnt = Sheets.Count  'シートの枚数を数える

  For i = 1 To SheetCnt    'forでシートの枚数分繰り返す
     'SheetName にSheets(1)から順番にシート名を格納する

    SheetName = Sheets(i).Name


    'フォームに作成したリストボックスに追加していく
    lbxShName.AddItem SheetName 

  Next i

End Sub

**************************************************************************

 

マクロが実行されるのは青字のところ。

シングルクオーテーションの緑字は実行されません。

 

やっていることは、

シート名を取得して、フォームに用意したリストボックスに記述していく。

シートが何枚あるか最初に数えてあるので、その回数だけforで繰り返します。

AddItem でリストに追加します。 

 

f:id:tuna-kichi:20200321161757p:plain

 

**************************************************************************
Private Sub cmdMove_Click()

  ①Dim ShName As String

  ②ShName = lbxShName.Text

  ③Sheets(ShName).Select

  ④Unload frmMoveSheet

End Sub

**************************************************************************

①Dim ShName As String・・・ShName に文字列を格納すると定義

②左 ShName・・・リストボックスの選択されたテキストを格納

②右 lbxShName.Text・・・リストボックスで選択したシート名

③Sheets(ShName).Select・・・選択したシートを表示

④Unload frmMoveSheet・・・フォームを閉じる

 

ここまでがフォームを開いてからの動きです。

 

【フォームを起動】

フォームを起動させるには標準モジュールに

フォームを起動するマクロを記述する必要があります。

 

それがこれ。

*************************************************************

Sub シート選択()

  frmMoveSheet.Show

End Sub

************************************************************* 

 フォームの名前は プロパティウインドウで設定します。

f:id:tuna-kichi:20200321235208p:plain

 

これで、フォームの作成とマクロの記述は完成。

あとは、リボンに登録です。

f:id:tuna-kichi:20200321235556p:plain

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

2020.05.01 追加 

リストボックスに表示されたシート名を、ダブルクリックすると、

そのシートを表示することが出来ました。

 

VBE上で、リストボックスをダブルクリックして、コードを記述する画面になったら

右側のドロップダウンから【DblClick】を選択して、

「移動」ボタンに書いたコードをそのままコピペすれば、OK

 

f:id:tuna-kichi:20200501170305p:plain

 

********************************************************************************

Private Sub lbxShName_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim ShName As String

  ShName = lbxShName.Text

  Sheets(ShName).Select

  Unload frmMoveSheet

End Sub

********************************************************************************

 

 _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/