VBA100本ノック 95本目：図形のテキストを検索するフォーム作成
図形のテキストから指定の文字列を探すフォームを作成する問題です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。
出題
出題ツイートへのリンク
#VBA100本ノック 95本目
Activeブックの全シート・全図形のテキストから指定の文字列を探すフォームを作成。
「検索」で「検索文字列」を含む図形をリスト表示。
シート名!左上セル番地;テキスト文字列 ←列幅は随意
表示されたリストをクリック選択したら当該図形を選択してください。
※動画参照
サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_95.xlsm
https://excel-ubara.com/vba100sample/VBA100_95.zip
Activeブックの全シート・全図形のテキストから指定の文字列を探すフォームを作成。
「検索」で「検索文字列」を含む図形をリスト表示。
シート名!左上セル番地;テキスト文字列 ←列幅は随意
表示されたリストをクリック選択したら当該図形を選択してください。
※動画参照
https://excel-ubara.com/vba100sample/VBA100_95.xlsm
https://excel-ubara.com/vba100sample/VBA100_95.zip
VBA作成タイム
この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。
他の人の回答および解説を見て、書いたVBAを見直してみましょう。
頂いた回答
解説
リストボックスは67本目で扱いました。
リストボックスでは非表示列がいろいろと便利に使えます。
Listプロパティには2次元配列で入れるので、配列の扱いに注意が必要です。
テキストを持たない図形の場合にはTextプロパティがエラーとなってしまうので何らかの対処が必要になります。
テキストの存在はTextFrame2.HasTextで判定できます。
先のVBAではブック切替に対応していますが、2013以降ではフォームをモードレス表示した場合にブックを切り替えるとフュームが隠れてしまいます。
これについては記事補足で説明しました。
リストボックスでは非表示列がいろいろと便利に使えます。
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
先のVBAではブック切替に対応していますが、2013以降ではフォームをモードレス表示した場合にブックを切り替えるとフュームが隠れてしまいます。
これについては記事補足で説明しました。
補足
上記VBAでは、「検索」した時点のブックを退避しておき、リストクリック時にActivateしています。
しかし2013以降では、ブックを切り替えた場合にユーザーフォームが隠れてしまうようになっています。
ユーザーフォームを起動した時点のアクティブブックの時にしか前面に出てくれなくなっています。
つまり、ユーザーフォームを起動した後にブックを切り替えると、アクティブブックの後ろにユーザーフォームが隠れてしまうという事です。
したがって、そもそもフォームが隠れてしまうので、VBAを上記のような仕様にしても使いづらいものとなっています。
2010まではSDI(Single Document Interface)であったために、ユーザーフォームは常に前面に出ていました。
しかし、2013でMDI(Multiple Document Interface)になったため、このような現象が発生しています。
仕方ない部分もありますが、複数ブックでモードレスフォームを使いづらくなってしまっています。
でも、たぶん、改善されないんでしょうね・・・
しかし2013以降では、ブックを切り替えた場合にユーザーフォームが隠れてしまうようになっています。
ユーザーフォームを起動した時点のアクティブブックの時にしか前面に出てくれなくなっています。
つまり、ユーザーフォームを起動した後にブックを切り替えると、アクティブブックの後ろにユーザーフォームが隠れてしまうという事です。
しかし、2013でMDI(Multiple Document Interface)になったため、このような現象が発生しています。
仕方ない部分もありますが、複数ブックでモードレスフォームを使いづらくなってしまっています。
でも、たぶん、改善されないんでしょうね・・・
サイト内関連ページ
第97回．図形オートシェイプ（Shape）
第12回．リストボックス(ListBox)の追加
・図形オートシェイプ（Shape）関連のオブジェクト群 ・図形オートシェイプ（Shape）の追加 ・図形オートシェイプ（Shape）の削除 ・図形オートシェイプ（Shape）の編集 ・図形オートシェイプ（Shape）の全選択 ・図形オートシェイプ（Shape）の扱い方を工夫する ・オートシェイプ（Shape）を扱う実践例 ・図形オートシェイプ（Shape）に関連する記事
ユーザーフォーム入門第12回．リストボックス(ListBox)の追加
・リストボックスの追加 ・リストボックスのプロパィ ・Listプロパティ ・Columnプロパティ ・RowSourceプロパティ ・選択されたリスト項目の取得 ・リストボックスの複数選択 ・IntegralHeightプロパティ
同じテーマ「VBA100本ノック」の記事
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
93本目：複数ブックを連結して再分割・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
94本目：表範囲からHTMLのtableタグを作成・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
95本目：図形のテキストを検索するフォーム作成96本目：Accessデータを取得（マスタ結合&抽出）
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
97本目：Accessデータを取得（グループ集計）・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
98本目：席替えルールが守られているか確認・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
99本目：自動席替え（行列と前後左右が全て違うように）・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
100本目：WEBから100本ノックのリストを取得・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
魔球編：組み合わせ問題・出題 ・頂いた回答 ・解答 ・補足
魔球編：閉領域の塗り潰し・出題 ・頂いた回答 ・解答 ・解答VBAコード
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
- ホーム
- マクロVBA入門編
- VBA100本ノック
- 95本目：図形のテキストを検索するフォーム作成
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。