Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > there must be a more simple way of doing this?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
IF formula-simple question; simple operator Rich D Excel Discussion (Misc queries) 4 December 6th 07 03:36 PM
Simple problem, simple formula, no FUNCTION ! Ron@Buy Excel Worksheet Functions 6 September 28th 07 04:51 PM
Simple Simple Excel usage question BookerW Excel Discussion (Misc queries) 1 June 23rd 05 10:06 PM
Make it more simple or intuitive to do simple things Vernie Charts and Charting in Excel 1 March 16th 05 04:01 AM
simple question, hopefully a simple answer! Matt B Excel Programming 5 January 13th 04 08:43 PM


All times are GMT +1. The time now is 03:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement