|
Selection Change - Data Sub Totals?
Please correct my macro.
Line1 is OK LastLine - The worksheet "flickers" (like doing a loop) for about 5 seconds. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell = "$G$5" Then GoTo Line1 If ActiveCell = "$L$5" Then GoTo LastLine Line1: If Intersect(Target, Me.Range("G5")) Is Nothing Then 'do nothing Else Selection.RemoveSubtotal Range("A5").Select End If LastLine: If Intersect(Target, Me.Range("L5")) Is Nothing Then 'do nothing Else Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _ 7, 8, 9, 10, 11, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Range("A5").Select End If End Sub Whats wrong with it? Thanks |
Selection Change - Data Sub Totals?
When your code selects something (like Range("A5")), then it causes the
_selectionchange event to fire. This worked under light testing: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not (Intersect(Target, Me.Range("G5")) Is Nothing) Then Target.RemoveSubtotal Application.EnableEvents = False Me.Range("A5").Select Application.EnableEvents = True Else If Intersect(Target, Me.Range("L5")) Is Nothing Then 'do nothing Else Application.DisplayAlerts = False Target.CurrentRegion.Subtotal GroupBy:=1, _ Function:=xlSum, _ TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Application.DisplayAlerts = True Application.EnableEvents = False Me.Range("A5").Select Application.EnableEvents = True End If End If End Sub Personally, I'd think about adding a couple of buttons from the Forms toolbar to the worksheet. I don't think I'd want this kind of stuff running based on the selection of cells. Danny wrote: Please correct my macro. Line1 is OK LastLine - The worksheet "flickers" (like doing a loop) for about 5 seconds. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell = "$G$5" Then GoTo Line1 If ActiveCell = "$L$5" Then GoTo LastLine Line1: If Intersect(Target, Me.Range("G5")) Is Nothing Then 'do nothing Else Selection.RemoveSubtotal Range("A5").Select End If LastLine: If Intersect(Target, Me.Range("L5")) Is Nothing Then 'do nothing Else Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _ 7, 8, 9, 10, 11, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Range("A5").Select End If End Sub Whats wrong with it? Thanks -- Dave Peterson |
Selection Change - Data Sub Totals?
Thanks a lot. I'll follow your advice, I'll use buttons. Have a great day!
"Dave Peterson" wrote: When your code selects something (like Range("A5")), then it causes the _selectionchange event to fire. This worked under light testing: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not (Intersect(Target, Me.Range("G5")) Is Nothing) Then Target.RemoveSubtotal Application.EnableEvents = False Me.Range("A5").Select Application.EnableEvents = True Else If Intersect(Target, Me.Range("L5")) Is Nothing Then 'do nothing Else Application.DisplayAlerts = False Target.CurrentRegion.Subtotal GroupBy:=1, _ Function:=xlSum, _ TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Application.DisplayAlerts = True Application.EnableEvents = False Me.Range("A5").Select Application.EnableEvents = True End If End If End Sub Personally, I'd think about adding a couple of buttons from the Forms toolbar to the worksheet. I don't think I'd want this kind of stuff running based on the selection of cells. Danny wrote: Please correct my macro. Line1 is OK LastLine - The worksheet "flickers" (like doing a loop) for about 5 seconds. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell = "$G$5" Then GoTo Line1 If ActiveCell = "$L$5" Then GoTo LastLine Line1: If Intersect(Target, Me.Range("G5")) Is Nothing Then 'do nothing Else Selection.RemoveSubtotal Range("A5").Select End If LastLine: If Intersect(Target, Me.Range("L5")) Is Nothing Then 'do nothing Else Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _ 7, 8, 9, 10, 11, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Range("A5").Select End If End Sub Whats wrong with it? Thanks -- Dave Peterson |
| All times are GMT +1. The time now is 05:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com