血圧管理手帳のExcel化-6/「ユーザー設定のビュー」は他のシートには使えないのでマクロ(VBA)化してみた

≪目次≫


いきさつ

 「血圧管理手帳のExcel化-5/Google先生に色々教えてもらって次4週分のグラフを」で知った [ユーザー設定のビュー](表示 タブ/ブックの表示 グループ)、
id:emkcafeさんに連絡する前にテストしていて、次のことが判った。

  • 作ったシートでは機能するが、他のシートで適用すると、作ったシートがアクティブになって機能する‥‥
  • これでは例えば家計簿77のように、月ごとにシートを分けていると、使えない‥‥

ことが判った。
 それならマクロ(VBA)で作ってみようかぁ‥‥ と。

ユーザー設定のビュー風マクロ(VBA)を作る

 苦労したのが「アクティブセルを左上隅にする」方法。先にGoogl先生に聞けばよかったぁ~

§ アクティブセルを画面の左上端に表示する(ScrollRow/ScrollColumnプロパティ)(moug)

 次のサンプルマクロは、セルD6をアクティブにして画面の左上端に表示します。
Sub Sample()
 Range("D6").Select
 MsgBox "アクティブセルD6を画面左上端に表示します"
 With ActiveWindow
  .ScrollRow = ActiveCell.Row
  .ScrollColumn = ActiveCell.Column
 End With
End Sub
mougさん ありがとう。下記 Sub が完成しました。

01. Public Sub view実績入力時()
02. Dim mbTitle As String
03. 
04.     mbTitle = "view実績入力時/" & getMBTitle()
05.         'ウィンドウ枠の解除
06.     With ActiveWindow
07.         If .FreezePanes Then .FreezePanes = False
08.     End With
09.     LNGAns = 非表示列の一括表示()
10.         'セルA17 を左上端にする。
11.     Range("A17").Select
12.     With ActiveWindow
13.         .ScrollRow = ActiveCell.Row
14.         .ScrollColumn = ActiveCell.Column
15.     End With
16.         '行22をウィンドウ枠の固定する。
17.     Range("E22").Select
18.     ActiveWindow.FreezePanes = True
19.         '新しい行をアクティブにする。
20.     Cells(getLast行番号 + 1, 2).Select
21.     
22. End Sub
  • 20行目:getLast行番号() では、入力してある最後の行番号を返す関数。

上記の他に下記の2本も完成した。

  • Sub view車番の空白を埋める時()
  • Sub view全面表示()

マクロをクイックアクセスツールバーに登録する

 マクロをクイックアクセスツールバーに登録できることは知っているが、使ったことはない。
よく使うマクロであろうから、登録してみた。

  1. Excel2013 を起動する。
    • 白紙の Book1 が表示される。
  2. [ファイル]⇒[開く]から マクロ.xlsm を開く。
  3. [ファイル]⇒[オプション]⇒ クイックアクセス ツールバー
  4. コマンドの選択で、マクロ を選択
  5. マクロ.xlsm!view実績入力時 を選択し、[追加(A) >>]する。
  6. 追加した「マクロ.xlsm!view実績入力時」のアイコンを変更する
    1. マクロ.xlsm!view実績入力時 を選択し、[変更(M)...]f:id:ogohnohito:20200420081721j:plain:right:w240
    2. 【ボタンの変更】ダイアログボックスから‥‥
      • 名前に相応しい? f:id:ogohnohito:20200420082219j:plain:h18 を選択し
      • [OK]で閉じる。
  7. Excelのオプション】を閉じる。

ついでに他の2っも登録した。f:id:ogohnohito:20200420084739j:plain:h26

§ 動作検証

 念のため、再起動した。

  1. Excel2013を起動
    • 白紙の Book1 が開く。
  2. クイックアクセス ツールバーf:id:ogohnohito:20200420082219j:plain:h18 をクリックすると‥‥
  3. マクロを有効にする? は出ず、マクロ.xlsm がエラーを出した。
  4. Excelを閉じる。
  5. 再度 Excel2013を開き、正規の(=ビューを使いたい)ブックを開く。
  6. クイックアクセス ツールバーf:id:ogohnohito:20200420082219j:plain:h18(view実績入力時)をクリックすると‥‥
  7. 何も言わずに動作した。🤷‍♂️



 腑に落ちないことがある(マクロ.xlsmは開いていない)が、動作した。
どんなブックでも動作するのであれば、マクロの内容(VBAコード)を見直す必要がある...

追伸

2020-04-20:リボンの最小化を追加

 動作確認後、リボンを最小化しようと下記を追加した。

    'リボンが最大化されていれば最小化する
If Application.CommandBars.GetPressedMso("MinimizeRibbon") = False Then
    Application.CommandBars.ExecuteMso "MinimizeRibbon"     'トグルボタン
End If
  • Ctrl+F1 で戻せる。

2020-04-21:VBAコードに連番を振った

 説明(覚え書き)の都合上、連番を次の方法で振った。

  1. VBAコードをコピーし、Excelの B列に貼り付ける。
  2. A列のセルの書式を "00." とし、連番を振る。
    • 2桁表示にしたのは、最終的に 9. と 10. でコードが1スペース分ずれることが判ったため。
  3. Excelの表をWordにコピペする。
  4. Word上で[表の解除]を行う。
    • 区切り記号は、半角スペース
  5. ブログに貼り付けた。

2020-04-21:Function 非表示列の一括表示() のコード

 如何にも原始的だが、この方法しか知らない...

01. Function 非表示列の一括表示() As Long
02. Dim mbTitle As String
03. Dim c As Long, lastCol As Long
04. Dim ctr As Long
05. 
06.     mbTitle = "非表示列の一括表示/" & getMBTitle()
07.     lastCol = Cells(getタイトル行番号(), Columns.Count).End(xlToLeft).Column
08.     ctr = 0
09.     For c = 1 To lastCol
10.         If Columns(c).Hidden Then
11.             ctr = ctr + 1
12.             Columns(c).Hidden = False
13.         End If
14.     Next
15.     非表示列の一括表示 = ctr
16.     
17. End Function
  • 7行目:getタイトル行番号() では 20 が返る。