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 <Fxneb.1601$(E-Mail Removed)>,
"Hotbird" <(E-Mail Removed)> 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
>
>
|
Similar Threads
