Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Long-winded code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Long-winded code
I have written 2 blocks of code to Initialise a User Form: the first defines
a range for a Worksheet Function (Max) and the second performs a test to
determine which of 9 buttons, should be visible. The User Form is named
TiedVote, and the Buttons B1 to B9.

My code works, but is long-winded. Can anyone suggest a method to achieve
the same functionality but simplify the repetitive code?

Private Sub UserForm_Initialize()

Select Case RoundN
Case 1
Set Voting = Worksheets("Rules").Range("K17:K25")
Case 2
Set Voting = Worksheets("Rules").Range("L17:L25")
Case 3
Set Voting = Worksheets("Rules").Range("M17:M25")
Case 4
Set Voting = Worksheets("Rules").Range("N17:N25")
Case 5
Set Voting = Worksheets("Rules").Range("O17:O25")
Case 6
Set Voting = Worksheets("Rules").Range("P17:P25")
Case 7
Set Voting = Worksheets("Rules").Range("Q17:Q25")
End Select*

If Sheets("Rules").Cells(17, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B1.Visible = True
Else
TiedVote.B1.Visible = False
End If
If Sheets("RULES").Cells(18, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B2.Visible = True
Else
TiedVote.B2.Visible = False
End If
If Sheets("RULES").Cells(19, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B3.Visible = True
Else
TiedVote.B3.Visible = False
End If
If Sheets("RULES").Cells(20, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
Else
TiedVote.B4.Visible = False
End If
If Sheets("RULES").Cells(21, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B5.Visible = True
Else
TiedVote.B5.Visible = False
End If
If Sheets("RULES").Cells(22, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B6.Visible = True
Else
TiedVote.B6.Visible = False
End If
If Sheets("RULES").Cells(23, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B7.Visible = True
Else
TiedVote.B7.Visible = False
End If
If Sheets("RULES").Cells(24, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B8.Visible = True
Else
TiedVote.B8.Visible = False
End If
If Sheets("RULES").Cells(25, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B9.Visible = True
Else
TiedVote.B9.Visible = False
End If

End Sub


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 493
Default Long-winded code
One way:

As long as RoundN can only take on the integral values 1 through 7:

Dim compRng As Variant
Dim maxVoting As Double
With Sheets("Rules").Cells(17, 10 + RoundN).Resize(9, 1)
compRng = .Value
maxVoting = Application.Max(.Cells)
End With
With TiedVote
.B1.Visible = compRng(1, 1) = maxVoting
.B2.Visible = compRng(2, 1) = maxVoting
.B3.Visible = compRng(3, 1) = maxVoting
.B4.Visible = compRng(4, 1) = maxVoting
.B5.Visible = compRng(5, 1) = maxVoting
.B6.Visible = compRng(6, 1) = maxVoting
.B7.Visible = compRng(7, 1) = maxVoting
.B8.Visible = compRng(8, 1) = maxVoting
.B9.Visible = compRng(9, 1) = maxVoting
End With


In article ,
"Hotbird" wrote:

I have written 2 blocks of code to Initialise a User Form: the first defines
a range for a Worksheet Function (Max) and the second performs a test to
determine which of 9 buttons, should be visible. The User Form is named
TiedVote, and the Buttons B1 to B9.

My code works, but is long-winded. Can anyone suggest a method to achieve
the same functionality but simplify the repetitive code?

Private Sub UserForm_Initialize()

Select Case RoundN
Case 1
Set Voting = Worksheets("Rules").Range("K17:K25")
Case 2
Set Voting = Worksheets("Rules").Range("L17:L25")
Case 3
Set Voting = Worksheets("Rules").Range("M17:M25")
Case 4
Set Voting = Worksheets("Rules").Range("N17:N25")
Case 5
Set Voting = Worksheets("Rules").Range("O17:O25")
Case 6
Set Voting = Worksheets("Rules").Range("P17:P25")
Case 7
Set Voting = Worksheets("Rules").Range("Q17:Q25")
End Select*

If Sheets("Rules").Cells(17, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B1.Visible = True
Else
TiedVote.B1.Visible = False
End If
If Sheets("RULES").Cells(18, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B2.Visible = True
Else
TiedVote.B2.Visible = False
End If
If Sheets("RULES").Cells(19, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B3.Visible = True
Else
TiedVote.B3.Visible = False
End If
If Sheets("RULES").Cells(20, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
Else
TiedVote.B4.Visible = False
End If
If Sheets("RULES").Cells(21, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B5.Visible = True
Else
TiedVote.B5.Visible = False
End If
If Sheets("RULES").Cells(22, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B6.Visible = True
Else
TiedVote.B6.Visible = False
End If
If Sheets("RULES").Cells(23, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B7.Visible = True
Else
TiedVote.B7.Visible = False
End If
If Sheets("RULES").Cells(24, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B8.Visible = True
Else
TiedVote.B8.Visible = False
End If
If Sheets("RULES").Cells(25, 10 + RoundN).Value = _
Application.WorksheetFunction.Max(Voting) Then
TiedVote.B9.Visible = True
Else
TiedVote.B9.Visible = False
End If

End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Long-winded code
Thank you for your reply. It worked like a dream....


"J.E. McGimpsey" wrote in message
...
One way:

As long as RoundN can only take on the integral values 1 through 7:

Dim compRng As Variant
Dim maxVoting As Double
With Sheets("Rules").Cells(17, 10 + RoundN).Resize(9, 1)
compRng = .Value
maxVoting = Application.Max(.Cells)
End With
With TiedVote
.B1.Visible = compRng(1, 1) = maxVoting
.B2.Visible = compRng(2, 1) = maxVoting
.B3.Visible = compRng(3, 1) = maxVoting
.B4.Visible = compRng(4, 1) = maxVoting
.B5.Visible = compRng(5, 1) = maxVoting
.B6.Visible = compRng(6, 1) = maxVoting
.B7.Visible = compRng(7, 1) = maxVoting
.B8.Visible = compRng(8, 1) = maxVoting
.B9.Visible = compRng(9, 1) = maxVoting
End With


In article ,
"Hotbird" wrote:

I have written 2 blocks of code to Initialise a User Form: the first
defines
a range for a Worksheet Function (Max) and the second performs a test to
determine which of 9 buttons, should be visible. The User Form is named
TiedVote, and the Buttons B1 to B9.

My code works, but is long-winded. Can anyone suggest a method to
achieve
the same functionality but simplify the repetitive code?


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
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo Max Loger Excel Discussion (Misc queries) 0 June 14th 11 04:30 PM
Replace html code - formula too long andy62 Excel Discussion (Misc queries) 1 February 24th 09 09:48 PM
Code takes to long Oggy Excel Discussion (Misc queries) 1 March 30th 08 10:56 PM
Long Code Alam Excel Discussion (Misc queries) 2 November 15th 07 08:46 AM
my code too long? Zach Excel Worksheet Functions 2 August 3rd 05 07:53 PM


All times are GMT +1. The time now is 11:39 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement