Crashing Excel with simple data validation

[ieJasonW]

I'm building a small interface and I need the user to input a value (from 1 to 50) in one cell, and for the spreadsheet to dynamically create a little table with the number of columns they enter. Here's the code I'm using -- its crashing my Excel 2007. Just an FYI, its running in compatability mode b/c the original file was started in Excel 2003:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, nl As Integer
nl = Cells(20, 4).Value
For i = 1 To nl
Cells(23, 4 + i).Value = i
Next i
End Sub

Thanks in advance for why it might be crashing.
ieJasonW

 

[ieJasonW]

I should add that the cell where a user inputs their number of columns has data validation on it -- so they can only choose values from 1 to 50 from a drop down list.



ieJasonW

 

[Smitty]

Welcome to the Board!

Try turning off events. I'd imagine you're getting stuck in a recursive loop, in which the change event gets called ad infinitum.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, nl <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        <br>        nl = Cells(20, 4).Value<br>        <br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> nl<br>                Cells(23, 4 + i).Value = i<br>            <SPAN style="color:#00007F">Next</SPAN> i<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

You also might want to try using the Target property of the change event.

Hope that helps,

 

[ieJasonW]

I was getting into a loop. It just kept churning though!

Thanks for the help. It cleared things right up.

Also, as soon as I posted I tried to add 'End' to the code. It seemed to work too. Thanks for the help! ieJasonW

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, nl As Integer
nl = Cells(20, 4).Value 'Number of Layers

For i = 1 To nl
Cells(23, 4 + i).Value = i
Next i
End '**********Here********

End Sub

 

[Smitty]

I don't know how reliable End will be in this case because you're addressing the problem after it's occurred. EnableEvents nixes it from the start.

 

[Jon von der Heyden]

It's not a good idea to use End. My understanding is that it is similar to quitting excel by terminating the Excel.exe process in Task Manager.

 

[Smitty]

It's not a good idea to use End. My understanding is that it is similar to quitting excel by terminating the Excel.exe process in Task Manager.

Yeah, it's kind of a brute force approach instead of addressing the actual issue.

 

[ieJasonW]

Thanks for the help everyone! After Smitty's suggestion I read up on ActiveEvents in a Business Solutions book I bought. It explained why my trying to change the sheet caused an infinite loop. That makes tons of sense.

Also, now I need to do some basic formatting for the chart I'm making - color fill, borders that are grey instead of black, etc. And I'm a little confused with how to refer to each of these properties/objects. For example, is there somewhere I can find a list of the names for the line weights and what they look like? What about for colors? Do I need to always look up a color from the custom color tool in the cell format portion of the menu bar? And what are the members of the format object?

I hope that all makes sense. I'm getting confused when I read about words like objects, properties, methods, classes, etc. I don't understand why I'm so lost! Hahaha.

ieJasonW

 

[Smitty]

You can access most of them. Here's an example on cell background colors:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> GetColors()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 56<br>            Cells(i, 1) = i<br>            Cells(i, 2).Interior.ColorIndex = i<br>        <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

A great place to start is the VB helpfile, and the MSDN has a lot of stuff too.