写真のように、ユーザーフォームの、各テキストボックスに数字を入れるようにしています。
そして、それぞれの、テキストボックスには、数字の下限値と上限値が設けられており、それに満たない数字が入力された場合、登録ボタンを押したときに、メッセージでそのテキストボックス名称を表示させるようにしようとしたいです。
また、値の下限値、コントロール名称、値の上限値は、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
No.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
回答ありがとうございます。
7行目の
myVal = UserForm1.Controls("TextBox" & (i - 1)).Text * 1
ですが、コントロールの名称を変えており、B2:B15間の名前で利用しているため
myVal = UserForm1.Controls(Range("B" & i).Text).Text * 1
と修正してみましたが、エラーとなってしまいました。
この場合、どう修正すればいいかもご教授いただけないでしょうか。
No.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
No.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がどうなるのか忘れてしまいました。
この話は、現在のブックを使い続けるならば、という但し書を付けておきますね。
とりあえず、以上です。
No.4ベストアンサー
- 回答日時:
#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
でした。
単純な転記(編集)ミスです。失礼しました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA 重複チェック後に値をワークシートに転記する方法を教えてください。 1 2023/03/19 12:43
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) VBAでoutlook365が起動しません。 4 2022/08/25 13:31
- Visual Basic(VBA) Excel VBA キーワードから列を取得して、さらに空欄行を非表示にする 3 2022/10/21 22:49
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
- Visual Basic(VBA) excel vbaでvlooupの変数がわかりません。 7 2022/05/30 09:35
- Visual Basic(VBA) 稀に1円合いません? Sheet1から金額と個数を貼り付ける下記コードで、金額を切り上げるコードを何 3 2022/09/05 15:11
- Visual Basic(VBA) VBAが止まります。 3 2022/08/31 14:09
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
Microsoft1Officeの互換ソフト...
-
Excel ピボットテーブルで日付...
-
エクセル関数を教えてください
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
LOOKUP関数を使えばいいのでし...
-
エクセル 白黒印刷で白線を印刷...
-
【関数】先頭だけにある、半角...
-
【関数】適切な文字数の数字を...
-
Excelのチェックボックスの使い...
-
エクセルでの作業計算方法について
-
Excelのpivotについて質問です
-
WPS OFFICEでの縦書きについて
-
時間によってファイル名が変わ...
-
エクセルのセルに同じ大きさの...
-
Aというブックの1というシート...
-
エクセルの順位別一覧表の自動...
-
西暦や和暦の表示をyyyymmdd表...
-
【マクロ】エクセルにかいてあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報