View Full Version : Single Command Button to run multiple Worksheets
Lawrence
02-21-2008, 02:23 PM
Forgive my trivial question, but I am a total newbie when it comes to VBA. I am just starting and I came up with this code after lots of reading and trial and error, although probably not the most efficient one.
I have this code pasted in Worksheet1 and Worksheet2 with a CommandButton1 on each one to fire the code. What I am trying to do is to have a single CommandButton1 that would fire the code though all the specified sheets, instead of having to go to each sheet and hit the button each time.
Lastly, is there a way to dynamically populate? Right now, I am telling to put the value for lngSelect1 and lngSelect2 in a specific cell (H17 and H18). The problem is that I have about 500 values to return and if there are any changes, I have to manually slide everything. Could I set the first values (for lngSelect1) and have VBA return all the others one cell/row down?
Thanks for your help
Option Explicit
Dim glngDataTotalRows As Long
Private Sub CommandButton2_Click()
Application.EnableEvents = False
Sheets(Array("Sheet1", "Sheet2")).PrintPreview
Application.EnableEvents = True
End Sub
Private Sub CommandButton1_Click()
glngDataTotalRows = lngLastRow
UpdateDataTable
MsgBox "The report is ready"
End Sub
Private Function lngLastRow() As Long
Dim lngDataTotalRows As Long
lngDataTotalRows = 1
Do Until Worksheets(strDataTab).Cells(lngDataTotalRows, 1).Value = ""
lngDataTotalRows = lngDataTotalRows + 1
Loop
lngLastRow = lngDataTotalRows - 1
End Function
Private Function blnIsRegion(lngRowIndex As Long)
If Worksheets(strDataTab).Cells(lngRowIndex, 2).Value = Worksheets(Me.Name).Range("H15").Value Then
blnIsRegion = True
Else
blnIsRegion = False
End If
End Function
Private Function blnIsDivision(lngRowIndex As Long)
If Worksheets(strDataTab).Cells(lngRowIndex, 3).Value = Worksheets(Me.Name).Range("H15").Value Then
blnIsDivision = True
Else
blnIsDivision = False
End If
End Function
Private Function blnIsState(lngRowIndex As Long)
If Worksheets(strDataTab).Cells(lngRowIndex, 37).Value = Worksheets(Me.Name).Range("H15").Value Then
blnIsState = True
Else
blnIsState = False
End If
End Function
Private Sub Worksheet_Activate()
End Sub
Private Function strDataTab() As String
strDataTab = Me.Name & "-Data"
End Function
Private Sub UpdateDataTable()
Dim lngRowIndex As Long
Dim lngSelect1 As Long,
Dim lngSelect2 As Long,
For lngRowIndex = 2 To glngDataTotalRows
If blnIsRegion(lngRowIndex) = True Or blnIsDivision(lngRowIndex) = True Or blnIsState(lngRowIndex) = True Then
Select Case Worksheets(strDataTab).Cells(lngRowIndex, 3).Value
Case "ABC"
lngSelect1 = lngSelect1 + 1
Case "DEF"
lngSelect2 = lngSelect2 + 1
End Select
End If
Next
'Populate
Worksheets(Me.Name).Range("H17").Value = lngSelect1
Worksheets(Me.Name).Range("H18").Value = lngSelect2
Bob Phillips
02-21-2008, 02:42 PM
Private Sub CommandButton2_Click()
Application.EnableEvents = False
Sheets(Array("CuWorksheet1", "Worksheet2")).PrintPreview
Application.EnableEvents = True
End Sub
Private Sub CommandButton1_Click()
Dim itm As Variant
For Each itm In Array("Sheet1", "Sheet2")
UpdateDataTable itm
Next itm
MsgBox "The report is ready"
End Sub
Private Sub UpdateDataTable(ByVal strDataTab As String)
Dim glngDataTotalRows As Long
Dim lngRowIndex As Long
Dim lngSelect1 As Long
Dim lngSelect2 As Long
For lngRowIndex = 2 To lngLastRow(strDataTab)
If blnIsRegion(strDataTab, lngRowIndex) = True Or _
blnIsDivision(strDataTab, lngRowIndex) = True Or _
blnIsState(strDataTab, lngRowIndex) = True Then
Select Case Worksheets(strDataTab & "-Data").Cells(lngRowIndex, 3).Value
Case "ABC"
lngSelect1 = lngSelect1 + 1
Case "DEF"
lngSelect2 = lngSelect2 + 1
End Select
End If
Next
'Populate
Worksheets(strDataTab).Range("H17").Value = lngSelect1
Worksheets(strDataTab).Range("H18").Value = lngSelect2
End Sub
Private Function lngLastRow(ByVal strDataTab As String) As Long
Dim lngDataTotalRows As Long
lngDataTotalRows = 1
Do Until Worksheets(strDataTab).Cells(lngDataTotalRows, 1).Value = ""
lngDataTotalRows = lngDataTotalRows + 1
Loop
lngLastRow = lngDataTotalRows - 1
End Function
Private Function blnIsRegion(ByVal strDataTab As String, lngRowIndex As Long)
blnIsRegion = Worksheets(strDataTab).Cells(lngRowIndex, 2).Value = Worksheets(strDataTab).Range("H15").Value
End Function
Private Function blnIsDivision(ByVal strDataTab As String, lngRowIndex As Long)
blnIsDivision = Worksheets(strDataTab).Cells(lngRowIndex, 3).Value = Worksheets(strDataTab).Range("H15").Value
End Function
Private Function blnIsState(ByVal strDataTab As String, lngRowIndex As Long)
blnIsState = Worksheets(strDataTab).Cells(lngRowIndex, 37).Value = Worksheets(strDataTab).Range("H15").Value
End Function
Lawrence
02-21-2008, 03:20 PM
Thank you, it helped run the code though the worksheets, but now it is returning zeros in every cell ???
mdmackillop
02-21-2008, 04:10 PM
Can you post some sample data?
Lawrence
02-21-2008, 04:54 PM
Attached is a simplified sample. You make your selection in cell J5 and then click the red button to update the data. I put the same code in each sheet because the selection you make on the "Current Day" sheet in cell J5 is carried through all other sheets. I can't figure out how to have a single CommandButton1 that would update both "Current Day" and "Prior Day" output sheets instead of having to do it for every output sheet. The ultimate would be to have the data automatically update based on cell J5 selection, but I need to get better first :)
Edit: This is with the original code I posted, not with XLD's edits
Lawrence
02-22-2008, 09:06 AM
Anyone? As mentioned earlier, xld's code posted above goes through both sheets, however it zero's everything out.
Bob Phillips
02-22-2008, 10:12 AM
This is quite a bit different to what you shoewed us earlier.
Option Explicit
Private Sub CommandButton2_Click()
Application.EnableEvents = False
Sheets(Array("Current Day", "Prior Day")).PrintPreview
Application.EnableEvents = True
End Sub
Private Sub CommandButton1_Click()
Dim itm As Variant
For Each itm In Array("Current Day", "Prior Day")
UpdateDataTable itm
Next itm
MsgBox "The report is ready"
End Sub
Private Sub UpdateDataTable(ByVal strDataTab As String)
Dim glngDataTotalRows As Long
Dim lngRowIndex As Long
Dim lngSelect1 As Long, lngOthers1 As Long
Dim lngSelect2 As Long, lngOthers2 As Long
Dim lngSelect3 As Long, lngOthers3 As Long
Dim lngSelect4 As Long, lngOthers4 As Long
Dim lngSelect5 As Long, lngOthers5 As Long
Dim lngSelect6 As Long, lngOthers6 As Long
Dim lngSelect7 As Long, lngOthers7 As Long
Dim lngSelect8 As Long, lngOthers8 As Long
For lngRowIndex = 6 To lngLastRow(strDataTab)
If blnIsRegion(strDataTab, lngRowIndex) = True Then
Select Case Worksheets(strDataTab).Cells(lngRowIndex, "C").Value
Case "ABC": lngSelect1 = lngSelect1 + 1
Case "DEF": lngSelect2 = lngSelect2 + 1
Case "XYZ": lngSelect3 = lngSelect3 + 1
Case "East": lngSelect4 = lngSelect4 + 1
Case "West": lngSelect5 = lngSelect5 + 1
Case "South": lngSelect6 = lngSelect6 + 1
Case "AK": lngSelect7 = lngSelect7 + 1
Case "CA": lngSelect8 = lngSelect8 + 1
End Select
Else
Select Case Worksheets(strDataTab).Cells(lngRowIndex, 1).Value
Case "ABC": lngOthers1 = lngOthers1 + 1
Case "DEF": lngOthers2 = lngOthers2 + 1
Case "XYZ": lngOthers3 = lngOthers3 + 1
Case "East": lngOthers4 = lngOthers4 + 1
Case "West": lngOthers5 = lngOthers5 + 1
Case "South": lngOthers6 = lngOthers6 + 1
Case "AK": lngOthers7 = lngOthers7 + 1
Case "CA": lngOthers8 = lngOthers8 + 1
End Select
End If
Next
Worksheets(strDataTab).Range("E6").Value = lngSelect1
Worksheets(strDataTab).Range("E7").Value = lngSelect2
Worksheets(strDataTab).Range("E8").Value = lngSelect3
Worksheets(strDataTab).Range("E9").Value = lngSelect4
Worksheets(strDataTab).Range("E10").Value = lngSelect5
Worksheets(strDataTab).Range("E11").Value = lngSelect6
Worksheets(strDataTab).Range("E12").Value = lngSelect7
Worksheets(strDataTab).Range("E13").Value = lngSelect8
Worksheets(strDataTab).Range("F6").Value = lngOthers1
Worksheets(strDataTab).Range("F7").Value = lngOthers2
Worksheets(strDataTab).Range("F8").Value = lngOthers3
Worksheets(strDataTab).Range("F9").Value = lngOthers4
Worksheets(strDataTab).Range("F10").Value = lngOthers5
Worksheets(strDataTab).Range("F11").Value = lngOthers6
Worksheets(strDataTab).Range("F12").Value = lngOthers7
Worksheets(strDataTab).Range("F13").Value = lngOthers8
End Sub
Private Function lngLastRow(ByVal strDataTab As String) As Long
Dim lngDataTotalRows As Long
lngDataTotalRows = 6
Do Until Worksheets(strDataTab).Cells(lngDataTotalRows, "C").Value = ""
lngDataTotalRows = lngDataTotalRows + 1
Loop
lngLastRow = lngDataTotalRows - 1
End Function
Private Function blnIsRegion(ByVal strDataTab As String, lngRowIndex As Long)
blnIsRegion = Worksheets(strDataTab).Cells(lngRowIndex, 2).Value = Worksheets(strDataTab).Range("H15").Value
End Function
Private Function blnIsDivision(ByVal strDataTab As String, lngRowIndex As Long)
blnIsDivision = Worksheets(strDataTab).Cells(lngRowIndex, 3).Value = Worksheets(strDataTab).Range("H15").Value
End Function
Private Function blnIsState(ByVal strDataTab As String, lngRowIndex As Long)
blnIsState = Worksheets(strDataTab).Cells(lngRowIndex, 37).Value = Worksheets(strDataTab).Range("H15").Value
End Function
Lawrence
02-22-2008, 11:05 AM
This is quite a bit different to what you shoewed us earlier.
Sorry for not being clear enough.
Basically, in the sample file I attached, the output sheets are "Current Day" and "Prior Day", and the input data sheets (lots of raw data will be there) are "Current Day-Data" and "Prior Day-Data". The idea is for the code to look at the corresponding input data sheet and to return the count for each item on the appropriate output sheet. It works but I have to hit the CommandButton1 on each sheet to fire each code,
Did I explain it better?
Bob Phillips
02-22-2008, 11:26 AM
I would have thought it is all best done by formulae, but what is this test trying to check
If blnIsRegion(strDataTab, lngRowIndex) Then
Lawrence
02-22-2008, 11:59 AM
I would have thought it is all best done by formulae, but what is this test trying to check
If blnIsRegion(strDataTab, lngRowIndex) Then
Sorry about the confusion, I just started this a couple weeks ago and this is my first code.
In the input sheets, I have roughly 150 columns and 40,000 rows. The statement above checks for a certain attribute in a specified column. If there is a match, it is true and then it returns the count in a certain column. If not a match, it is false, and then returns the count in another column.
In my original post, I had 3 of these statements because my drop down menu takes values from 3 different columns. However, in the simplified example attached, it would look at Column 1 in the corresponding Data tab and if it is a matches the selection in cell E5, it returns the count in column E. If not a match, it returns the count in column F instead.
The following code you gave me works, however it looks at the output sheet instead of looking at the input (corresponding data) sheet, so it returns 1 in every cell because it finds only one instance of everything.
Option Explicit
Private Sub CommandButton2_Click()
Application.EnableEvents = False
Sheets(Array("Current Day", "Prior Day")).PrintPreview
Application.EnableEvents = True
End Sub
Private Sub CommandButton1_Click()
Dim itm As Variant
For Each itm In Array("Current Day", "Prior Day")
UpdateDataTable itm
Next itm
MsgBox "The report is ready"
End Sub
Private Sub UpdateDataTable(ByVal strDataTab As String)
Dim glngDataTotalRows As Long
Dim lngRowIndex As Long
Dim lngSelect1 As Long, lngOthers1 As Long
Dim lngSelect2 As Long, lngOthers2 As Long
Dim lngSelect3 As Long, lngOthers3 As Long
Dim lngSelect4 As Long, lngOthers4 As Long
Dim lngSelect5 As Long, lngOthers5 As Long
Dim lngSelect6 As Long, lngOthers6 As Long
Dim lngSelect7 As Long, lngOthers7 As Long
Dim lngSelect8 As Long, lngOthers8 As Long
For lngRowIndex = 6 To lngLastRow(strDataTab)
If blnIsRegion(strDataTab, lngRowIndex) = True Then
Select Case Worksheets(strDataTab).Cells(lngRowIndex, 3).Value
Case "ABC": lngSelect1 = lngSelect1 + 1
Case "DEF": lngSelect2 = lngSelect2 + 1
Case "XYZ": lngSelect3 = lngSelect3 + 1
Case "East": lngSelect4 = lngSelect4 + 1
Case "West": lngSelect5 = lngSelect5 + 1
Case "South": lngSelect6 = lngSelect6 + 1
Case "AK": lngSelect7 = lngSelect7 + 1
Case "CA": lngSelect8 = lngSelect8 + 1
End Select
Else
Select Case Worksheets(strDataTab).Cells(lngRowIndex, 3).Value
Case "ABC": lngOthers1 = lngOthers1 + 1
Case "DEF": lngOthers2 = lngOthers2 + 1
Case "XYZ": lngOthers3 = lngOthers3 + 1
Case "East": lngOthers4 = lngOthers4 + 1
Case "West": lngOthers5 = lngOthers5 + 1
Case "South": lngOthers6 = lngOthers6 + 1
Case "AK": lngOthers7 = lngOthers7 + 1
Case "CA": lngOthers8 = lngOthers8 + 1
End Select
End If
Next
Worksheets(strDataTab).Range("E6").Value = lngSelect1
Worksheets(strDataTab).Range("E7").Value = lngSelect2
Worksheets(strDataTab).Range("E8").Value = lngSelect3
Worksheets(strDataTab).Range("E9").Value = lngSelect4
Worksheets(strDataTab).Range("E10").Value = lngSelect5
Worksheets(strDataTab).Range("E11").Value = lngSelect6
Worksheets(strDataTab).Range("E12").Value = lngSelect7
Worksheets(strDataTab).Range("E13").Value = lngSelect8
Worksheets(strDataTab).Range("F6").Value = lngOthers1
Worksheets(strDataTab).Range("F7").Value = lngOthers2
Worksheets(strDataTab).Range("F8").Value = lngOthers3
Worksheets(strDataTab).Range("F9").Value = lngOthers4
Worksheets(strDataTab).Range("F10").Value = lngOthers5
Worksheets(strDataTab).Range("F11").Value = lngOthers6
Worksheets(strDataTab).Range("F12").Value = lngOthers7
Worksheets(strDataTab).Range("F13").Value = lngOthers8
End Sub
Private Function lngLastRow(ByVal strDataTab As String) As Long
Dim lngDataTotalRows As Long
lngDataTotalRows = 6
Do Until Worksheets(strDataTab).Cells(lngDataTotalRows, 3).Value = ""
lngDataTotalRows = lngDataTotalRows + 1
Loop
lngLastRow = lngDataTotalRows - 1
End Function
Private Function blnIsRegion(ByVal strDataTab As String, lngRowIndex As Long)
blnIsRegion = Worksheets(strDataTab).Cells(lngRowIndex, 3).Value = Worksheets(strDataTab).Range("E5").Value
End Function
Bob Phillips
02-22-2008, 12:32 PM
In the input sheets, I have roughly 150 columns and 40,000 rows. The statement above checks for a certain attribute in a specified column. If there is a match, it is true and then it returns the count in a certain column. If not a match, it is false, and then returns the count in another column.
That much I could have said, it is just generic terms. I wanted to know what your business requirement is.
In my original post, I had 3 of these statements because my drop down menu takes values from 3 different columns. However, in the simplified example attached, it would look at Column 1 in the corresponding Data tab and if it is a matches the selection in cell E5, it returns the count in column E. If not a match, it returns the count in column F instead.
E5 holds AK, so if the AK numbers go in column E, the others in F, why do we have AK in row 12, the numbers can only be in E or F if I understand you, but you load numbers for both types in the code and then write them all back - I am confused by it all.
Lawrence
02-22-2008, 01:42 PM
Does this help?
The idea is to know that for AK for example, 4 instances came from Division ABC, 1 from DEF, and 1 from XYZ, 2 from the East Region, etc...
but you load numbers for both types in the code and then write them all back - I am confused by it all.
I never said it was pretty, and there's probably a better way of doing it.
Bob Phillips
02-22-2008, 04:49 PM
Every time you post something, you change it. We don't stand a chance.
Both sheets are different to how they were last time.
But, if I finally get it, you can do it with formula
E6: =SUMPRODUCT(--(('Prior Day-Data'!$A$1:$A$20=$C6)+('Prior Day-Data'!$B$1:$B$20=$C6)+('Prior Day-Data'!$C$1:$C$20=$C6)),--('Prior Day-Data'!$C$1:$C$20=E$5))
F6: =SUMPRODUCT(--(('Prior Day-Data'!$A$1:$A$20=$C6)+('Prior Day-Data'!$B$1:$B$20=$C6)+('Prior Day-Data'!$C$1:$C$20=$C6)))-E6
Copy E6:F6 down to row 13
E14: =SUMPRODUCT(--('Prior Day-Data'!$C$1:$C$20=$E$5),--('Prior Day-Data'!$D$1:$D$20<1000))
F14: =COUNTIF('Prior Day-Data'!D:D,"<1000")-E14
E15: =SUMPRODUCT(--('Prior Day-Data'!$C$1:$C$20=$E$5),--('Prior Day-Data'!$D$1:$D$20>=1000))
F15: =COUNTIF('Prior Day-Data'!D:D,">=1000")-E15
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.