VBA練習問題
VBA100本ノック 95本目:図形のテキストを検索するフォーム作成

VBAを100本の練習問題で鍛えます
最終更新日:2021-02-25

VBA100本ノック 95本目:図形のテキストを検索するフォーム作成


図形のテキストから指定の文字列を探すフォームを作成する問題です。


ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。
マクロVBA情報を中心に、エクセル関数・基本操作までサンプルとともに解説。初心者向けの基本から上級者向けの高度な内容までVBAサンプルコードを掲載解説しています。


出題

出題ツイートへのリンク

#VBA100本ノック 95本目
Activeブックの全シート・全図形のテキストから指定の文字列を探すフォームを作成。
「検索」で「検索文字列」を含む図形をリスト表示。
シート名!左上セル番地;テキスト文字列 ←列幅は随意
表示されたリストをクリック選択したら当該図形を選択してください。
※動画参照

マクロ VBA 100本ノック


サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_95.xlsm
https://excel-ubara.com/vba100sample/VBA100_95.zip


頂いた回答

解説

リストボックスは67本目で扱いました。
リストボックスでは非表示列がいろいろと便利に使えます。
Listプロパティには2次元配列で入れるので、配列の扱いに注意が必要です。
テキストを持たない図形の場合にはTextプロパティがエラーとなってしまうので何らかの対処が必要になります。

Option Explicit

Private wb As Workbook

Private Sub btnSearch_Click()
  Set wb = ActiveWorkbook '検索時のアクディブブックを退避
  Me.lstSearch.Clear
  If Me.txtSearch.Text = "" Then Exit Sub
  
  Dim ws As Worksheet, sp As Shape
  Dim ary, ix As Long, maxLen1 As Long, maxLen2 As Long
  For Each ws In wb.Worksheets
    For Each sp In ws.Shapes
      If sp.TextFrame2.HasText Then
        If sp.TextFrame2.TextRange.Text Like "*" & Me.txtSearch.Text & "*" Then
          If ix = 0 Then ReDim ary(3, ix)
          ReDim Preserve ary(3, ix)
          ary(0, ix) = ws.Name & "!" & sp.TopLeftCell.Address
          ary(1, ix) = sp.TextFrame2.TextRange.Text
          ary(2, ix) = ws.Name
          ary(3, ix) = sp.Name
          If LenB(ary(0, ix)) > maxLen1 Then maxLen1 = LenB(ary(0, ix))
          If LenB(ary(1, ix)) > maxLen2 Then maxLen2 = LenB(ary(1, ix))
          ix = ix + 1
        End If
      End If
    Next
  Next
  
  If ix = 0 Then Exit Sub 'Transposeで対応したので、この行は無くても動く
  
  With Me.lstSearch
    .MultiSelect = fmMultiSelectSingle
    .ColumnCount = 2
    .ColumnWidths = maxLen1 * 5 & ";" & maxLen2 * 5 'この数値は適当です。この程度あれば
    .List = Transpose(ary)
  End With
End Sub

Private Sub lstSearch_Click()
  wb.Activate '検索時のアクディブブックに切り替え
  Dim ws As Worksheet
  With Me.lstSearch
    Set ws = wb.Worksheets(.List(.ListIndex, 2))
    On Error Resume Next 'バージョン(2013で確認)によりws.Selectがエラーになる場合あり
    ws.Select
    ws.Shapes(.List(.ListIndex, 3)).Select
  End With
End Sub

Private Function Transpose(ByRef aAry) As Variant
  If IsEmpty(aAry) Then '今回は使っていませんが、0件の場合対応
    Transpose = Array(): Exit Function
  End If
  
  Dim ary, i As Long, j As Long
  ReDim ary(LBound(aAry, 2) To UBound(aAry, 2), LBound(aAry, 1) To UBound(aAry, 1))
  For i = LBound(aAry, 1) To UBound(aAry, 1)
    For j = LBound(aAry, 2) To UBound(aAry, 2)
      ary(j, i) = aAry(i, j)
    Next
  Next
  Transpose = ary
End Function


テキストの存在はTextFrame2.HasTextで判定できます。
先のVBAではブック切替に対応していますが、2013以降ではフォームをモードレス表示した場合にブックを切り替えるとフュームが隠れてしまいます。
これについては記事補足で説明しました。


補足

上記VBAでは、「検索」した時点のブックを退避しておき、リストクリック時にActivateしています。
しかし2013以降では、ブックを切り替えた場合にユーザーフォームが隠れてしまうようになっています。
ユーザーフォームを起動した時点のアクティブブックの時にしか前面に出てくれなくなっています。
つまり、ユーザーフォームを起動した後にブックを切り替えると、アクティブブックの後ろにユーザーフォームが隠れてしまうという事です。

マクロ VBA 100本ノック


したがって、そもそもフォームが隠れてしまうので、VBAを上記のような仕様にしても使いづらいものとなっています。

2010まではSDI(Single Document Interface)であったために、ユーザーフォームは常に前面に出ていました。
しかし、2013でMDI(Multiple Document Interface)になったため、このような現象が発生しています。
仕方ない部分もありますが、複数ブックでモードレスフォームを使いづらくなってしまっています。
でも、たぶん、改善されないんでしょうね・・・


サイト内関連ページ

第97回.図形オートシェイプ(Shape)
マクロVBAで、オートシェイプ(図形)を扱う場合の解説です。オートシェイプ(図形)はShapeオブジェクトであり、ShapeオブジェクトのコレクションがShapesコレクションになります。Shapeオブジェクトは、多くのオブジェクトをメンバーに持った複雑なオブジェクトとなっています。
ユーザーフォーム入門
第12回.リストボックス(ListBox)の追加
ユーザーフォーム入門として基礎から解説します。リストボックスについて解説します。リストボックスとコンボボックスの使い方はほぼ同様になります。前回コンボボックスで単一列(1列)の場合を説明しましたので、今回は特に複数列について詳しく説明します。




同じテーマ「VBA100本ノック」の記事

88本目:クロスABC分析作成
89本目:2つのフォルダの統合
90本目:セルに重なっている画像の削除
91本目:時間計算(残業時間の月間合計)
92本目:セルの色を16進で返す関数
93本目:複数ブックを連結して再分割
94本目:表範囲からHTMLのtableタグを作成
95本目:図形のテキストを検索するフォーム作成
魔球編:組み合わせ問題
魔球編:閉領域の塗り潰し
迷宮編:巡回セル問題


新着記事NEW ・・・新着記事一覧を見る

VBA100本ノック 95本目:図形のテキストを検索するフォーム作成|VBA練習問題(2月23日)
VBA100本ノック 94本目:表範囲からHTMLのtableタグを作成|VBA練習問題(2月23日)
VBA100本ノック 93本目:複数ブックを連結して再分割|VBA練習問題(2月22日)
VBA100本ノック 92本目:セルの色を16進で返す関数|VBA練習問題(2月20日)
VBA100本ノック 91本目:時間計算(残業時間の月間合計)|VBA練習問題(2月19日)
VBA100本ノック 90本目:セルに重なっている画像の削除|VBA練習問題(2月17日)
VBA100本ノック 89本目:2つのフォルダの統合|VBA練習問題(2月16日)
VBA100本ノック 88本目:クロスABC分析作成|VBA練習問題(2月15日)
VBA100本ノック 87本目:数式のシート間の依存関係|VBA練習問題(2月13日)
VBA100本ノック 86本目:全シートの総当たり表を作成|VBA練習問題(2月12日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ