タグ別アーカイブ: ユーザーフォーム

エクセルVBA~ユーザーフォームの不思議~ガッツの日記 第12回

【エクセルはとにかく機能が豊富です。その象徴がユーザーフォームです】

エクセルVBAの初級者か、そうでないかの境目の一つは「ユーザーフォームが使えるか?」だと思っています

ユーザーフォームを使うことで、エクセルファイル内の利便性は格段に向上します

ユーザーフォームを使えると、入力と表示を明確に切り分けることができるのでデータを有効活用できるからです

例えば、下の画像を見てください

これはG2セルとG7セルに移動して、会社名をそれぞれ入力しています

つまり、宛先を表示するために、宛先の表示場所に移動して入力作業をしています

これが、ユーザーフォームを使うとどうなるでしょう?

ユーザーフォーム内に表示されている内容から「選択」を行い、「登録」ボタンを押すだけです

これは、繰り返しになりますが、入力と表示が分かれているから行えます

便利なユーザーフォームですが、実際に活用するとなると最初はかなりとっつきにくいと思います

通常のエクセルと考え方が違うからです

後、とにかく機能が多いです

今回の記事ではユーザーフォームを使う中で、躓きやすい「不思議な箇所」、並びにこれは便利だと思った機能を解説したいと思います

VBAを使った事が無い方、VBAを使ったことがあるけどユーザーフォームを使ったことが無い方も、これを機会にユーザーフォームの魅力、エクセルの奥深さを感じて頂けたら幸いです

ちなみに上記のユーザーフォームのコードは以下の通りとなります

初期表示

***

With ListBox1 ’リストボックスのオブジェクト名
.Font.Size = 9
.ColumnCount = 3
.ColumnWidths = “20;50;100”
.TextAlign = fmTextAlignCenter

‘リストボックスに情報を追加
Dim i As Long
Dim myLast As Long
myLast = Worksheets(“①”).Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To myLast
.AddItem
.List(.ListCount – 1, 0) = Worksheets(“①”).Cells(i, 2).Value
.List(.ListCount – 1, 1) = Worksheets(“①”).Cells(i, 3).Value
.List(.ListCount – 1, 2) = Worksheets(“①”).Cells(i, 4).Value

Next

End With

***

登録ボタン

***

With ListBox1
Dim myValue As String
myValue = .List(.ListIndex, 1)

Worksheets("①").Range("G2").Value = .List(.ListIndex, 2)
Worksheets("①").Range("G7").Value = .List(.ListIndex, 2)

End With

***

リストボックスの10列超を表示できない

例えば、こんな横長な表があったとします

こちらの表をリストボックスで表示してみます

すると上の画像のような不思議なエラーになります

使用しているコードは「AddItem」になります

実はこの「AddItem」を使用する時は10列までしかリストボックスに表示できません

この場合は「RowSource」か「List」を使用し、直接エクセルの範囲を指定します

RowSource

RowSourceの場合は、関数で別シートを参照する時のような書き方で範囲を指定します

これで、B3セルからM列の最終行までをリストボックスで表示できます

しかも、ColumnHeadsを使用して指定範囲の1つ上の行を見出しとして表示できるのが便利です

List

Listで範囲指定する場合には、下のように通常のVBAの書き方をして、最後にValueを付け加えます

.List = Worksheets(“②”).Range(“B2:M” & myLast).Value

但し、RowSourceと違い、見出し設定はできませんのでご注意ください

リストボックスから行削除が行えない

これまた不思議なのですが、.RemoveItemを使用してもリストボックスの行を削除できないことがあります

しかもエラーメッセージが不思議な内容ででます

実はこれ、RowSourceを使用してリストボックスを表示すると起こる現象です

RowSourceを使用する場合であれば、削除はデータ元のエクセルで行う必要があります

他の方法でリストボックスを作成している場合はこの現象はありません

リストボックスを複数選択にする

これはユーザーフォームではこんな事もできるという紹介です

上の画像の「MultiSelect = fmMultiSelectMulti」を追加するだけで、リストボックスは複数選択にできます

これだとどこが選択されているか、どう選択したらいいか分かりにくい、という場合は次の一行を足します

そうすれば、次の画像のように各行にチェックボックスが表示されます

複数選択できるのは分かったけど、どう選択されたものを取得するの?

と思われた方も多いと思います

複数選択されたものを取得するには、リストボックスの各行を繰り返し処理で取得します

そしてポイントは「Selected」を使用して、選択行を特定することです

***

With ListBox1
For i = 0 To .ListCount – 1 ‘ListBoxの各行
If .Selected(i) Then ‘行が選択されている場合
MsgBox .List(i, 0)
.Selected(i) = False
End If
Next i

End With

***

これで複数行を取得できます

テキストボックス内で文字列を折り返す

テキストボックス内でセルと同じように折り返せないか?と思った方もいらっしゃると思います

この場合はプロパティで「MultiLine」をFalseからTrueに変更します

後、もう一か所関係がある箇所があります

WordWrapがTrueに設定してある必要があります

こちらは通常はTrueに設定されています

これで文字列は折り返されます

入力位置遷移(カーソル遷移)

テキストボックス間での遷移を変更したい時があります

以下のGIF画像では横にカーソルが動いています

この遷移を変えたい時には「TabIndex」を変更します

今回の場合で言えば、下のテキストボックスを3から1に変更します

このようにTabIndexを変えれば、カーソル遷移は変わります

<まとめ>

今回はエクセルVBAのユーザーフォームについて記事を書きました

私は正しい脱エクセルをかかげていますが、最近、改めてエクセルの浸透の深さに驚いています

他の言葉で言い換えれば、様々な企業で貴重なエクセル資産があります

わざわざ資産を他に移し替えるのはもったいないケースも多々ありますので、エクセルの研究は引き続き続けていきたいと思います

その研究テーマの一つが今回のユーザーフォームですね

ユーザーフォームは本当奥深いですね

今後も研究は続けていきたいものです

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

半自動でデータを仕訳をするためのマクロ~複数行・カーソル選択

【カオスな現場経験 x 分析力を組み合わせた、少し尖ったマクロのレシピ集】

 今回はデータ現物、もしくは他のデータを照合しながら「データの仕分け」を行いたい時に役に立つマクロ/VBAを紹介します 

 複数行をカーソルで選択した後、ショートカットキーでマクロを起動し、カーソルで選択した範囲の内容を自動で変更します

全てのデータを一括で処理できるわけではないですが、手動で行うよりは相当な業務削減になります

尚且つ、後でデータベースに取り込む想定なので、IDを自動で変更する内容になっています

上のGIF画像はシート上のコードを自動で変えるところですが、ユーザーフォームで内容も変えることができます

 ユーザーフォームは前述の通りショートカットキーで開くのですが、ユーザーフォームが開いた時には自動で修正後の商品コードが反映されます

以前、私は肉関連の仕事をした時があります

この仕事のデータ管理上の特徴は、入荷した肉を部位の特性に応じて仕分けをすることでした

全く同じ質の肉が入荷するわけではないので、こういった仕分けが必要になっていました

 私が関わる前は、現場担当者が書面上で仕分けを行い、別な担当者がエクセルのを手動で分けて「肉の仕分け状況」をデータ上で表現していました

この一連の作業をするのに毎週、複数人で相当な時間をかけていました

 つまり、列で仕分けをしているということは、人間が目で見ないと「入荷データが区分されている/IDが違っている」ことに気づけないのです

 他の言葉で言い変えると、「仕分け情報」をコードで表現できておらず、データベースに入荷データを取り込めない形になっていました

ですので、その後の作業も完全に手動になってしまっていました

仕分けした入荷データをデータベースに取り込めるようにする際に、活用したのが今回紹介するマクロです

以下が今回紹介するマクロの流れです

複数行・選択➡ショートカットキー/マクロ起動➡ユーザーフォームで内容確認・仕分け実行

 手動でデータを仕分けするよりはかなり高速で処理を行えるだけでなく、データベースにも取り込める形になっています

 このマクロは肉などの生き物関連の他にも、意外と活用機会があるのではないかと思いますので、役に立つ機会があれば幸いです

ポイント

専用IDの作成

 今回は現実にそんな場面が実際にあるかは別として、入荷データと現物の肉を見ながら商品コードを設定していく内容になっています

商品コードは3か所に分け、「ハイフン」で繋ぎます

最後の枝番は1をデフォルトに設定しておいて、現物を見ながら変えていくイメージです

例:A-1-1 A-1-2 A-1-3

カーソルの選択範囲を取得

カーソルの選択範囲は、開始行終了行の2つの行位置で範囲を取得します

ショートカットキーに紐づけたマクロを起動した後は、開始行と終了行の位置を取得して変数に格納します

開始行と終了行の位置を取得するコード

カーソルの選択範囲の開始行は次のコードで取得します

rowStart = ActiveCell.Row

カーソルの選択範囲の終了行は次のコードで取得します

rowEnd = Selection(Selection.Count).Row

ショートカットキーでマクロ起動

ショートカットキーは次の画面で割当することができます

枝番の自動繰り上げ

前述の通り、ユーザーフォームが開いた時には修正後の商品コードがユーザーフォームに反映されます

 商品コード(5桁)を別途、4桁で管理した上で(例:A-2-2➡A-2-)、4桁別に枝番の最大値を別シートで管理できるようにしておくのがポイントです

以下の流れで枝番の自動繰り上げを設定します

①ショートカットキーが押された時

・まずシートの内容を全て別シートにコピーします

 コピー元シート名:仕分けシート、コピー先シート名:最大値

・コピーされた内容は商品コードを4桁に変換します(例:A-1-1A-1-)。その上で重複を排除します

 同時に下の画像のように枝番最大値を1に設定します

最大値シート

・コピー元のシートの全ての行とコピー先のシートをMATCH関数により照合して、コピー先の最大値シートの枝番最

 大値を置き換えます。例えば、下の画像のようにコピー元の仕分けシートにてA-2-2という風に既に「A-2-」の枝

 番の最大値が2になっている場合には、コピー先の最大値シートの枝番最大値を2に変更します

シート間の照合

②ユーザーフォームの実行ボタンを押した時

・コビー先の最大値シートから、該当の商品コード(4桁)の最大値を取得し、取得した最大値に+1をした枝番を

 商品コード(4桁)の枝番にします

ユーザーフォームの内容をシートに反映

ユーザーフォームの実行ボタンを押したら、取得した開始行位置と終了行位置で指定する範囲にユーザーフォームの内容を反映します

<まとめ>

今回は、カーソルで選択した範囲を自動的に変換するマクロについて解説を行いました

カーソルで選択した範囲の開始行と終了行の位置を取得するのが最大のポイントです

このマクロを実際に使用する時には「処理を間違えた時にどうするの?」という疑問が出ると思います

間違えた時に修正を行えるようにするには、元のデータを別シートにコピーしておくのがいいと思います


にほんブログ村