ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   there must be a more simple way of doing this? (https://www.excelbanter.com/excel-programming/428359-there-must-more-simple-way-doing.html)

noord453
there must be a more simple way of doing this?
 
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

Jacob Skaria
there must be a more simple way of doing this?
 
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


--
If this post helps click Yes
---------------
Jacob Skaria


"noord453" wrote:

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

noord453
there must be a more simple way of doing this?
 
Jacob,

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

Thanks
Gerard

"Jacob Skaria" wrote:

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


--
If this post helps click Yes
---------------
Jacob Skaria


"noord453" wrote:

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


All times are GMT +1. The time now is 10:07 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com