VBAで入力規則の設定

質問

いつもお世話になっております。
VBAを勉強して3ヶ月程度の初心者です。

VBAでの入力規則の設定について質問させていただきます。

A1セルに入力規則の設定を行い、0～10までの数値を選択できるようにしたいと考えています。

Sub Test()
Range("A1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation _
, Formula1:="0,1,2,3,4,5,6,7,8,9,10"
End Sub

とコードを記述すれば、確かに望みの仕様は満たされるのですが、複数のセルに入力規則を設定する場合、
また、リストの数が増えた場合を考えて、"1,2,3,4,5,6,7,8,9,10"を変数に出来ない？と考えて・・・
Sub Test2()
Dim kazu(10) As Long
kazu(0) = 0
kazu(1) = 1
kazu(2) = 2
kazu(3) = 3
kazu(4) = 4
kazu(5) = 5
kazu(6) = 6
kazu(7) = 7
kazu(8) = 8
kazu(9) = 9
kazu(10) = 10

Range("A2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation _
, Formula1:=kazu
End Sub
なんて事もやってみましたが・・・駄目でした。

もう一点、初期値に0を表示したい場合は、入力規則コードの後に、

　Range("A1").Value=0
と表示すればよいのでしゅか？

ご指導をよろしくお願いいた

回答No.4

keithin · ベストアンサー

入力規則のリストネタを、カンマ区切りの文字列で作成するだけです。

シンプルに：
sub macro1()
　dim i as integer
　dim buf as string

　for i = 0 to 10
　　buf = buf & "," & i
　next i
　buf = mid(buf,2)

　range("A1").validation.delete
　range("A1").validation.add type:=xlvalidatelist, formula1:=buf
　range("A1") = 0
end sub

＃参考
＞初期値に0を表示したい場合は、入力規則コードの後に、

入力規則コードの「前」に初期値を記入することで、入力規則で「許可していない値を初期値として」セットする事もできます。

お礼

ありがとうございます。

回答No.3

classical

配列を使わなくても、リスト用シートを作成した方が早いのではないでしょうか？

　例えば、sheet2のＡ１に１、Ａ２に２・・・
とリストを作成しておいて、そこから参照すれば、リスト項目が増えた場合でも対応できます。

　なお、質問文ではセルを選択していますが、任意の場所（選択セル（複数可））
に対して実行できるようにしています。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

Sub sample()

Dim ListWS As Worksheet
Set ListWS = Worksheets("sheet2") 'リスト用シート
Dim MyRg As Range
Set MyRg = Selection '選択しているセルを格納

ListWS.Range("A1").CurrentRegion.Name = "MyList" 'リスト用シートに名前を付ける
　 'currentregionとは、選択しているセルを含む、空白で囲まれた範囲

With MyRg.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, _
Formula1:="=MyList"
MyRg.Value = 0
End With

End Sub

でどうでしょうか？

お礼

ありがとうございます。

回答No.2

watabe007

>もう一点、初期値に0を表示したい場合は、
常にA1セルを選択した時は0を表示したいのであれば
シートモジュールに以下のコードを
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　With Target
　　　　If .Address = "$A$1" Then .Value = 0
　　End With
End Sub

お礼

ありがとうございます。

回答No.1

watabe007

配列kazuに収められている数値を","で繋ぐ必要があります。
ここではJoin関数を使いましたので変数の型はLongではエラーが出るので
Variantにしています。

Dim kazu(10) As Variant, i As Long
For i = 0 To 10
　　kazu(i) = i
Next
Range("A2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation _
, Formula1:=Join(kazu, ",")

>Range("A1").Value=0
>と表示すればよいのでしゅか？
良いのでは

お礼

ありがとうございます。