シート上のドロップダウンリストでは、登録したアイテムを選ぶと、リストアップ番号しか抽出されないのが手間だな － Validationオブジェクト（VBA）

フォームコントロールのドロップダウン

　　　　

以前の投稿では、ActiveXを使わずにユーザーフォームを捻りだす工夫でした。

専用のワークシートを設けて、サイズも限定しながら表示させるアイデアです。

これだと、ユーザーフォームに近いサイズの専用シートで操作・作業は可能ね。

ただ、このフォームの作業中は、作表シートは隠されてしまうのは否めません。

両方の閲覧はできませんが、フォームで作業した後は、作表シートに復帰する。

まあ、許せる範囲と思いますが、後はフォームコントロールのパーツ貼り付け。

ボタンは月並みとしても、コンボボックス、スピンボタンなどは処理が複雑だ。

しかも、フォームコントロールのパーツは、総て"Shape"オブジェクトなの。

要するに、オートシェイプのような描画オブジェクトになっているのが不思議。

しかも、貼り付けた後に、マクロの登録をしても、Shapeの名称が異なります。

だから、あらかじめ確認する作業が必要で、このためのコードを紹介しますね。

この画面では、コンボボックスのマクロ登録をするため、命名は”Drop1”です。

写真

これで、次に”Sample1”と書いたプロシージャを実行させて、次の作業に着手。

シート上の”Shape”オブジェクト名称を、列挙させながらリストを作成します。

そうすると、名称は”Drop Down 1”で、タイプ８のフォームコントロールです。

まあ、フォームコントロールの”Shape”をシートに複数並べるときは、要注意。

一個作成したら名称の確認が必要だと思いつつ、次にリストを表示させたいな。

名称がドロップダウンになっているのに、なぜコンボボックスと言わないのか。

摩訶不思議な上に、リストに値を追加した後の、選択値の表示がちょっと違う。

先ず、ユーザーフォームでお馴染みの”AddItem”で設定後、値を選んだとする。

選んだ値を取得するのに、ControlFormat.Valueで記述すると、値が順番なの。

つまり、登録したアイテムの値の何番目にあるのか、数値だけが取得されます。

なので、登録する値はセルに書き並べて、それをアイテムとして読み込ませる。

次に値が選ばれたら、その順番の値からセル番地を指定して登録値を取得する。

こんなコードの流れになるのかなと思ったりもして、サンプルコードの紹介だ。

というわけで、セルをコンボボックスにした”Range().Validation”もあります。

この場合ですと、コードの記述が”.Add Type:=xlValidateList”で、また異なってくるのですが、ActiveXを使わずにコンボボックスを作るのは、色々とアイデアがあるようなので、マクロのコードを書くときには、Mac用とかウインドウズ用とか、OSの用途に応じて書き分ければよいだけだと、思ったのでした。

<サンプルコード>

Sub sample1() 'シート上の登録されたシェープを調べる

Dim sp As Shape

  On Error Resume Next

  For Each sp In ActiveSheet.Shapes

    Debug.Print sp.Name ; ",Type=" & sp.Type

   Next

End Sub

Sub ShapePosition() 'シート上の登録シェープ位置指定

  With ActiveSheet.Shapes("Drop Down 1")

    .Top = 20

    .Left = 20

  End With  '登録シェープの位置変更は、数値を変えて実行

End Sub

Sub AddingItem() 'シート上のドロップダウンボックスのリスト作成

Dim i As Long

  With ActiveSheet.Shapes("Drop Down 1").ControlFormat

    .RemoveAllItems

      For i = 1 To Cells(Rows.Count, 4).End(xlUp).Row

        .AddItem Cells(i, 4).Value 'リスト項目はＢ列１行目から読み込み

      Next i

  End With

End Sub

Sub ComboBox_CellLink() 'ドロップダウンボックスのリスト値を取得

ActiveSheet.Shapes("Drop Down 1").ControlFormat.LinkedCell = "$B$5"

End Sub 'シートのセル絶対番地で書き込んでいる

Sub PickedValue() 'ドロップダウンボックスのリスト値から項目を取得

Dim i As Long

On Error GoTo Skip

i = ActiveSheet.Shapes("Drop Down 3").ControlFormat.Value

Debug.Print Cells(i, 4).Value 'リスト項目Ｂ列の指定した順番のセル値取得

Skip:

End Sub

Sub ShapeDelete() 'ドロップダウンボックスの削除・シャープ名指定

ActiveSheet.Shapes("Drop Down 1").Delete

End Sub

いいねと思ったら、三つポチっとね！
  
にほんブログ村    にほんブログ村      にほんブログ村