Excal VBA - Vlookup macro dropdown - out of stack space

0
votes

I have an excel sheet, from a dropdown list I will have numbers of items to select.

After selecting specific item, a specific macro is run that will paste vlookup formulas to the range of cells.

My workbook VBA looks as follows:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Range("B2")

Case "Company 1"
Call Company1

Case "Company 2"
Call Company2

End Select
End Sub

And my macros look as follows

Sub Company1()
Range("D7:D14").Formula = "=VLOOKUP(B7,'SheetLocation\[Sheet1.xls]Sheet1'!$A$6:$E$93,3,FALSE)"
End Sub

Sub Company2()
Range("D7:D14").Formula = "=VLOOKUP(B7,'SheetLocation\[Sheet2.xls]Sheet2'!$A$6:$E$93,3,FALSE)"
End Sub

With such macros everytime I select the value form dropdown list my excel hangs up with error Runtime error 28 - Out of stack space.

Need your help guys.

1
excelvlookupvba
You don't care which worksheet Range("B2") is on? – user4039065
I care, it should be Sheet1, I know it's not defined. Nonetheless adding to the code: code If Not Intersect(Target, Range("B2")) Is Nothing Then solved the issue. – Drcs
Could you not be causing an infinite loop, using worksheet_change? – Davesexcel
Is this question answered now? – Davesexcel

1 Answers

0
votes

you are pasting formulas using Workbook_SheetChange. This event triggers everytime ANY cell changes values, so it activates for each formula pasted, and also, it triggers again when calculation is done. Every time a formula is calculed, Excel recalculates ALL formulas, so it triggers this event again, and again, and again. So yeah, infinite loop overflows memory, and you get that error. To fix it, disable events

Application.EnableEvents = False
Select Case Range("B2")

Case "Company 1"
Call Company1

Case "Company 2"
Call Company2

End Select
Application.EnableEvents = true

But to be honest, best solution would be NOT using that event. Do you really need it in that event for any particular reason?

More info about Out of stack space (Error 28)

More info about Workbook.SheetChange Event