Long-winded code

[Hotbird]

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("P1725")
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]

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

 

[Hotbird]

Thank you for your reply. It worked like a dream....