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

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

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