Can i shorten this simple code

[clares]

Hi All

I am sure that I can, but can this code be shortened?

Private Sub CommandButton1_Click()
Dim PeterHSG As Integer
Dim PeterHSS As Integer
Dim PeterHHG As Integer
Dim PeterHHS As Integer
Dim BarryHSG As Integer
Dim BarryHSS As Integer
Dim BarryHHG As Integer
Dim BarryHHS As Integer
Dim AlanHSG As Integer
Dim AlanHSS As Integer
Dim AlanHHG As Integer
Dim AlanHHS As Integer

PeterHSG = Range("L18").Value
PeterHSS = Range("L20").Value
PeterHHG = Range("P18").Value
PeterHHS = Range("P20").Value

BarryHSG = Range("L28").Value
BarryHSS = Range("L30").Value
BarryHHG = Range("P28").Value
BarryHHS = Range("P30").Value

AlanHSG = Range("L38").Value
AlanHSS = Range("L40").Value
AlanHHG = Range("P38").Value
AlanHHS = Range("P40").Value

'Peters images
Image1.Visible = False
Image2.Visible = False
Image3.Visible = False
Image4.Visible = False

'Barrys image
Image5.Visible = False
Image6.Visible = False
Image7.Visible = False
Image8.Visible = False

'Alans images
Image9.Visible = False
Image10.Visible = False
Image11.Visible = False
Image12.Visible = False

If (PeterHSG > BarryHSG) And (PeterHSG > AlanHSG) Then
    Image1.Visible = True
ElseIf (BarryHSG > PeterHSG) And (BarryHSG > AlanHSG) Then
    Image5.Visible = True
ElseIf (AlanHSG > PeterHSG) And (AlanHSG > BarryHSG) Then
    Image9.Visible = True
End If

If (PeterHSS > BarryHSS) And (PeterHSS > AlanHSS) Then
    Image2.Visible = True
ElseIf (BarryHSS > PeterHSS) And (BarryHSS > AlanHSS) Then
    Image6.Visible = True
ElseIf (AlanHSS > PeterHSS) And (AlanHSS > BarryHSS) Then
    Image10.Visible = True
End If

If (PeterHHG > BarryHHG) And (PeterHHG > AlanHHG) Then
    Image3.Visible = True
ElseIf (BarryHHG > PeterHHG) And (BarryHHG > AlanHHG) Then
    Image7.Visible = True
ElseIf (AlanHHG > PeterHHG) And (AlanHHG > BarryHHG) Then
    Image11.Visible = True
End If

If (PeterHHS > BarryHHS) And (PeterHHS > AlanHHS) Then
    Image4.Visible = True
ElseIf (BarryHHS > PeterHHS) And (BarryHHS > AlanHHS) Then
    Image8.Visible = True
ElseIf (AlanHHS > PeterHHS) And (AlanHHS > BarryHHS) Then
    Image12.Visible = True
End If

End Sub

Kinest Regards

Peter

 

[DRJ]

Hi

You can replace this

'Peters images 
Image1.Visible = False 
Image2.Visible = False 
Image3.Visible = False 
Image4.Visible = False 

'Barrys image 
Image5.Visible = False 
Image6.Visible = False 
Image7.Visible = False 
Image8.Visible = False 

'Alans images 
Image9.Visible = False 
Image10.Visible = False 
Image11.Visible = False 
Image12.Visible = False

With This

For x = 1 to 12
    UserForm1.Controls("Image" & x).Visible = False
Next x

 

[clares]

Hi There

Thanks for that.

Is there a way that I could also reduce the if statements, and perhap dim the variables and set their values to a specific cell reference?

Any help would be very much appreciated.

Kindest Regards

Peter

 

[P Sitaram]

In a separate part of the worksheet, or in a new sheet build up a table with the three names as the headers and the values HSG, HSS, HHG and HHS values in the rows. Assume the data is A1:D5. In E2, enter the formula:

=MATCH(MAX(B2:D2),B2:D2)

You can then have a simple select case statement for each value in E2 to E5

ps: If a multiple of 3 is added to each of the above results and your image numbers are changed to suit, then even the selece case can be avoided by directly concatanating the Image numbers. (eg. "Image" & [E2] )