Please tell me how to make this more efficient and repeatable

N

noord453

Hi I am looking into the VB programming now for 2 weeks, and I am learning
allday. However I have still problems in the actual programming, creating
forms is a part I am getting the hang of it, but for all other issues not. So
therefore please find below I have some VB for 2 textbox colums, in the form
there are 10 of those colums, how to make this code more efficient, and
repeatable.

Regards
Gerard

If Sheets("Input - components").Range("D5").Value = "" Then
TextBox37.Visible = False
TextBox38.Visible = False
TextBox39.Visible = False
TextBox40.Visible = False
TextBox41.Visible = False
TextBox42.Visible = False
TextBox43.Visible = False
TextBox44.Visible = False
TextBox45.Visible = False
TextBox46.Visible = False
TextBox47.Visible = False
TextBox48.Visible = False
TextBox49.Visible = False
TextBox50.Visible = False
TextBox51.Visible = False
TextBox52.Visible = False
TextBox53.Visible = False
TextBox54.Visible = False
TextBox55.Visible = False
TextBox56.Visible = False
TextBox57.Visible = False
TextBox58.Visible = False
TextBox59.Visible = False
TextBox60.Visible = False
TextBox61.Visible = False
TextBox62.Visible = False
TextBox63.Visible = False
TextBox64.Visible = False
TextBox65.Visible = False
TextBox66.Visible = False
TextBox67.Visible = False
TextBox68.Visible = False
TextBox69.Visible = False
Else
TextBox37.Visible = True
TextBox38.Visible = True
TextBox39.Visible = True
TextBox40.Visible = True
TextBox41.Visible = True
TextBox42.Visible = True
TextBox43.Visible = True
TextBox44.Visible = True
TextBox45.Visible = True
TextBox46.Visible = True
TextBox47.Visible = True
TextBox48.Visible = True
TextBox49.Visible = True
TextBox50.Visible = True
TextBox51.Visible = True
TextBox52.Visible = True
TextBox53.Visible = True
TextBox54.Visible = True
TextBox55.Visible = True
TextBox56.Visible = True
TextBox57.Visible = True
TextBox58.Visible = True
TextBox59.Visible = True
TextBox60.Visible = True
TextBox61.Visible = True
TextBox62.Visible = True
TextBox63.Visible = True
TextBox64.Visible = True
TextBox65.Visible = True
TextBox66.Visible = True
TextBox67.Visible = True
TextBox68.Visible = True
TextBox69.Visible = True
End If
If Sheets("Input - components").Range("E5").Value = "" Then
TextBox70.Visible = False
TextBox71.Visible = False
TextBox72.Visible = False
TextBox73.Visible = False
TextBox74.Visible = False
TextBox75.Visible = False
TextBox76.Visible = False
TextBox77.Visible = False
TextBox78.Visible = False
TextBox79.Visible = False
TextBox80.Visible = False
TextBox81.Visible = False
TextBox82.Visible = False
TextBox83.Visible = False
TextBox84.Visible = False
TextBox85.Visible = False
TextBox86.Visible = False
TextBox87.Visible = False
TextBox88.Visible = False
TextBox89.Visible = False
TextBox90.Visible = False
TextBox91.Visible = False
TextBox92.Visible = False
TextBox93.Visible = False
TextBox94.Visible = False
TextBox95.Visible = False
TextBox96.Visible = False
TextBox97.Visible = False
TextBox98.Visible = False
TextBox99.Visible = False
TextBox100.Visible = False
TextBox101.Visible = False
TextBox102.Visible = False
Else
TextBox70.Visible = True
TextBox71.Visible = True
TextBox72.Visible = True
TextBox73.Visible = True
TextBox74.Visible = True
TextBox75.Visible = True
TextBox76.Visible = True
TextBox77.Visible = True
TextBox78.Visible = True
TextBox79.Visible = True
TextBox80.Visible = True
TextBox81.Visible = True
TextBox82.Visible = True
TextBox83.Visible = True
TextBox84.Visible = True
TextBox85.Visible = True
TextBox86.Visible = True
TextBox87.Visible = True
TextBox88.Visible = True
TextBox89.Visible = True
TextBox90.Visible = True
TextBox91.Visible = True
TextBox92.Visible = True
TextBox93.Visible = True
TextBox94.Visible = True
TextBox95.Visible = True
TextBox96.Visible = True
TextBox97.Visible = True
TextBox98.Visible = True
TextBox99.Visible = True
TextBox100.Visible = True
TextBox101.Visible = True
TextBox102.Visible = True
End If
 
B

Bob Phillips

If Sheets("Input - components").Range("D5").Value = "" Then

Call SetTB(37, 69, False)
Else

Call SetTB(37, 69, truee)
End If

If Sheets("Input - components").Range("E5").Value = "" Then

Call SetTB(70, 102, False)
Else

Call SetTB(70, 102, truee)
End If

'rest of code in this proc

Private Sub SetTB(Min As Long, Max As Long, State As Boolean)
Dim i As Long

For i = Min To Max

Me.Controls("TextBox" & i).Visible = State
Next i
End Sub
 
N

noord453

Bob,

Thxs

Gerard

Bob Phillips said:
If Sheets("Input - components").Range("D5").Value = "" Then

Call SetTB(37, 69, False)
Else

Call SetTB(37, 69, truee)
End If

If Sheets("Input - components").Range("E5").Value = "" Then

Call SetTB(70, 102, False)
Else

Call SetTB(70, 102, truee)
End If

'rest of code in this proc

Private Sub SetTB(Min As Long, Max As Long, State As Boolean)
Dim i As Long

For i = Min To Max

Me.Controls("TextBox" & i).Visible = State
Next i
End Sub

--
__________________________________
HTH

Bob
Click to expand...
 
Top