Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Please tell me how to make this more efficient and repeatable
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Please tell me how to make this more efficient and repeatable
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default Please tell me how to make this more efficient and repeatable

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

"noord453" wrote in message
...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default Please tell me how to make this more efficient and repeatable
Those truee parameters should of course be True

--
__________________________________
HTH

Bob

"Bob Phillips" wrote in message
...

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

"noord453" wrote in message
...
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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Please tell me how to make this more efficient and repeatable
Bob,

Thxs

Gerard

"Bob Phillips" wrote:


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

"noord453" wrote in message
...
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



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
How to get repeatable Excel RAND sequence? Joe User[_2_] Excel Worksheet Functions 12 April 22nd 10 01:50 PM
here is my code...it's running a bit slow though..any ideas to make it more efficient? thanks Zarlot531 Excel Programming 2 April 30th 07 02:12 PM
Repeatable Fixed Width Import donnie darko Excel Discussion (Misc queries) 9 December 6th 06 06:47 PM
How can I make this code more efficient? Sethaholic[_27_] Excel Programming 1 August 1st 06 05:15 PM
need to make code more efficient (if possible) Lilivati Excel Programming 8 July 7th 06 07:50 PM


All times are GMT +1. The time now is 08:18 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