2022年8月18日木曜日

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

フォームコントロールのドロップダウン
    
専用のワークシートを設けて、サイズも限定しながら表示させるアイデアです。

これだと、ユーザーフォームに近いサイズの専用シートで操作・作業は可能ね。
ただ、このフォームの作業中は、作表シートは隠されてしまうのは否めません。

両方の閲覧はできませんが、フォームで作業した後は、作表シートに復帰する。
まあ、許せる範囲と思いますが、後はフォームコントロールのパーツ貼り付け。

ボタンは月並みとしても、コンボボックス、スピンボタンなどは処理が複雑だ。
しかも、フォームコントロールのパーツは、総て"Shape"オブジェクトなの。

要するに、オートシェイプのような描画オブジェクトになっているのが不思議。
しかも、貼り付けた後に、マクロの登録をしても、Shapeの名称が異なります。

だから、あらかじめ確認する作業が必要で、このためのコードを紹介しますね。
この画面では、コンボボックスのマクロ登録をするため、命名は”Drop1”です。

写真

これで、次に”Sample1”と書いたプロシージャを実行させて、次の作業に着手。
シート上の”Shape”オブジェクト名称を、列挙させながらリストを作成します。

そうすると、名称は”Drop Down 1”で、タイプ8のフォームコントロールです。
まあ、フォームコントロールの”Shape”をシートに複数並べるときは、要注意。

一個作成したら名称の確認が必要だと思いつつ、次にリストを表示させたいな。
名称がドロップダウンになっているのに、なぜコンボボックスと言わないのか。

摩訶不思議な上に、リストに値を追加した後の、選択値の表示がちょっと違う。
先ず、ユーザーフォームでお馴染みの”AddItem”で設定後、値を選んだとする。

選んだ値を取得するのに、ControlFormat.Valueで記述すると、値が順番なの。
つまり、登録したアイテムの値の何番目にあるのか、数値だけが取得されます。

なので、登録する値はセルに書き並べて、それをアイテムとして読み込ませる。
次に値が選ばれたら、その順番の値からセル番地を指定して登録値を取得する。

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

この場合ですと、コードの記述が”.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 'リスト項目はB列1行目から読み込み
      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 'リスト項目B列の指定した順番のセル値取得
Skip:
End Sub

Sub ShapeDelete() 'ドロップダウンボックスの削除・シャープ名指定
ActiveSheet.Shapes("Drop Down 1").Delete
End Sub



いいねと思ったら、三つポチっとね!
にほんブログ村 スキースノボーブログへにほんブログ村 スキースノボーブログ スキーへにほんブログ村 旅行ブログ 旅日記・旅の思い出へ
にほんブログ村    にほんブログ村      にほんブログ村 



0 件のコメント:

コメントを投稿