Command Buttons on XL User Forms

  • Thread starter Thread starter Hotbird
  • Start date Start date
H

Hotbird

Have been happily adding control buttons to spreadsheets for a month or two,
but my code is very repetitive - in fact almost the same for each button
that I use. Is there a way to simplify the code by using button arrays on
XL Forms, as can be done with Visual Basic?

Second question. Is there a good place to find sample spreadsheets
illustrating the use of XL Forms?
 
No, you can't create an array of buttons. What you can
try is moving your macro code to a module, include a
parameter and run the macro from the button, something
along this line:

'Module Code
Sub MyMacro(Button as Integer)

Select Case Button
Case 1
'Stuff specific to button 1
Case 2
'Stuff specific to button 2
Case 3
'Stuff specific to button 3
End Select

'Stuff shared by all buttons

End Sub


'Worksheet Code
Private Sub CommandButton1_Click()
MyMacro(1)
End Sub

Private Sub CommandButton2_Click()
MyMacro(2)
End Sub

Private Sub CommandButton3_Click()
MyMacro(3)
End Sub
 
This is an example of code for my 9 buttons. (BTW my subject is wWeakest
Link Voting)
I have tried unsuccessfully to specify the button names using a parameter:
e'g' B1.BackColor , B2.BackColor etc up to Button 9

Private Sub B1_Click()
If Voting = 1 Then
ActiveSheet.Cells(1 + Voting, 1 + RoundN).Value = ""
Voting = 0: B1.BackColor = Grey: TidyUp: Exit Sub
End If
If Voting = 0 Then
Voting = 1: B1.BackColor = vbYellow
Else:
ActiveSheet.Cells(1 + Voting, 1 + RoundN).Value = 1
Voting = 0: B1.BackColor = Grey
End If
TidyUp
End Sub

Private Sub B2_Click()
If Voting = 2 Then
ActiveSheet.Cells(1 + Voting, 1 + RoundN).Value = ""
Voting = 0: B2.BackColor = Grey: TidyUp: Exit Sub
End If
If Voting = 0 Then
Voting = 2: B2.BackColor = vbYellow
Else
ActiveSheet.Cells(1 + Voting, 1 + RoundN).Value = 2
Voting = 0: B2.BackColor = Grey
End If
TidyUp
End Sub

Private Sub B3_Click()
If Voting = 3 Then
ActiveSheet.Cells(1 + Voting, 1 + RoundN).Value = ""
Voting = 0: B3.BackColor = Grey: TidyUp: Exit Sub
End If
If Voting = 0 Then
Voting = 3: B3.BackColor = vbYellow
Else
ActiveSheet.Cells(1 + Voting, 1 + RoundN).Value = 3
Voting = 0: B3.BackColor = Grey
End If
TidyUp
End Sub
 
Back
Top