Thread: Error: "Out of stack space"

Hi All ,

I need help with my macro!

my macro comes up with an ERROR "out of stack space. when it runs this macro. bassically i want it to run the macro only when i select a matrix of calls, IE row row 5, column 3, row 5, colum 5, row 5 column 7, row 7, column 3..and onwards,

Depending on which cell is selected as the target then it sets a probabily and impact slection that finds rows that match that critera.
IE row 3 column 5 , is low probability medium Impact.

It then copies specific cells in the row to a different tab, and then goes down to the next row to see if that matches the criteria.

Marco below.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim Sharepointsheet As Worksheet
Dim ProbabilityString As String
Dim ImpactString As String
Dim Outputsheet As Worksheet

Set Sharepointsheet = ThisWorkbook.Worksheets("Sharepoint1")
Set Outputsheet = ThisWorkbook.Worksheets("Risks-Work stream")

'This is where the grid is
Outputsheet.Select

'grid that determins the selection of rows i want to copy

If Target.Row = 5 And Target.Column = 7 And Range("G2").Value = "YES" Then
    ProbabilityString = "High"
    ImpactString = "High"

ElseIf Target.Row = 5 And Target.Column = 5 And Range("G2").Value = "YES" Then
    ProbabilityString = "Medium"
    ImpactString = "High"

ElseIf Target.Row = 5 And Target.Column = 3 And Range("G2").Value = "YES" Then
    ProbabilityString = "Low"
    ImpactString = "High"

ElseIf Target.Row = 9 And Target.Column = 7 And Range("G2").Value = "YES" Then
    ProbabilityString = "High"
    ImpactString = "Medium"

ElseIf Target.Row = 9 And Target.Column = 5 And Range("G2").Value = "YES" Then
    ProbabilityString = "Medium"
    ImpactString = "Medium"

ElseIf Target.Row = 9 And Target.Column = 3 And Range("G2").Value = "YES" Then
    ProbabilityString = "Low"
    ImpactString = "Medium"

ElseIf Target.Row = 13 And Target.Column = 7 And Range("G2").Value = "YES" Then
    ProbabilityString = "High"
    ImpactString = "low"

ElseIf Target.Row = 13 And Target.Column = 5 And Range("G2").Value = "YES" Then
    ProbabilityString = "Medium"
    ImpactString = "Low"

ElseIf Target.Row = 13 And Target.Column = 3 And Range("G2").Value = "YES" Then
    ProbabilityString = "Low"
    ImpactString = "Low"



End If

' where the data is stored

Sharepointsheet.Select

'Start search in row 2
LSearchRow = 2
LCopyToRow = 28
Lworkstream = X1

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'Populate depending on cell highlighted in matrix

If Range("B" & LSearchRow).Value = Sharepointsheet.Range("W1") And (Range("P" & LSearchRow).Value = "(1) Active" Or Range("K" & LSearchRow).Value = "(2) Postponed") And Range("L" & LSearchRow).Value = ProbabilityString And Range("K" & LSearchRow).Value = ImpactString Then

'Select row in Sharepointsheet to copy

Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select

Selection.Copy

'Paste row into output sheet

Sheets("Risks-Work stream").Select

Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Sheets("Test").Select.Paste
'Move counter to next row

LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching

Outputsheet.Select

End If

LSearchRow = LSearchRow + 1

Wend
'Position on cell A3
'Application.CutCopyMode = False
'Sheets("Sharepoint1").Select
'Sheets("Sharepoint1").Range("A3").Select
'Sheets("Sharepoint1").Select
'Range("A3").Select
'Exit Sub

'format row heights

Outputsheet.Select
'Rows("28:51").Select
'Selection.RowHeight = 130

'merge cells to enable better viewing or large items
Range("H28:J28").Select
Selection.Merge
Range("E28:G28").Select
Selection.Merge

'Format cells
Range("A28:T28").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    

Exit Sub

End Sub

Hope you can help!!

Rich

Try disabling events whilst in your event code so the changing of cells does not cause the event to fire again.

Code:

Application.EnableEvents = false
'
' your code
'
Application.EnableEvnets = True