エクセル、フォームでのVBAについて（コンボボックス）

エクセル９７でフォームのコンボボックスを使用する場合の質問です。
ワークシートにコンボボックスを１０個配置しました。
入力範囲はすべて同じです。
このコンボボックスで特定の項目（入力範囲６番目）を選んだ場合、次のマクロを実行するようにしようと思っています。マクロを１０個書いてそれぞれのコンボボックスに登録してもいいのですが、それよりはどのコンボボックスが使用されたか特定できればすべてのコンボボックスに一つのマクロで対応できます。
コンボボックスに番号（たとえばコンボ１～コンボ１０）を振ってその番号の取得ができればいいのですが、どのように記述したらよいのか検討がつきません。
お教えください。

No.7 ベストアンサー 回答者： noname#11856 回答日時： 2003/07/01 19:18

ゆん（yunn117）です。

まず、ByValですが、引数を値渡しするという宣言（？）です。
もうひとつ、ByRefというのがあって、それは参照渡しというモノになります。
・・・値渡しとか参照渡しってなに？というのを説明するべきなんでしょうが
説明がかなり苦手なので。。。申し訳ないです。(^-^;

で、検討がつかない、なんておっしゃってますが
Combo2では、ちゃんとNextMacro(2)って渡してるじゃないですか♪
カッコ内に入れた値が、NextMacro側に渡ってます。

ExcelのVBAのヘルプが見られる環境なら、ヘルプで調べてみてください。引数、ByVal、ByRefなど。

で、更なる質問部分についてですが
DrawingObjects("Combo1")といった表現をする場合はまとめられないんです。。。
が！
もう一度、考え直してみました。

Sub Combo1()
Call NextMacro(1)
End Sub

Sub NextMacro(ByVal SelCombo As Integer)
Dim ans As String
If Sheets("LOG").Range("C1") = False Then
Sheets("回答書").DrawingObjects("Combo" & SelCombo).ListIndex _
= Sheets("LOG").Range("H" & (30 + SelCombo)).Value
Exit Sub
End If
If Sheets("回答書").DrawingObjects("Combo" & SelCombo).ListIndex = 6 Then
ans = InputBox("地域を入れてください。", "地域設定")
Worksheets("LOG").Range("J" & SelCombo) = ans
End Sub

で、いいのでは。
ちゃんと全部キレイにまとまるやん♪
と、言いたいところなんですが、テストしてません。
動かなかったらごめんなさい。(^-^;;;

この回答へのお礼

ゆんさん、ありがとうございます！
最後のEnd Ifが抜けたのはご愛嬌で、あとは完璧です！
すばらし～っ！
これからもよろしくお願いしますね。

お礼日時：2003/07/01 19:35

No.8 回答者： imogasi 回答日時： 2003/07/01 23:57

ＶＢＡがイベントとして、貼りつけたコンボボックスのどれでもＣｌｉｃｋイベントなどが起こったとき、１ヶ所へ飛んできてくれて、コンボボックスのインデックス値をも、持ってきてくれれば良いのですが、以前のエクセルＶＢＡではそれを行ってくれません。

ですから根本的にはＶＢＡでは諦めざるを得ないと思います。あるいはＡＰＩの世界や、Ｃ＋＋の世界に入るか。
そこで個別のコンボボックスでイベントは捉えて（イベントプロセジュアーは１０個所作る）、そこで捉えた各コンボボックスのＩＤ即ちインデックスやＮａｍｅプロパティを引数で持ってこさせて、１箇所のモジュールに集めて、またそこでＣＡＳＥ文等で判別して、大部分共通の部分に飛ばすよりほかないのではないでしょうか。本問程度では
コーディング経済的にはあまり省略短縮を期待できないかもしれません。

この回答へのお礼

ありがとうございます。

お礼日時：2003/07/03 11:38

No.6 回答者： noname#11856 回答日時： 2003/07/01 15:46

#4です。

各コンボから呼び出すマクロは、やはりひとつずつ書くしかないように思います。
ただ、InputBoxを表示してコンボ毎に違うセルに書き出すというのはまとめられます。

Sub Combo1()
If Worksheets("回答書").DrawingObjects("Combo1").ListIndex = 6 Then NextMacro(1)
End Sub

Sub NextMacro(ByVal SelCombo As Integer)
Dim ans As String
ans = InputBox("地域を入れてください。", "地域設定")
Worksheets("LOG").Range("J" & SelCombo) = ans
End Sub

とすれば、少しはまとまりますよね。
今回は2行ほどなので、あまりまとまった気がしないかも知れませんが
これが10行、20行・・・となってきた場合は
修正箇所が少なくて済むし、まとめた意味がわかるかも。。。

この回答へのお礼

ありがとうございました！
ByVal SelComboの意味はぜんぜんわかりませんが以下のように書いてうまく行きました。
ところでByVal SelComboってなんなのでしょう？
Selcomboが変数なのはわかりますが、どうやって１～１０の数値を取得したのか見当がつきません。

それと、わがままを言いますが、最初のIFから Exit Sub End Ifの部分もまとめられないでしょうか？
Sub Combo1から10まで、セルC1は固定、H列は、H31～H40の連番です。

Sub Combo1()
If Sheets("LOG").Range("C1") = False Then
Sheets("回答書").DrawingObjects("Combo1").ListIndex _
= Sheets("LOG").Range("H31").Value
Exit Sub
End If
If Sheets("回答書").DrawingObjects("Combo1").ListIndex = 6 Then NextMacro (1)
End Sub

Sub Combo2()
If Sheets("LOG").Range("C1") = False Then
Sheets("回答書").DrawingObjects("Combo2").ListIndex _
= Sheets("LOG").Range("H32").Value
Exit Sub
End If
If Sheets("回答書").DrawingObjects("Combo2").ListIndex = 6 Then NextMacro (2)
End Sub

お礼でまた質問してしまい、すみません。

お礼日時：2003/07/01 17:34

No.5 回答者： happypoint 回答日時： 2003/07/01 15:34

こんにちは。

コントロール配列のようなものをやりたいのですか？

VisualBasicではコントロールを配列にできます
（ComboBox(1),
　ComboBox(2),
　ComboBox(3)...といった具合）

が、VBAにはコントロール配列はありません。

むりやり「擬似コントロール配列」を実現するコードも見たことがありますが、
かえって可読性が悪くなって、メンテナンスがしにくくなるような気がします。

AQUALINEさん、気持ちはわかるのですが
現状のやり方がベストだと思いますよ。

この回答へのお礼

ありがとうございます。
あんまり背伸びはしないほうがいいのかなあ。

お礼日時：2003/07/01 17:36

No.4 回答者： noname#11856 回答日時： 2003/07/01 13:23

呼び出すマクロに引数を付けたらいいのでは？

具体的には（呼び出すマクロがNextMacroという関数名だと仮定）

Sub NextMacro(ByVal SelCombo As Integer)

などとして、関数内ではSelComboでSelect Caseしてあげれば
もしくは、1ならC1、2ならC2・・・ならRange("C" & SelCombo).Value = 入力した値
などで出来ると思います。

・・・書いてるとなんかはずしてる気がしてくるなぁ(^-^;

この回答への補足

すみません、ぜんぜんわからないのです。
自作したのはこうです。

Sub Combo1()
Dim ans As String
If Worksheets("回答書").DrawingObjects("Combo1").ListIndex = 6 Then
ans = InputBox("地域を入れてください。", "地域設定")
Worksheets("LOG").Range("J1") = ans
End If
End Sub

Sub Combo2()
Dim ans As String
If Worksheets("回答書").DrawingObjects("Combo1").ListIndex = 6 Then
ans = InputBox("地域を入れてください。", "地域設定")
Worksheets("LOG").Range("J2") = ans
End If
End Sub

以下、マクロ名、コンボボックス名、転記先セルの数字が３～１０まで変るだけで、ぜんぶで１０個のマクロを書き、それぞれのコンボボックス（Combo1～Combo10）にマクロ（１～１０）を登録しました。これでちゃんと動きますが、もっといい方法を覚えたかったのです。

補足日時：2003/07/01 14:34

No.3 回答者： taknt 回答日時： 2003/07/01 13:13

Private Sub ComboBox1_Change()

End Sub

や
Private Sub ComboBox1_Click()

End Sub
などのイベントに記述したらどうでしょうか？
なお、それぞれ
Private Sub ComboBox1_Click() から
Private Sub ComboBox10_Click()
まで イベントを作らないといけませんが。

Private Sub Worksheet_Change(ByVal Target As Range)
は、変更時に発生するイベントで
これで
Worksheets("シート名").OLEObjects("ComboBox" & no).ListIndex
で ループさせて コンポボックス１から１０の値を配列に取得し、以前取得した内容と比較して、変更されたか
チェックしてもいいと思います。

この回答への補足

何度もすみません。
コンボボックスは「コントロールツールボックス」ではなく「フォーム」で作ったものです。（コントロールツーツボックスの使い方がいまいちわからないため、あいかわらず９５時代のフォームなのです。とほほ）
従って、イベントというとらえ方は出来ないのです。

補足日時：2003/07/01 13:21

No.2 回答者： taknt 回答日時： 2003/07/01 12:03

noという変数には、

コンボ１～コンボ１０の1から10までをセットしたらいいでしょう。

あと、どのコンボボックスというのは、イベントかな？

この回答への補足

わたしの説明が悪くてすみません。
フォームで作成したコンボボックスが同一シートに１０個あるのです。（名前は ComboBox 1～ComboBox 10）
それぞれが参照している範囲はすべて同一。リンク先のセルはすべて違います。
これら10個のコンボボックスすべてにつき、ドロップダウンリストの６番目が選択されたときだけマクロを作動させたいのです。
具体的には６番目は「その他」という項目なのでインプットボックスを表示して入力させ、ComboBox 1の場合はセルC1に、ComboBox の場合はセルC2に転記したいのです。
お手数ですがよろしくお願いします。

補足日時：2003/07/01 12:45

No.1 回答者： taknt 回答日時： 2003/07/01 11:13

Worksheets("シート名").OLEObjects("ComboBox" & no).ListIndex

上記で no に ComboBoxの番号を入れればいいでしょう。
上記を応用して用いてみてください。

この回答への補足

さっそくありがとうございます。試しに
Sub test()
MsgBox Worksheets("回答書").OLEObjects("ComboBox" & no).ListIndex
End Sub

とやってみました(コンボボックスにマクロを登録)が、「WorksheetクラスのOLEObjectsプロパティが取得できません」という「実行時エラー」になってしまいます。

補足日時：2003/07/01 11:39