excel2000vba コントロールの値チェック

質問

写真のように、ユーザーフォームの、各テキストボックスに数字を入れるようにしています。

そして、それぞれの、テキストボックスには、数字の下限値と上限値が設けられており、それに満たない数字が入力された場合、登録ボタンを押したときに、メッセージでそのテキストボックス名称を表示させるようにしようとしたいです。

また、値の下限値、コントロール名称、値の上限値は、workwheet1 のＡ列、Ｂ列、Ｃ列にそれぞれ入力されています。

下記プロシージャで異常な値のテキストボックスだけを、メッセージに表示させようと狙っていましたが、いつも、worksheet1の最下行のコントロールだけが、異常ですというメッセージが出てしまいます。

記述をどのように修正すれば、また、もっといい記述があれば、なども併せてアドバイスいただけないでしょうか。よろしくお願いします。

Private Sub CommandButton1_Click()
Dim myctl As Object
Dim endrow As Long
Dim i As Long
Dim myMSG As String

endrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
For Each myctl In Controls
For i = 2 To endrow
If InStr(1, myctl.Name, Range("B" & i).Text) < Range("A" & i) Or InStr(1, myctl.Name, Range("B" & i).Text) > Range("C" & i) Then
myMSG = Range("B" & i).Text & vbCrLf
End If
Next
Next
MsgBox myMSG & vbCrLf & "が異常です。"
End Sub

回答No.4

cj_mover · ベストアンサー

＃３、ｃｊです。
訂正が一件あります。（最後の方）

Private Sub UserForm_Click()
Dim myctl As Object
Dim i As Long
　　Debug.Print "Index", "Name", "TypeName"
　　For Each myctl In Controls
　　　　Debug.Print i, myctl.Name, TypeName(myctl)
　　　　i = i + 1
　　Next
End Sub

でした。
単純な転記（編集）ミスです。失礼しました。

お礼

ご返事が遅れて、申し訳ありません。
分かりやすく、記載していただき、助かりました。
ありがとうございました。

回答No.3

cj_mover

こんにちは。

まず、２回以上出てくる記述は変数に纏める、という書き方で、

Private Sub CommandButton1_Click()
Dim r As Range
Dim sCtrlNm As String
Dim myMSG As String
Dim valTmp
Dim endrow As Long
　　endrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
　　For Each r In Range("B2:B" & endrow)
　　　　sCtrlNm = r.Value
　　　　valTmp = Val(Controls(sCtrlNm).Value)
　　　　If valTmp < r.Offset(, -1).Value Or valTmp > r.Offset(, 1).Value Then
　　　　　　myMSG = myMSG & sCtrlNm & vbCrLf
　　　　End If
　　Next r
　　If myMSG <> "" Then MsgBox myMSG & vbCrLf & "が異常です。"
End Sub

次に変数を追加せずに書いてみると、

Private Sub CommandButton1_Click()
Dim r As Range
Dim myMSG As String
Dim endrow As Long
　　endrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
　　For Each r In Range("B2:B" & endrow)
　　　　If Val(Controls(r.Value)) < r.Offset(, -1).Value Or Val(Controls(r.Value)) > r.Offset(, 1).Value Then
　　　　　　myMSG = myMSG & r.Value & vbCrLf
　　　　End If
　　Next r
　　If myMSG <> "" Then MsgBox myMSG & vbCrLf & "が異常です。"
End Sub

（※TextBox に ０値（ゼロ）を入力することが想定される場合や、
　　カンマ区切りで数値を入力する場合は、
　　Val関数の代わりの記述を考える必要があります。）

一応、↑こんな感じでしょうか？
"コントロール名称"というのが正しくControl.Nameと完全に一致するという前提です。
シートの保護をする、など前提に狂いが生じないよう注意は必要ですね。

ControlsをループしてControl.Nameをセル範囲から探すのは非効率的なので
セル範囲をループしてControls("コントロール名称")でTextBoxにアクセスした方が一手間省けます。

ご提示のコードは、"コントロール名称"にControl.Nameが含まれる場合、という着想で書かれたものと読めます。
仮にそうだとすると、（複数ヒットする場合が生じるので）名称が不適切ですし、
質問文を読む限りでは"コントロール名称"は一意なControl.Nameに対応していると読む方が自然なので、
ご提示のコードへの解釈よりも全体を見て解釈換えしたものを提示しました。

それから、
UserFormの余白をクリックして、以下の結果をイミディエイトウィンドウで確認してみてください。

Private Sub UserForm_Click()
Dim myctl As Object
Dim i As Long
　　Debug.Print "Index", "Name", "TypeName"
　　For Each myctl In Controls
　　　　i = i + 1
　　　　Debug.Print i, myctl.Name, TypeName(myctl) 'myctl.Type
　　Next
End Sub

Controls(index)という記述でそれぞれアクセスできるので、場合によっては活用できるかも知れません。
原則、Userformのコントロールを追加した順番に、Controlsコレクション内のIndexが決まります。
Userformを他のブックにエクスポートしたりした場合はIndexがどうなるのか忘れてしまいました。
この話は、現在のブックを使い続けるならば、という但し書を付けておきますね。

とりあえず、以上です。

回答No.2

KURUMITO

B2セルからB15セルにはコントロールの名称が入力されているとしていますがControlsの使い方が間違っています。
下のマクロではControlsはユーザーフォームにあるテキストボックスの番号を1番から14番まで変えるために使っています。
テキストボックス１は時無の値段が入力されていますね。その値段がA2セルの値より小さいか、C2の値段より大きい場合には時無が異常ですと表示されますね。同じようにテキストボックス2では・・・・となっていきます。

Private Sub CommandButton1_Click()
Dim endrow, i As Integer
Dim myVal As Long
Set WS1 = Worksheets("Sheet1")

For i = 2 To 15
myVal = UserForm1.Controls("TextBox" & (i - 1)).Text * 1
If myVal < WS1.Range("A" & i).Value Or myVal > WS1.Range("C" & i).Value Then
MsgBox WS1.Range("B" & i).Value & "が異常です。"
End If
Next
End Sub

お礼

ご返事が遅れて、申し訳ありません。
大変参考になりました。
ありがとうございました。

回答No.1

KURUMITO

次のようなマクロにすればよいでしょう。

Private Sub CommandButton1_Click()
Dim endrow, i As Integer
Dim myVal As Long
Set WS1 = Worksheets("Sheet1")

endrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To endrow
myVal = UserForm1.Controls("TextBox" & (i - 1)).Text * 1
If myVal < WS1.Range("A" & i).Value Or myVal > WS1.Range("C" & i).Value Then
MsgBox WS1.Range("B" & i).Value & "が異常です。"
End If
Next
End Sub

お礼

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

7行目の
myVal = UserForm1.Controls("TextBox" & (i - 1)).Text * 1

ですが、コントロールの名称を変えており、B2：B15間の名前で利用しているため

myVal = UserForm1.Controls(Range("B" & i).Text).Text * 1

と修正してみましたが、エラーとなってしまいました。

この場合、どう修正すればいいかもご教授いただけないでしょうか。