ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Long-winded code (https://www.excelbanter.com/excel-programming/278413-long-winded-code.html)

Hotbird
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

J.E. McGimpsey
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

Hotbird
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?


All times are GMT +1. The time now is 12:43 PM.

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