Excel VBAで入力規則リストに記述できる関数

入力規則のリストで、ユーザー定義関数を使ったリストにしたいのですが、どうやれば良いのでしょうか？
使い方としては、レジストリの内容をユーザー定義関数経由でリストで選択させるようなことをやりたいと思っています。
入力規則の設定で「元の値」に、ユーザー定義関数を記述してもうまくいきませんでした。
多分、関数の戻り値が悪いのだろうと思うのですが、どのような戻り値にすれば良いか、お教えください

No.3 回答者： end-u 回答日時： 2012/03/01 00:10

入力規則のリストに、ユーザー定義関数は使えないんじゃなかったかなーと思います。

[名前の定義]経由でも『元の値はエラーと判断されます..』状況です。


..と、思ったんですけど、シートを経由して[名前の定義]を使えば何とかなるかな..と。
でもかなりトリッキーな手法です。

1)まずテスト値をセット。
　例えばレジストリの"VB and VBA Program Setting"配下にテスト値を設定します。

　Sub test()
　　　SaveSetting "MyMacro", "Main", "Data", "a,b,c"
　End Sub
　#後で掃除してくださいね。

2)ダミーシートを追加し、"dummy"という名前をつける。

3)標準モジュールにFunction追加。

　Function GetTestData() As Range
　　　Dim r As Range
　　　Dim s

　　　s = Split(GetSetting("MyMacro", "Main", "Data"), ",")
　　　Set r = Sheets("dummy").Range("A1").Resize(, UBound(s) + 1)
　　　r.Value = s
　　　Set GetTestData = r
　End Function

　この内容は、カンマ区切り文字列のレジストリテスト値を読み込んで
　Splitで分割し、"dummy"シートのA1:C1に値を書き込むというものです。

4)[名前の定義]を設定する。
　名前：　　　LIST
　参照範囲：　=GetTestData()

5)任意のセルに入力規則リスト設定。
　元の値：=　LIST
　『元の値はエラーと判断されます..』メッセージが出るけど無視。


GetTestDataの戻り値をRangeにし、シートと[名前の定義]を経由する事で無理矢理設定します。
シート経由というのが引っ掛かりますが、まあ、非表示にしておくという手も。
GetTestDataの中で"dummy"シートに書きこんでますから、ワークシート関数としては使えません。
それに、入力規則リストにアクセスする度にGetTestDataが走りますから
"dummy"シートも都度書き換わります。
あまり現実的ではないような気がしますね。

#もしかしたらこんなトリッキーな方法でなくても可能なんですかね？
#ご存知の方いらっしゃるかもしれませんのでしばらく待ってみてください。


ユーザー定義関数にこだわらなくてもいい場合は
Workbook_Openイベントなどで"dummy"シートに書き出したほうが遥かにラクです。



>#後で掃除してくださいね。
の分でテストキーの削除。
Sub test2()
　　Dim s As String

　　s = GetSetting("MyMacro", "Main", "Data")
　　If Len(s) > 0 Then
　　　　DeleteSetting "MyMacro", "Main", "Data"
　　End If
End Sub

この回答へのお礼

回答、有難うございます。

早速やってみましたが、シートを経由するのが、ちょっと辛いですね
せめて、「名前の定義」だけで出来れば良いのですが．．．

もう少し、待ってみます。

有難うございます

お礼日時：2012/03/01 22:25

No.2 回答者： kiriG 回答日時： 2012/02/27 18:43

リストボタン（ボタンをクリックすると）を作る方法ですね！

※　指定セル（ここでは　B3）をクリックすると　セルの右側にリストボタン（▼）が現れ
　　リストボタンをクリックすると　設定されているリスト（複数のデーター）が表示され
　　適応するテーターをクリックして入力する方法

この事であれば、簡単です。

添付画像を見ながら設定してください

１）　リスト元（H1..H4）を入力します。

　　　H2＝空白　（空白データーは必要と思います）
　　　H3=AAA　　（１個目）
　　　H4=BBB　　（２個目）
　　　H5=CCC　　（３個目）

２）　セル　B3　をクリック
３）　メニューバーより　　データー　⇒　入力規則　⇒　設定　タブ　を選択
　　　入力値の種類　▼　をクリックして　リスト　を選択

　　　元の値　枠の右側　赤い矢印をクリック
　　　セル　H2　をクリック
　　　SHIFT　キーを押しながら　セル　H5　をクリック

　　　データー入力規則の窓を閉じて　「OK」　をクリック

　　　これで設定完了

４）　セル　B3　をクリックして　セル右側の　リストボタン（▼）をクリックして
　　　リスト表示をして　適応するリストを選択して　クリック

５）　複数のセルに適応したいのであれば
　　　複写ポインター（設定されたセルの右下の小さな○）を目的の方向へドラッグしてください

以上

この回答へのお礼

回答、有難うございます

それを直接、ユーザー定義関数でやりたいのです。

つまり、VBAマクロで、
Public Function GetTestData()
：
（処理は省略）
：
End Function
と、作成したものを
メニューバーよりデータ⇒入力規則⇒設定で「入力値の種類」を「リスト」を選択して
さらに「元の値」に「=GetTestData()」とやりたいのです。

このままやると、エラーになりリストにも表示されないので、どのようなVBAマクロを作ればいいのでしょうか？

お礼日時：2012/02/27 21:38

No.1 回答者： kiriG 回答日時： 2012/02/27 01:05

もう少し　具体的に　何がしたいのかを　教えてください

どのような　入力　規則を　したいのでしょう～

VBA　でプログラムすれば　どんな　ことでも適用できますが・・・

■質問

メニューバー　から　　データー　→　入力規則　→　入力値の種類を「ユーザー定義」二変更して

数式の枠に　入力する方法で　しているのでしょうか？


ツール　→　マクロ　→　Visual Basic Editor （VBA）を選択して　プログラムとして記述すれば

問題は、解決できると思います。

でも　少し勉強しないと　無理かも

どのように　したいのかが　解かればアドバイスも　可能ですが・・・

VBAの詳しいことは　参考URLを見てください。

参考URL：http://excelvba.pc-users.net/index.html

この回答へのお礼

回答有難うございます。

メニューバー　から　　データー　→　入力規則　→　入力値の種類を「ユーザー定義」ではなく、
メニューバー　から　　データー　→　入力規則　→　入力値の種類を「リスト」でやりたいのです。

「ユーザー定義」に記述するようなユーザー定義関数や、
セルに記述するようなユーザー定義関数は作成できるのですが、
リストの「元の値」に入力できるような関数が作れなくて、困っています。

ご存知だったら、お教えください

お礼日時：2012/02/27 07:16