there must be a more simple way of doing this?

N

noord453

Hi, below is some VB which I copied and pasted. I have the feeling it can be
done shorter and faster, since I have to do this for another 15 times. Anyone
who has suggestions?

regards
Gerard
Label78.Caption = Sheets("Input - components").Range("D5").Value
Label79.Caption = Sheets("Input - simple").Range("H8").Value
Label80.Caption = Sheets("Input - simple").Range("K8").Value
Label81.Caption = Sheets("Input - simple").Range("N8").Value
Label82.Caption = Sheets("Input - simple").Range("Q8").Value
Label83.Caption = Sheets("Input - simple").Range("T8").Value
Label84.Caption = Sheets("Input - components").Range("D5").Value
Label85.Caption = Sheets("Input - simple").Range("W8").Value
Label86.Caption = Sheets("Input - simple").Range("Z8").Value
Label87.Caption = Sheets("Input - simple").Range("AC8").Value
Label88.Caption = Sheets("Input - simple").Range("AF8").Value
Label89.Caption = Sheets("Input - simple").Range("AI8").Value

If Sheets("Input - simple").Range("h8").Value = "" Then
TextBox12.Visible = False
Else
TextBox12.Visible = True
End If
If Sheets("Input - simple").Range("k8").Value = "" Then
TextBox13.Visible = False
Else
TextBox13.Visible = True
End If
If Sheets("Input - simple").Range("n8").Value = "" Then
TextBox14.Visible = False
Else
TextBox14.Visible = True
End If
If Sheets("Input - simple").Range("q8").Value = "" Then
TextBox15.Visible = False
Else
TextBox15.Visible = True
End If
If Sheets("Input - simple").Range("t8").Value = "" Then
TextBox16.Visible = False
Else
TextBox16.Visible = True
End If
If Sheets("Input - simple").Range("w8").Value = "" Then
TextBox17.Visible = False
Else
TextBox17.Visible = True
End If
If Sheets("Input - simple").Range("z8").Value = "" Then
TextBox18.Visible = False
Else
TextBox18.Visible = True
End If
If Sheets("Input - simple").Range("ac8").Value = "" Then
TextBox19.Visible = False
Else
TextBox19.Visible = True
End If
If Sheets("Input - simple").Range("af8").Value = "" Then
TextBox20.Visible = False
Else
TextBox20.Visible = True
End If
If Sheets("Input - simple").Range("ai8").Value = "" Then
TextBox21.Visible = False
Else
TextBox21.Visible = True
End If
 
J

Jacob Skaria

Try the below and feedback... (untested)

Dim lngcol As Long
Dim intLabel As Integer
Dim intTextBox As Integer

intTextBox = 12
intLabel = 78
For lngcol = 8 To 35 Step 3
'Text Controls
If Sheets("Input - simple").Cells(8, lngcol).Value = "" Then
Me.Controls("TextBox" & intTextBox).Visible = False
Else
Me.Controls("TextBox" & intTextBox).Visible = True
End If
'Labels
If intLabel = 84 Then intLabel = 85
Me.Controls("Label" & intLabel).Caption = _
Sheets("Input - simple").Cells(8, lngcol).Value
'/
intLabel = intLabel + 1
intTextBox = intTextBox + 1
Next

Label78.Caption = Sheets("Input - components").Range("D5").Value
Label84.Caption = Sheets("Input - components").Range("D5").Value
 
N

noord453

Jacob,

It is working as you have provided the answer, no changes where needed.

Thanks
Gerard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Workbook_BeforeSave 8
Unhide sheet, copy and rename new sheets from list, rehide sheet 8
Code does not error but no copy either 4
Loop thru xx No. of sheets until the four strValue combo's are found 6
Copy row info but not in normal column sequence 6
Please tell me how to make this more efficient and repeatable 3
Macro's checking for errors 2

Top