|
▼Jaka さん:
>何がやりたいのか良く解らないけど。
>
>for i = 1 to 7
>msgbox Me.Controls("TextBox" & i).value
>Next
シート内のデータの有無でCheckboxやTextboxの表示の切り替えをしたかったのですが、
With Workbooks("データ").Worksheets("メンテナンス")
For a = 1 To 8
If .Cells(4 + a, 2) = "○" Then
Select Case a
Case Is = 1
CheckBox1.Visible = True
TextBox2.Visible = True
TextBox10.Visible = True
Case Is = 2
CheckBox2 = True
TextBox3.Visible = True
TextBox11.Visible = True
Case Is = 3
CheckBox3 = True
TextBox4.Visible = True
TextBox12.Visible = True
Case Is = 4
CheckBox4 = True
TextBox5.Visible = True
TextBox13.Visible = True
Case Is = 5
CheckBox5 = True
TextBox6.Visible = True
TextBox14.Visible = True
Case Is = 6
CheckBox6 = True
TextBox7.Visible = True
TextBox15.Visible = True
Case Is = 7
CheckBox7 = True
TextBox8.Visible = True
TextBox16.Visible = True
Case Is = 8
CheckBox8 = True
TextBox9.Visible = True
TextBox17.Visible = True
End Select
End If
Next a
For a = 1 To 8
If .Cells(14 + a, 2) = "○" Then
Select Case a
Case Is = 1
CheckBox9 = True
TextBox19.Visible = True
TextBox27.Visible = True
Case Is = 2
CheckBox10 = True
TextBox20.Visible = True
TextBox28.Visible = True
Case Is = 3
CheckBox11 = True
TextBox21.Visible = True
TextBox29.Visible = True
Case Is = 4
CheckBox12 = True
TextBox22.Visible = True
TextBox30.Visible = True
Case Is = 5
CheckBox13 = True
TextBox23.Visible = True
TextBox31.Visible = True
Case Is = 6
CheckBox14 = True
TextBox24.Visible = True
TextBox32.Visible = True
Case Is = 7
CheckBox15 = True
TextBox25.Visible = True
TextBox33.Visible = True
Case Is = 8
CheckBox16 = True
TextBox26.Visible = True
TextBox34.Visible = True
End Select
End If
Next a
以下略
Select caseを使って1個1個書くのは大変なのでいっぺんにできないかと思って質問しました。
今回教えてもらった方法を参考にし、下記の短いコードで同じことができるようになりました。ありがとうございました。
Private Sub UserForm_Initialize()
Dim a As Integer, i As Integer, b As Integer, c As Integer
x = 1
b = 1
c = 2
For i = 4 To 64 Step 10
For a = 1 To 8
If Cells(i + a, 2) = "○" Then
Me.Controls("CheckBox" & b).Visible = True
Me.Controls("TextBox" & c).Visible = True
Me.Controls("TextBox" & c + 8).Visible = True
End If
b = b + 1
c = c + 1
Next a
c = c + 9
Next i
End Sub
|
|