こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

下記プロシージャで異常な値のテキストボックスだけを、メッセージに表示させようと狙っていましたが、いつも、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

投稿日時 - 2012-11-24 22:11:20

QNo.7814191

困ってます

質問者が選んだベストアンサー

#3、cjです。
訂正が一件あります。(最後の方)

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

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

投稿日時 - 2012-11-26 23:28:04

お礼

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

投稿日時 - 2012-12-11 19:44:43

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

こんにちは。

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

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 に 0値(ゼロ)を入力することが想定される場合や、
  カンマ区切りで数値を入力する場合は、
  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がどうなるのか忘れてしまいました。
この話は、現在のブックを使い続けるならば、という但し書を付けておきますね。

とりあえず、以上です。

投稿日時 - 2012-11-26 22:45:14

ANo.2

B2セルからB15セルにはコントロールの名称が入力されているとしていますがControlsの使い方が間違っています。
下のマクロではControlsはユーザーフォームにあるテキストボックスの番号を1番から14番まで変えるために使っています。
テキストボックス1は時無の値段が入力されていますね。その値段が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

投稿日時 - 2012-11-26 07:05:15

お礼

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

投稿日時 - 2012-12-11 19:38:57

ANo.1

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

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

投稿日時 - 2012-11-25 14:05:14

お礼

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


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

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

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

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

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

投稿日時 - 2012-11-25 19:37:17

あなたにオススメの質問