ExcelVBA UserFormに沢山のボタンを貼り付けたい

質問

Excel2003のVBAを使っています。
コマンドボタンが沢山付いたユーザーフォームを起動したいのですが、
フォームに貼り付けるコマンドボタンの個数は、マクロを実行した時に決めたいです。
（例えば任意のセルの値を読み込むなどして）
要するにコードの中で、コマンドボタンの生成をしたいのですが、
可能でしょうか。
VBでプログラム組んでる知人に聞いたところ、「コントロール配列」なるものを利用すれば可能だそうですけど、
VBAにはそれが無いと聞きました。

回答No.7

Daiki_PaPa · ベストアンサー

少し時間が出来たので、作ってみました。
フォームを開いたら、ボタンを5個作成する最低限のプログラムですけどね。
一応コントロール配列になってます。
以下のコードをコピペしてみて下さい。
動的にボタンが作成できることを確信できると思います。
後は、勉強と応用です。

１．まず準備として、新規のブックにUserForm、標準モジュール、Class1モジュールを追加して下さい。
２．以下のI～IIIをコピペ。
３．全てコピーが完了したら、標準モジュールの「　FormOpen　」を実行して下さい。

(I)
'以下をUserFormモジュールにコピペして下さい。

'---------------------------------------------------------------------

Private AddCmd(4) As Class1

Private Sub UserForm_Click()
MsgBox AddCmd(4).Index
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer

'ボタン作成
For i = LBound(AddCmd) To UBound(AddCmd)
Set AddCmd(i) = New Class1
AddCmd(i).Add ContainerClassObject:=UserForm1, _
Top:=i * 24 + 12, _
Left:=12, _
Height:=21, _
Width:=84, _
Caption:="ボタン " & i, _
Index:=i
Next i
End Sub

'---------------------------------------------------------------------

(II)
'以下を標準モジュールにコピペして下さい。
'---------------------------------------------------------------------

Sub FormOpen()
UserForm1.Show
End Sub

'---------------------------------------------------------------------

(III)
'以下をClass1モジュールにコピペして下さい。
'---------------------------------------------------------------------

Option Explicit

Private WithEvents CButton As MSForms.CommandButton
Private c_Index As Integer

Public Sub Add(ByVal ContainerClassObject As Object, _
Optional ByVal Left As Single = 0, _
Optional ByVal Top As Single = 0, _
Optional ByVal Height As Single = 12, _
Optional ByVal Width As Single = 21, _
Optional ByVal Caption As String, _
Optional ByVal Index As Long = 0)

'オブジェクト作成
With ContainerClassObject.Controls
Set CButton = .Add("Forms.CommandButton.1")
End With
'プロパティ設定
With CButton
.Top = Top
.Left = Left
.Height = Height
.Width = Width
.Caption = Caption
End With
Me.Index = Index
End Sub

Public Property Get Index() As Integer
Index = c_Index
End Property

Public Property Let Index(ByVal iNewValue As Integer)
c_Index = iNewValue
End Property

Public Property Get Top() As Single
Top = CButton.Top
End Property

Public Property Let Top(ByVal sNewValue As Single)
CButton.Top = sNewValue
End Property

Public Property Get Left() As Single
Left = CButton.Left
End Property

Public Property Let Left(ByVal sNewValue As Single)
CButton.Left = sNewValue
End Property

Public Property Get Height() As Single
Height = CButton.Height
End Property

Public Property Let Height(ByVal sNewValue As Single)
CButton.Height = sNewValue
End Property

Public Property Get Width() As Single
Width = CButton.Width
End Property

Public Property Let Width(ByVal sNewValue As Single)
CButton.Width = sNewValue
End Property

Public Property Get Caption() As String
Caption = CButton.Caption
End Property

Public Property Let Caption(ByVal sNewValue As String)
CButton.Caption = sNewValue
End Property

Public Sub CButton_Click()
MsgBox "このコマンドボタンのインデックスは " & Me.Index & " です"
End Sub

'---------------------------------------------------------------------

お礼

回答ありがとうございます。
ソースまで作っていただいて申し訳ありません。

提示いただいたソースを貼り付けてみたところ、正常動作致しました。
ソースの内容は単純そうなのですが、見たことの無い記述が多いので、
これから解析しようと思います。

ありがとうございました。

回答No.8

Wendy02

こんにちは。

#6の回答者です。

>「入力フォーム」とはどのようなものでしょうか？

シートに、いろんな種類のイベントやボタン、入力規則、コンボボックスなどを付けます。具体例を示すほどの必要性はないとは思いますが、ただ、場合によって、一つのシートの大きさを小さくして、ユーザーフォームのようにして、呼び出すこともします。

お礼

回答ありがとうございます。

ユーザーフォームとは別のフォームが存在するのかと思いましたが、
シートにボタン等を貼り付けて使うことを言うんですね。
実際に「コントロールツールボックス」というツールバーを出して貼り付けてみたのですが、
貼り付けてみたところでどうやって使うのか、というのがイマイチわかりません。
ユーザーフォームであれば「実行」をクリックすれば作ったフォームが起動し、ボタン等を押せるようになりますが、
シート上に貼り付けた場合は何をするとボタン等を押せるようになるのか（現状ボタンをクリックすると選択状態になるだけですから）、
という意味です。
そのあたりはもう少し調べ、疑問があれば別途質問させていただきます。
ありがとうございました。

回答No.6

Wendy02

こんばんは。

>最大の時の数が数百個にもなるので流石に諦めました

コントロール配列であっても、クラス・インスタンスであっても、その成功する見込みは低いはずです。コントロール・オブジェクトの本質は、メモリを占有するインスタンスですから、それを、100以上など作るなどは、ExcelのVBAで許されるメモリの範囲では、パンクするか、仮に動いても、重くてしょうがないです。

まず、本当の経験者に、設計の段階から相談することですね。
失敗してもよいなら、クラス・インスタンスの方法もあるけれども。

まず、リストボックスやコンボボックスを利用していくことと、コントロール・オブジェクトの使いまわしを考えます。だいたい、VBAが1～2年程度の経験者ですと、Excel で、UserForm を使おうとしますが、意外に、その勝手が悪いように思います。VBのように動いてくれません。私の想像ですが、ThisWorkbook をメインととて、UserFormのオブジェクトとしてのつながりが弱いからだと思います。プロの人なら気が付くはずですが、その点で不具合があります。

私なら、そのような状況下では、UserFormは使わないですね。最近も、途中まで、UserFormで作ってみたけれども、Access とは違う土台であることに気がついて、シートひとつを、入力フォームに換えることにしました。それなら、そこで、イベントやコントロールを貼り付けられますから、100個であろうが、200個であろうが、問題はなくなります。要するにイベントのタスクの内容によります。（こういう設計の段階からのことは、教えようがないのですが。）

お礼

回答ありがとうございます。

>>最大の時の数が数百個にもなるので流石に諦めました
これは「1個ずつ名前をつけ、配置するのが面倒」という意味であり、
動作の重さ等については考えていませんでした。
フォーム上に常に数百個のボタンが並ぶわけではなく、
タブなどを利用し、表示を切り替えることでいくつかのボタンを表示させるつもりでしたが、
そんなつくりであろうと動作速度には関係ないでしょうか（速度というよりはメモリ食いすぎで落ちそう、と聞こえましたが）。

> シートひとつを、入力フォームに換えることにしました。それなら、そこで、イベントやコントロールを貼り付けられますから、100個であろうが、200個であろうが、問題はなくなります。

「入力フォーム」とはどのようなものでしょうか？
現在、今回から使い始めたユーザーフォームの他には、
「標準モジュール」にコードを書き込み、
Excelのセルに値を入力することで動作させるような形しか使っていないのですが、
また別の形式があるのでしょうか。

回答No.5

marbin

参考になると思います。

http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays.htm

お礼

回答ありがとうございます。

少し読んでみたところ、
自分で検索したサイトよりはとっつきやすそうです。
勉強します。

回答No.4

Daiki_PaPa

時間がなく、細かく回答は出来ませんが、
出来るか出来ないかで言うと、出来ます。
クラスモジュールを利用します。
VBAには、コントロール配列はありませんが、
クラスモジュールで作成したコントロールを、
コントロール配列にすることも出来ます。

是非、クラスモジュールを勉強してみてください。

お礼

回答ありがとうございます。

「できる」という話は朗報でした！
が、ちらっと検索して読んでみた限りでは、かなり難しそうですね…。
もう少し読み進めてみます。

回答No.3

yama1718

No.2訂正です。
すみません、ワークシート上じゃなくて、ユーザーフォーム上の話ですね。

お礼

回答ありがとうございます。

ユーザーフォーム上の話です。
まだワークシート上でボタンを貼り付けたことはないのですが、
そちらなら可能らしい、ということを覚えておきます。

回答No.2

yama1718

OLEObjects.Add でできます。
試しに「マクロの記録」でコマンドボタンを作ってみたら、
それで記録されたコードを参考にしてマクロを組めばいいのです。

回答No.1

n-jun

事前にボタンを全て作っておいて、条件に応じて表示・非表示を行なうと言うのでは
ダメなのでしょうか？

お礼

回答ありがとうございます。
それは考えたのですが、最大の時の数が数百個にもなるので流石に諦めました。
（そんなものVBAで作るな、と言われそうですが）