Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Buttons on XL User Forms
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Buttons on XL User 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 -----Original Message----- 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? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Buttons on XL User Forms
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 "Mike" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User form Command Buttons | Excel Discussion (Misc queries) | |||
Creating Buttons using the Forms Tool bar. | Excel Worksheet Functions | |||
Forms buttons | Excel Discussion (Misc queries) | |||
User forms "back" buttons | Excel Discussion (Misc queries) | |||
Group buttons from the forms toolbar | Excel Discussion (Misc queries) |