Managing large numbers of buttons

[hiflier]

I have a property management workbook that has a summary sheet with several rows of buttons on it. Press one of the buttons and it creates a summary bill, taking data from each of the monthly sheets and creating the summary.

Right now the code is placed in each button's Click subroutine. I have 57 tenants, so I have 57 Click subroutines, each of which looks like this:

[VBA]Private Sub CommandButton1_Click()
Tenant = 1: SpaceNum = "1"
Call ReconcileSubroutine(Tenant, SpaceNum)
End Sub[/VBA]

This is extremely cumbersome. Is there a way to put the buttons in an array so that I can just call the one I need right from the button push? Thanks. Dave

[mikerickson]

If you use buttons from the forms menu, you could assign them all to a macro like
[VBA]Sub ButtonRoutine()
Select Case Application.Caller
Case Is "Button 1"
Tenant = 1: SpaceNum = "1"
Case Is "Button 2"
Tenant = 2: SpaceNum = "2"
End Select
Call ReconcileSubroutine(Tenant, SpaceNum)
End Sub[/VBA]
or if you are sure that the buttons are numbered consecutivly.
[VBA]Sub ButtonRoutine()
On Error Goto HaltRoutine
Tenant = Val(Mid(Application.Caller,8)
Call ReconcileSubroutine(Tenant, CStr(Tenant))
HaltRoutine:
On Error Goto 0
End Sub[/VBA]Since, they both use Application.Caller, they will error if called by the Macro Dialog box. Error handling to deal with that is shown in the second routine, but can be applied to the first.

[hiflier]

That's exactly what I wanted, but the buttons I use are created using the vba toolbar command button icon and they're planted right on the summary sheet itself. When I use the button1_click() event to call your routine, Application.caller returns error 2023, not the name of the calling button. Do I need to create a form and populate the form with the buttons to make your routine work? Thanks.

[hiflier]

I created a form and put several buttons on it. Button 1 called your routine. But application.caller still had error 2023 in it, not the button name. What am I doing wrong here? Thanks.

[Bob Phillips]

Try this approach.

In your sheet module, add this code

[vba]

Option Explicit

Dim mcolEvents As Collection

Private Sub Worksheet_Activate()
Dim cBtnEvents As clsActiveXEvents
Dim shp As Shape

Set mcolEvents = New Collection

For Each shp In Me.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.CommandButton Then
Set cBtnEvents = New clsActiveXEvents
Set cBtnEvents.mButtonGroup = shp.OLEFormat.Object.Object
mcolEvents.Add cBtnEvents
End If
End If
Next

End Sub

[/vba]

and create a class module called clsActiveXEvents with this code

[vba]

Option Explicit

Public WithEvents mButtonGroup As MSForms.CommandButton

Private Sub mButtonGroup_Click()
MsgBox mButtonGroup.Caption & " has been pressed"
End Sub
[/vba]

[hiflier]

Thanks. That works to provide the button number.

Your msgbox pops up after the button click subroutine is finished. I backed up my project, then deleted all of the button click subroutines, using only your code. Now Microsoft displays their error popup, saying that Excel has to close, and do I want to send the error message to MS? This happens every time I open the summary sheet, which is the one with the button group. So it's not working now, although I think I can get it to work with time. Do I call the ReconcileSubroutine from the class module? Also, I had to name the class module clsActiveButton, as it would not allow me to enter the name clsActiveXEvents. Maybe that has something to do with this.


This is the routine in the class module:

[VBA]Private Sub mButtonGroup_Click()
Tenant = mButtonGroup.Caption: SpaceNum = Tenant
Call ReconcileSubroutine(Tenant, SpaceNum)
'MsgBox mButtonGroup.Caption & " has been pressed"
End Sub[/VBA]

Would you mind commenting your code? And is there a reference I can buy to get to the depths of vba? What would you suggest? Thanks again for your help. As I try to implement your code, I will probably have more questions. Dave

[Bob Phillips]

You woud replace the Msgbox with your code.

Maybe best if you post your workbook to see what is up.

[lucas]

Gotta love the beauty of using a class file for handling what, 57 buttons?

I would have found a way to do it without buttons though.

[Bob Phillips]

Me too.

SOme guy in the NGs recently posted a question whereby he said that he had a form with 1024 optionbuttons the users could choose from. He wanted some advice on a smart way to structire. I suggested he re-design his app ... he took offence <g>

[hiflier]

The code works as is; the only reason I came here is to learn a better way, and you guys have helped me in the past. Here's the issue: I have 57 spaces in a mobile home park. I don't bill all of them every month, only when they are confused about their account. This routine allows me to push a button and get the complete rundown of their account for the year. I'll attach the reconciliation sheet without the code to show you how it's set up. I'll also attach the whole program although it's pretty large. Thanks again, and don't worry about offending me. As a friend once said, "Son, when you've been shot at, none of this matters."

[lucas]

There are probably some nice ways to handle this. Could you repost your workbook with some sample data in place of the formula's in column W that matches what you have in the template(form). Will that cell just say 245 dollars?

Where does the info come from that populates the form?

[lucas]

If you won't be offended by suggestions, I would suggest that you abandon linked workbooks.......

Can you make a copy of the linked workbook, clear out any personal info and post it so we can see what the data might look like?

We only need a few rows of data to work with and make it fake.

[hiflier]

These workbooks go from year to year. I normally link them until mid-January or so, when all the bills from December are in, then manually enter the carry-in. I'm open to any better way for all of this.

I am having a problem with Personal.xls, though. Is there a way to keep that from loading automatically? I attached it to another mobile home park's spreadsheet after reading a post on this forum, but I think it may be behind the crashes of excel that I'm experiencing.

I've uploaded a stripped down verson of the program. On the Recon sheet, it will show data by pressing the buttons for spaces 1 and 1A. Thanks for your help.

The data for the Recon form comes from the monthly forms Jan-Dec.

[hiflier]

Please ignore the Stardust test1 file I first put up. It was a first shot. This is the correct file. Thanks.

[lucas]

That's about as good a use of buttons as I've ever seen.

Personal.xls can cause you some problems. It tends to get corrupted. What did you put in the personal?

[lucas]

You can make it look a lot cleaner if you turn off screen updating at the beginning of this routine and turn it off at the end....I've added it to your code here....

[VBA]Sub ReconcileSubroutine(Tenant, SpaceNum)
Application.ScreenUpdating = False
Sheets(" Recon").Range("B17:L28").Select
Selection.ClearContents 'Erase the whole sheet from last use
Selection.Font.Bold = False

MySheets = "JanFebMarAprMayJunJulAugSepOctNovDec"
MyRange = "B" & Mid$(Str(Tenant + 3), 2) & ":L" & Mid$(Str(Tenant + 3), 2) & ""

MyMonth = Sheets(" Recon").Range("AC1")
For i = 1 To MyMonth
j = (i - 1) * 3 + 1
Sheets(Mid(MySheets, j, 3)).Select
Sheets(Mid(MySheets, j, 3)).Range(MyRange).Select
Selection.Copy
Sheets(" Recon").Select
MyTarget = "B" & Mid(Str(i + 16), 2) & ""
Range(MyTarget).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next

'Put Tenant number into cell
Range("c12").Select
ActiveCell.FormulaR1C1 = "2009 Reconcilation for Space #" & SpaceNum

'Change bill amount to boldface
MyMonth = MyMonth + 17
MyRange = "L" & Mid(Str(MyMonth - 1), 2)
Range(MyRange).Select
MyAmount = ActiveCell.Value
Selection.Font.Bold = True

'Put the billed amount in the bottom line
Range("g30").Select
ActiveCell.FormulaR1C1 = MyAmount * -1

Range("b30").Select
ActiveCell.Value = "Your balance due as of " & MonthName(MyMonth - 17) & " 30 is:"

'Move visible window to Recon area

Application.Goto Reference:=Range("A11"), _
Scroll:=True
Application.ScreenUpdating = True
End Sub
[/VBA]

[hiflier]

That makes a world of difference. Thanks.

[hiflier]

Someone wrote that you put subroutines in Personal. I have a calendar, a menu form (for moving around on the sheets) and a calculator for inputting numbers, particularly standard numbers, like the rent. I have three mobile home parks, and thought I could attach Personal to each without having to transfer code. But it immediately crashed Excel. Now I can't seem to get rid of it. Do you know how?

And by the way, thanks for the compliment. Made my day.

[lucas]

For any code I want to run on any workbook that is open I use a simple addin. You can add your menu's etc. and run the macro's from there. You can move it to any machine....install it as any addin and use it there. It is also easy to backup and edit this way.

Johnske has an excellent article on addins in the articles section.

You should be able to get rid of it by searching your hard drive for personal.xls make a copy of it just in case and delete it.

[Bob Phillips]

If you ditch all the selecting, you don't need the screenupdating.

I would also lose the Tenant and SpaceNum variables, and set the values directly in the call (more descriptive IMO) - actually I would implement the class, and pick up the button n umber and caption)

[vba]

Option Explicit
Dim Tenant As String, SpaceNum As String, MyMonth As String
Dim MySheets, MyRange, MyTarget, MyAmount, i, j As String

Private Sub ComboBox1_Change()
MyMonth = ComboBox1.Text
Range("AC2").Value = MyMonth
MyMonth = "10 " & MyMonth & " 2008"
Range("AC1").Value = Month(MyMonth)
Range("A11").Select
End Sub

Private Sub CommandButton1_Click()
Call ReconcileSubroutine(1, "1")
End Sub
Private Sub CommandButton1A_Click()
Call ReconcileSubroutine(2, "1A")
End Sub
Private Sub CommandButton2_Click()
Call ReconcileSubroutine(3, "2")
End Sub
Private Sub CommandButton3_Click()
Call ReconcileSubroutine(4, "3")
End Sub
Private Sub CommandButton4_Click()
Call ReconcileSubroutine(5, "4")
End Sub
Private Sub CommandButton5_Click()
Call ReconcileSubroutine(6, "5")
End Sub
Private Sub CommandButton6_Click()
Call ReconcileSubroutine(7, "6")
End Sub
Private Sub CommandButton7_Click()
Call ReconcileSubroutine(8, "7")
End Sub
Private Sub CommandButton8_Click()
Call ReconcileSubroutine(9, "8")
End Sub
Private Sub CommandButton9_Click()
Call ReconcileSubroutine(10, "9")
End Sub
Private Sub CommandButton10_Click()
Call ReconcileSubroutine(11, "10")
End Sub
Private Sub CommandButton11_Click()
Call ReconcileSubroutine(12, "11")
End Sub
Private Sub CommandButton12_Click()
Call ReconcileSubroutine(13, "12")
End Sub
Private Sub CommandButton13_Click()
Call ReconcileSubroutine(14, "13")
End Sub
Private Sub CommandButton14_Click()
Call ReconcileSubroutine(15, "14")
End Sub
Private Sub CommandButton15_Click()
Call ReconcileSubroutine(16, "15")
End Sub
Private Sub CommandButton16_Click()
Call ReconcileSubroutine(17, "16")
End Sub
Private Sub CommandButton17_Click()
Call ReconcileSubroutine(18, "17")
End Sub
Private Sub CommandButton18_Click()
Call ReconcileSubroutine(19, "18")
End Sub
Private Sub CommandButton19_Click()
Call ReconcileSubroutine(20, "19")
End Sub
Private Sub CommandButton20_Click()
Call ReconcileSubroutine(21, "20")
End Sub
Private Sub CommandButton21_Click()
Call ReconcileSubroutine(221, "21")
End Sub
Private Sub CommandButton22_Click()
Call ReconcileSubroutine(23, "22")
End Sub
Private Sub CommandButton23_Click()
Call ReconcileSubroutine(24, "23")
End Sub
Private Sub CommandButton24_Click()
Call ReconcileSubroutine(25, "24")
End Sub
Private Sub CommandButton25_Click()
Call ReconcileSubroutine(26, "25")
End Sub
Private Sub CommandButton26_Click()
Call ReconcileSubroutine(27, "26")
End Sub
Private Sub CommandButton27_Click()
Call ReconcileSubroutine(28, "27")
End Sub
Private Sub CommandButton28_Click()
Call ReconcileSubroutine(29, "28")
End Sub
Private Sub CommandButton29_Click()
Call ReconcileSubroutine(30, "29")
End Sub
Private Sub CommandButton30_Click()
'ReconcileSubroutine(30)
End Sub
Private Sub CommandButton31_Click()
Call ReconcileSubroutine(32, "31")
End Sub
Private Sub CommandButton32_Click()
Call ReconcileSubroutine(33, "32")
End Sub
Private Sub CommandButton33_Click()
Call ReconcileSubroutine(34, "33")
End Sub
Private Sub CommandButton34_Click()
'ReconcileSubroutine(34)
End Sub
Private Sub CommandButton35_Click()
Call ReconcileSubroutine(36, "35")
End Sub
Private Sub CommandButton36_Click()
Call ReconcileSubroutine(37, "36")
End Sub
Private Sub CommandButton37_Click()
Call ReconcileSubroutine(38, "37")
End Sub
Private Sub CommandButton38_Click()
Call ReconcileSubroutine(39, "38")
End Sub
Private Sub CommandButton39_Click()
Call ReconcileSubroutine(40, "39")
End Sub
Private Sub CommandButton40_Click()
Call ReconcileSubroutine(41, "40")
End Sub
Private Sub CommandButton41_Click()
Call ReconcileSubroutine(42, "41")
End Sub
Private Sub CommandButton42_Click()
Call ReconcileSubroutine(43, "42")
End Sub
Private Sub CommandButton42A_click()
Call ReconcileSubroutine(44, "42A")
End Sub
Private Sub CommandButton43_Click()
Call ReconcileSubroutine(45, "43")
End Sub
Private Sub CommandButton44_Click()
Call ReconcileSubroutine(46, "44")
End Sub
Private Sub CommandButton45_Click()
Call ReconcileSubroutine(47, "45")
End Sub
Private Sub CommandButton46_Click()
Call ReconcileSubroutine(48, "46")
End Sub
Private Sub CommandButton47_Click()
Call ReconcileSubroutine(49, "47")
End Sub
Private Sub CommandButton48_Click()
Call ReconcileSubroutine(50, "48")
End Sub
Private Sub CommandButton49_Click()
Call ReconcileSubroutine(51, "49")
End Sub
Private Sub CommandButton50_Click()
Call ReconcileSubroutine(52, "50")
End Sub
Private Sub CommandButton51_Click()
Call ReconcileSubroutine(53, "51")
End Sub
Private Sub CommandButton52_Click()
Call ReconcileSubroutine(54, "52")
End Sub
Private Sub CommandButton53_Click()
Call ReconcileSubroutine(55, "53")
End Sub
Private Sub CommandButton54_Click()
Call ReconcileSubroutine(56, "54")
End Sub
Private Sub CommandButton55_Click()
Call ReconcileSubroutine(57, "55")
End Sub
Private Sub CommandButton56_Click()
Call ReconcileSubroutine(58, "56")
End Sub
Private Sub CommandButton57_Click()
Call ReconcileSubroutine(59, "57")
End Sub
Private Sub CommandButton58_Click()
'call reconcilesubroutine(60, "58")
End Sub
Private Sub CommandButton59_Click()
'call reconcilesubroutine(61, "59")
End Sub
Private Sub CommandButton60_Click()
'call reconcilesubroutine(62, "60")
End Sub
Private Sub CommandButton101_Click()
Call ReconcileSubroutine(62, "2A")
End Sub
Private Sub CommandButton102_Click()
Call ReconcileSubroutine(63, "3A")
End Sub
Private Sub CommandButton103_Click()
Call ReconcileSubroutine(64, "4A")
End Sub
Private Sub CommandButton104_Click()
Call ReconcileSubroutine(65, "5A")
End Sub
Private Sub CommandButton105_Click()
Call ReconcileSubroutine(66, "6A")
End Sub
Private Sub CommandButton106_Click()
Call ReconcileSubroutine(67, "7A")
End Sub
Private Sub CommandButton107_Click()
Call ReconcileSubroutine(68, "8A")
End Sub
Private Sub CommandButton108_Click()
Call ReconcileSubroutine(69, "9A")
End Sub
Private Sub CommandButton109_Click()
Call ReconcileSubroutine(70, "10A")
End Sub
Private Sub CommandButton110_Click()
Call ReconcileSubroutine(71, "11A")
End Sub
Private Sub CommandButton111_Click()
Call ReconcileSubroutine(72, "12A")
End Sub

Sub ReconcileSubroutine(Tenant, SpaceNum)

With Sheets(" Recon")

With .Range("B17:L28")

.ClearContents 'Erase the whole sheet from last use
.Font.Bold = False
End With

MySheets = "JanFebMarAprMayJunJulAugSepOctNovDec"
MyRange = "B" & Mid$(Str(Tenant + 3), 2) & ":L" & Mid$(Str(Tenant + 3), 2) & ""

MyMonth = .Range("AC1")
For i = 1 To MyMonth
j = (i - 1) * 3 + 1
Worksheets(Mid(MySheets, j, 3)).Range(MyRange).Copy
MyTarget = "B" & Mid(Str(i + 16), 2) & ""
Sheets(" Recon").Range(MyTarget).PasteSpecial Paste:=xlPasteValues
Next

'Put Tenant number into cell
.Range("C12").Value = "2009 Reconcilation for Space #" & SpaceNum

'Change bill amount to boldface
MyMonth = MyMonth + 17
MyRange = "L" & Mid(Str(MyMonth - 1), 2)
MyAmount = .Range(MyRange).Value

'Put the billed amount in the bottom line
.Range("B30").Value = "Your balance due as of " & MonthName(MyMonth - 17) & " 30 is:"
.Range("G30").Value = MyAmount * -1

.Range("A11").Select
End With

End Sub
[/vba]