ExcelコマンドBOXでワンランク上のマクロ作成＋他のソフトにコピーまでしてみる

おしゃれマクロ作成

ExcelでもVBAを使えば、専用ソフトの様なコマンドボックスを設定でき、ボックス内に入力した文字のデータを反映させたり、下の図の様にプルダウンで選択したデータを反映できます。

Excelのコマンドボックス

この記事では次の事を紹介します。

  1. Excel VBAでコマンドボックスを設定する方法
  2. 例として、コマンドボックスのプルダウンで選択した内容に合わせて貼り付ける内容を変更する関数など
  3. 応用として、他のソフトに②を貼り付ける方法

少し長くなるので、目次から必要な部分に飛んでください。

広告

① Excel VBAでコマンドボックスを設定する方法

次の流れで作業していきます。

(1) VBAでユーザーフォームを設定する
(2) ユーザーフォームにコードを入れていく
(3) ユーザーフォーム上で実行ボタンを押して、マクロを動かす

(1) VBAでユーザーフォームを設定する

標準モジュールを右クリック – 挿入 – ユーザーフォーム

ユーザーフォーム設定

この様な画面が出てきます。
左のツールボックスからUserFormに追加していきます。

ユーザーフォーム設定前

・テキストボックス（は今回どちらでもよい）
・リストボックス：プログラムの選択に必要
・コマンドボックス：アクションのトリガーとなるボタン

ユーザーフォーム設定後

プロパティウィンドウで、フォントや文字のサイズ、左寄せなどを調整していきます。

ユーザーフォーム個別設定1
ユーザーフォーム個別設定2

(2) ユーザーフォームにコードを入れていく

ユーザーフォームのコマンドボックスをマクロ実行のトリガーにすべく、コードを入れていきます。

まずはUserFormを右クリック – コードの表示 で入力画面を出します

ユーザーフォームのコード呼び出し

ユーザーフォームのコード
Private Sub UserForm_Initialize()
'リストボックスの設定 UserForm1.Caption = "プログラムNo.の入力" ListBox1.RowSource = Range("K2:K10").Address
End Sub
Private Sub CommandButton1_Click()
'変数の定義と値付け Dim Listbx1 As String Listbx1 = Me.ListBox1.Text
'コマンドボタンを押したらBOXが消える設定をする Unload UserForm1
'リストボックスで選択した値を指定のセルの値にする Range("H2").Value = Listbx1
'変数の定義 St_cell = Range("G6").Value Fn_cell = Range("G7").Value
'コピー Range(St_cell & ":" & Fn_cell).Select Selection.Copy Range("F14").Select ActiveSheet.Paste
End Sub

コード解説

  • 『Private Sub UserForm_Initialize()』はフォームの初期設定です。
    →UserForm1.Caption = ” “：ボックス左上のテキスト名を定義
    →ListBox1.RowSource = 〇〇：ListBoxの引用元(反映元)を決めます。
  • 『Private Sub CommandBottun1_Click()』はボタンを押した時の動作設定です。
    →変数を設定
    →リストボックスで選択したプログラム名をシートの必要なセルに代入
    ※ここでは、コピー範囲のトリガーになるH2セルの値をリストボックスで選択した値になるようにしています。（トリガーの部分の説明はセクション②で紹介）

図で補足説明を書いたのが下です。

マクロコードと解説
memo

図の左上ウィンドウは標準モジュールです。UserFormの呼び出しに使います。
『UserForm1.Show』だけでOK

標準モジュールのコード
Sub popup()
'ユーザーフォーム（BOX）の召喚 UserForm1.Show
End Sub

図の左下がUserForm用の画面です。

(3) ユーザーフォーム上で実行ボタンを押して、マクロを動かす

ユーザーフォームを呼び出す登録です。
オートシェイプを右クリック – マクロ登録

マクロ登録

実際に『BOX起動』のマクロを下の順にやってみると

マクロ実行

↓　↓

マクロ実行後

コピーが完了しました！！

② プルダウンで選択した内容に合わせて貼り付ける内容を変更する関数

セクション①で出てきたExcelは、例えばこどものプログラミング学習でよくある、「移動」と「方向転換」のコマンドを使って、目的地に移動する行動パターンを想定しています。

行動パターンを何種類か作り、それをプルダウンで選択するという事をしたい。

次の流れで作業していきます。

(4) 計算シートを作る
(5) プルダウンを設定する
(6) テスト用マクロを作って動作確認

(4) 計算シートを作る

計算シートを作るにあたり、3つの関数を理解する必要があります。

MATCH関数

MATCH関数は

＝MATCH( 検索値, 検索範囲(主に1行か1列を指定), 変数：0 or 1 or -1)

という形で構成されます。

検索範囲の中の検索値が、何番目にあるかの数字を返します。
変数が0なら検索値と同じ値を返し、1とか-1はややこしいのでおりんパパは使いません。
（説明もややこしいので割愛しますー）

match使い方1

この例で行くと『02-おりんパパ買い物』というプログラムのタイトルをA列で検索してどこにあるかを取得します。（12番目にありますね）

これはA1～A26で検索しているので、12番目のA12にある分かりやすい例です。

検索範囲をA3～A26にしたら、10番目にあるので10と返ってきます。

ADDRESS関数

ADDRESS関数は、指定した行列を文字列として返す関数で

＝ADDRESS( 何行目, 何列目, 変数：1～4)

という形で構成されます。

変数は相対参照（$無し）か絶対参照（$有り）を決める
  • 1・・・行列ともに絶対参照（$A$1）
  • 2・・・行だけ絶対参照（A$1）
  • 3・・・列だけ絶対参照（$A1）
  • 4・・・行列ともに相対参照（A1）
相対参照と絶対参照

【相対参照】

参照セルや参照範囲が変化する参照方式
数式をコピーして別の位置に貼り付けると、コピー先のセル位置に応じて（相対的に）参照先が変わります。

【絶対参照】

参照セルや参照範囲が固定される参照方式
数式をコピーして別の位置に貼り付けても、参照先が変わることはありません。

そして、ADDRESS関数とMATCH関数をくみあわせると

『02-おりんパパ買い物』プログラムがどのセルから始まるのかが文字列で返ってきます。

これがマクロでセルを指定する時に役に立ちます。

address使い方1

INDIRECT関数

INDIRECT関数は、指定したセル番号の値を返す関数で

＝INDIRECT (セル番号 , 変数 )

という形で構成されます。

変数を省略する場合はA1形式、FALSEにするとR1C1形式になります。

形式について補足

A1形式は「A2」,「B2」という形式で、英字の列番号と数字の行番号で表します。

R1C1形式は「R2C1」,「R2C2」のようにROW（行）とCOLUMN（列）番号をつけて表します。
マクロ（VBA）で繰り返し作業がある時には、R1C1形式が多くなります。

先ほどの例では、F7のセルで入れた計算式の値がA12になっていますので、

＝INDIRECT(F7)
＝INDIRECT(”A12”)
＝A12

のどれも『02－おりんパパ買い物』が解になりますが、
=INDIRECT(F7)の場合はF7が計算式によって変わるので、可変対応できます。

それ以外はセル指定になってしまいます。

indirectの使い方

目的は指定のプログラム（今回は『02-おりんパパ買い物』）の記載位置が変わっても必要な部分をコピーするマクロを作るので、開始セルがA12(もしくはR12C1)で終了セルがD16(もしくはR16C4)を導けたらよいです。

そしてプログラムの調整で記載位置や行数が変わっても連動します。
この連動することが、マクロを構成する際に重要になります。

indirectの使い方2

準備として3つの関数とその組み合わせを紹介しました。

最終的には、開始位置には
=ADDRESS(MATCH( コピーしたいプログラム, プログラム名の列範囲,0),1,4)

終了位置には
=ADDRESS( (MATCH( コピーしたいプログラム, プログラム名の列範囲,0))+(MATCH(1,–(INDIRECT(開始位置のセル番号):範囲=””),0))-2,4,4)

となります。

関数を駆使して作成したフィールドはこちらの通り

フィールド

ファイルの拡張子は『○○.xlsm』にして保存てください。

(5) プルダウンを設定する

リスト（ここではK列）からプルダウンを作成するには、

データ – データツール – データの入力規則 を使います

プルダウン作成

設定タブをこの様にします。

プルダウン作成2

これで、セルH2はプルダウン選択できる様になります

プルダウン作成3


開始セルと終了セルを設定しておく

数式おさらい


(6) テスト用マクロを作って動作確認

テスト用マクロは簡単な構成です。
赤字に解説を追記しています。

可変の数値・文字列を扱うので、
変数を設定して定義⇒コマンドに変数を入れ込む
という流れです。

『’』をつけるとコメントアウトして式には反映されません

練習マクロ

コピー用のコードです。

HTML
Sub programcopy()
'変数の設定 Dim St_cell As String Dim Fn_cell As String
'変数の定義 St_cell = Range("G6").Value Fn_cell = Range("G7").Value
'コピー Range(St_cell & ":" & Fn_cell).Select Selection.Copy Range("F14").Select ActiveSheet.Paste
End Sub

マクロのコマンドをコピーではない別のものにしたい場合、こちらの記事も良かったら参考にしてください。

Excelマクロ登録 Excel_VBAの知識がなくても大丈夫！マクロを簡単に作成し、すべてのファイルで使用する方法

『マクロの記録』という機能をつかえば、コマンドを知らなくてもやりたいコードを作ってくれます。

起動ボタンを作る

・オートシェイプから好きな図形を出し、色・枠線・文字を装飾

・そして右クリック – マクロの登録 を選択

図形にマクロを登録

先ほど作成したマクロを選択

図形にマクロを登録2


実際に実行してみる

コピーするプログラムが『02- 』になっている状態。

マクロ起動ボタンを押すと、ぎゅんっとコピーされました。

マクロ実行1

コピーするプログラムを『01- 』にした状態

開始セル・終了セルが変化し、コピー後の結果も反映されていますね

マクロ実行2

③ 他のソフトに先ほどの②を貼り付ける方法

まず、Excelではセルで行列が細かく分かれていますが、他のソフトは行は分かれていても列は区分けされていない事が良くあると思います。

（例えば、縦方向に改行はできるが、横方向の区分はできない）


よって手順として、

(7) Excel上で他のソフトへ変換するシートを作っておく
(8) 変換シートへコピー⇒対象ソフトへコピーするコード作成
(9) 実行する

という流れで作っていきます。

(7) Excel上で他のソフトへ変換するシートを作っておく

これは一例です。連動させたいソフトの列数に対応したシートにして下さい。

このマクロでは、A列～C列が他ソフトへの貼り付け部で、
E列～がﾍﾞｰｽのExcelシートからの貼り付け部です。

貼り付けたいソフトは3列で表記されますが、元Excelでは4列で編集したいので、G列とH列をC列に統合させています。

コピーした部分をSheets(“他ソフトシート”)のRange(“E2”)に貼り付ける様にします。

コピー前の整理例

＜数式の解説を少々＞

=IF(〇=””,””,・・・)で〇が空白なら空白を返し、
空白でないなら・・・に続くはいろいろなシーンで使えます！

また”XY：”という文字列を&で結び”XY：”&G4とすると
『XY：2』(G4セルの値が2の場合)という様に表示できます。
対象のソフトの書き込み方式に合わせて、Excelの数式を設定します。

(8) 変換シートへコピー⇒対象ソフトへコピーするコード作成

前回のコード（右側）と比較して、青字部を追加

他ソフトへのコピーコード

Selection.ClearContents ⇒ 選択部をDelete

AppActivate “○○” ⇒ 起動済みの対象ソフトを最前面に表示
※最前面に表示することで、貼り付けを実現できます。

ソフトの〇〇という部分は、そのソフトのウィンドウ（画面）が持つキャプチャ（名前）を入れるのですが、ソフトのメーカーに確認するか、この部分を試しに入れるかします。

ｿﾌﾄのキャプチャ

SendKeys “^(V)” ⇒ SendKeysはキーボードの操作と同じができます。
こちらでも紹介しています。

Outlook左手ショートカット 片手で食事をとりながらメールを見る：Outlook(365)で既読→次のメールへ移動するショートカット

そのソフトでキーボード操作ができない場合、
上の記事のこの部分で紹介しているマウス操作を
導入したら解決するかもです。

標準モジュールのコード（ユーザーフォーム呼び出し）
Sub popup()
'ユーザーフォーム（BOX）の召喚 UserForm1.Show
End Sub
ユーザーフォームのコード（他ソフトへコピーする）
Private Sub UserForm_Initialize()
'リストボックスの設定 UserForm1.Caption = "プログラムNo.の入力" ListBox1.RowSource = Range("K2:K10").Address
End Sub
Private Sub CommandButton1_Click()
'変数の定義と値付け Dim Listbx1 As String Listbx1 = Me.ListBox1.Text
'コマンドボタンを押したらBOXが消える設定をする Unload UserForm1
'リストボックスで選択した値を指定のセルの値にする Range("H2").Value = Listbx1
'変数の定義 St_cell = Range("G6").Value Fn_cell = Range("G7").Value
'他ソフト用シートに残っているものを消しておく Sheets("他ソフトシート").Select Range("E2:H50").Select Selection.ClearContents Sheets("ﾍﾞｰｽ").Select'他ソフト用シートへ必要事項コピー Range(St_cell & ":" & Fn_cell).Select Selection.Copy Sheets("他ソフトシート").Select Range("E2").Select ActiveSheet.Paste
'そのシートから他ソフトへコピー Range("A4:C50").Select Selection.Copy
'他ソフト（暫定でメモ帳）に貼りつけ AppActivate "メモ帳" Application.Wait Now + TimeSerial(0, 0, 1) SendKeys "^(v)" 'Cntl＋Vで貼付け
End Sub

(9) 実行する

実行してみると

他ソフトコピー実行

01のプログラム選択時

01実行後

02のプログラム選択時

02実行後

おまけ

別記事でExcelテクを整理しています

 

Excelテク集の目次は下の通り
(下のリンクから各項目に直接アクセスできます)

時短や快適さアップのテクニックが多くあります！！

最後まで見ていただき、ありがとうございました。


広告


広告


広告


前の記事

Excel瞬間移動技（セル移動、シート移動、ファイル移動）を振…

次の記事

なぜOutlookには『添付付きで返信』『添付付きで全員に返信…
添付付き返信アイキャッチ