Out of stack space

[data808]

Using this VBA code:

Private Sub worksheet_change(ByVal Target As Excel.Range)

If Range("G3").Value = "1550" Then
    Range("A3").Value = "01"
End If

End Sub

And I keep getting an "Out of stack space" error. How do I prevent that error message?

 

[6StringJazzer]

When you change the value in A3, it triggers Worksheet_Change again, which changes A3, which triggers Worksheet_Change......forever until you run out of stack space.

Add the lines indicated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False

If Range("G3").Value = "1550" Then
    Range("A3").Value = "01"
End If

Application.EnableEvents = True

End Sub

 

[data808]

When you change the value in A3, it triggers Worksheet_Change again, which changes A3, which triggers Worksheet_Change......forever until you run out of stack space.

Add the lines indicated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False

If Range("G3").Value = "1550" Then
    Range("A3").Value = "01"
End If

Application.EnableEvents = True

End Sub

Thank you for the suggestion. I did add the enable event lines and it does get rid of the out of stack space error but if I change the G3 cell again, it will not trigger the code to run again unless I close the spreadsheet and reopen it. So basically I only get one shot at typing in 1550.

How do I make it so that it will always run if I type something into G3? I would also like A3 to clear if I delete 1550 from G3. Is that possible?

 

[NdNoviceHlp]

Hi data808. I trialed 6StringJazzer's code and it worked for me. I've had occasions in the past when I needed to create a public boolean variable to maintain a cell's value as long as the target cell value remained constant ie. A3 cannot be changed as long as G3 is 1550. Something like this...

Dim Switch As Boolean
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Switch Then
If Range("G3").Value = "1550" Then
 Switch = True
Range("A3").Value = "01"
End If
Else
Switch = False
End If
End Sub

This seems like what you want...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False

If Range("G3").Value = "1550" Then
    Range("A3").Value = "01"
Else
Range("A3").Value = vbNullString
End If

Application.EnableEvents = True

End Sub

HTH. Dave

 

[6StringJazzer]

BTW your code is comparing to strings. If you actually have numeric values in the cells, you should be using numeric values in the code.

 

[data808]

Hi data808. I trialed 6StringJazzer's code and it worked for me. I've had occasions in the past when I needed to create a public boolean variable to maintain a cell's value as long as the target cell value remained constant ie. A3 cannot be changed as long as G3 is 1550. Something like this...

Dim Switch As Boolean
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Switch Then
If Range("G3").Value = "1550" Then
 Switch = True
Range("A3").Value = "01"
End If
Else
Switch = False
End If
End Sub

This seems like what you want...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False

If Range("G3").Value = "1550" Then
    Range("A3").Value = "01"
Else
Range("A3").Value = vbNullString
End If

Application.EnableEvents = True

End Sub

HTH. Dave

Thank you. I may try your code if something goes wrong with what I came up with. Right now, I got the code to do what I want but not I'm having trouble with the formatting of the cells. In order to make the cells have "01" in it, I have to make it store that as text format but it will also show that green tip in the corner of the cell and also have that error box next to it asking if I want to ignore the error to get rid of that green tip in the corner of the cell. I have to do that with each cell that I store these numbers as text. Is there another way to get around this? I would like these cells to reflect "01" or any zeros in front of numbers in these cells with no error in formatting. Here is the code I am using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False

If Range("G3").Value = "" Then
    Range("A3").Value = ""
    Range("B3").Value = ""
    Range("C3").Value = ""
    Range("D3").Value = ""
    Range("E3").Value = ""
    Range("F3").Value = ""
    Range("H3").Value = ""
    Range("J3").Value = ""
    Range("K3").Value = ""
    Application.EnableEvents = True
    Exit Sub
End If

If Range("G3").Value <> "1550" Then
    GoTo Skip
Else
    Range("A3").Value = "01"
    Range("B3").Value = "011"
    Range("C3").Value = "A"
    Range("D3").Value = "2"
    Range("E3").Value = "123"
    Range("F3").Value = "B"
    Range("H3").Value = "MFR"
    Range("J3").Value = "1"
    Range("K3").Value = "456"
    Application.EnableEvents = True
    Exit Sub
End If

Skip:

If Range("G3").Value <> "1540" Then
    GoTo Skip2
Else
    Range("A3").Value = "01"
    Range("B3").Value = "011"
    Range("C3").Value = "A"
    Range("D3").Value = "15"
    Range("E3").Value = "123"
    Range("F3").Value = "B1"
    Range("H3").Value = "1455"
    Range("J3").Value = ""
    Range("K3").Value = "456"
    Application.EnableEvents = True
    Exit Sub
End If

Skip2:
'MsgBox "hello"

Application.EnableEvents = True

End Sub

 

[MARK858]

if I want to ignore the error to get rid of that green tip in the corner of the cell. I have to do that with each cell that I store these numbers as text.

If it is a contiguous range then select from the first cell (top left) to the last cell (bottom right), then ignore the tip on the first cell, the others will be removed (or remove the error checking option for numbers formatted as text or preceded by an apostrophe in File - Options - Formulas).

 

[data808]

If it is a contiguous range then select from the first cell (top left) to the last cell (bottom right), then ignore the tip on the first cell, the others will be removed (or remove the error checking option for numbers formatted as text or preceded by an apostrophe in File - Options - Formulas).

Thanks. That does work but once I remove all the values and re-enter them, it will give the error all over again. Is there a way in VBA to just automatically ignore the error? Or better yet, what format would I need to use if I wanted the value "01" to reflect in a cell without giving an error? Right, now if I leave it in General format and I type "01", it will show as "1". It will always remove the leading zero.

 

[MARK858]

The bit in brackets removes the error check from all workbooks, it won't reappear

 

[data808]

The bit in brackets removes the error check from all workbooks, it won't reappear

Thanks that worked great!