Infinite Loop in Userform Initialization

Question

I'm initializing a userform in an Excel VBA macro. When I go to populate the items in a combobox, I get stuck in an infinite loop, but I don't know why. Here is my code:

Private Sub UserForm_Initialize()

  'Populate the combobox with the months
  Me.cboCurrMth.SetFocus
  Dim cMth As Range
  Dim ws As Worksheet
  Set ws = Sheet1
  For Each cMth In ws.Range("months")
      With Me.cboCurrMth
          .AddItem cMth.Value
          .List(.LineCount - 1, 1) = cMth.Offset(0, 1).Value
      End With
  Next cMth

End Sub

The named range "months" includes all 12 rows and 2 columns where the first column is an integer (from 1 to 12) and the second column is the string name of each month.

Anyone see why this loop won't terminate? Thanks.

Yes. The loop is infinite, so the debugger just keeps stepping through the code line by line by line by... There is no information there that is useful to me. — AndroidDev
– AndroidDev, Commented May 26, 2012 at 6:15
Do you have any Change/Click Event for the ComboBox? When an item is added to the ComboBox, the change event is fired. So probably you have something there which is causing the loop? — Siddharth Rout
– Siddharth Rout, Commented May 26, 2012 at 12:31

Jon Crowell · Accepted Answer · 2012-05-26 12:38:01Z

1

You should rarely select cells or ranges in your production VBA code. However, it can be extremely helpful for debugging purposes.

Add a .select in your For Each loop and then step through your code. You should be able to figure out what's wrong.

Private Sub WhyAmIInfinite()

  'Loop through and select the months
  Dim cMth As Range
  Dim ws As Worksheet
  Set ws = Sheet1
  For Each cMth In ws.Range("months")
        cMth.Select
  Next cMth

End Sub

I set up a worksheet with a range exactly as you describe and the loop exited as I expected it to. I removed the combobox from my example because I wanted to isolate the loop itself.

answered May 26, 2012 at 12:38

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AndroidDev Over a year ago

Thank you. This was a great idea. It turns out that I was iterating through my range correctly, although I needed to redefine my range to only look at the left column (no titles, so integer values 1 - 12 only). I'm still not sure why my loop was infinite, but once I saw that I was looping through 26 cell (2 titles and 24 cells), I knew that I had to narrow my named range. This solved the infinite loop problem, so all is well. Thank you!

Jon Crowell Over a year ago

Glad it worked. I can't tell you how many times I've slapped my forehead on something simple when I use this technique to troubleshoot.

Sudhakar B · Accepted Answer · 2012-05-26 03:12:49Z

1

I have wrote the following code and it is worked for me. I am using Excel 2003.

ActiveSheet.Shapes("cmbMonths").Select

Dim currMonth As Range
With Selection
    For Each currMonth In Range("Months")
        .AddItem currMonth.Value
    Next
End With

This line ".List(.LineCount - 1, 1) = cMth.Offset(0, 1).Value" is giving error for me 'Saying member not found"

Please select your month cells once again and give the name for the selected range and try again. Hope it works.

answered May 26, 2012 at 3:12

Sudhakar B

1,57311 silver badges19 bronze badges

3 Comments

AndroidDev Over a year ago

Thank you for doing this. However, you apparently put your combobox directly on the worksheet. Mine is on a userform. That may be why your code errors out on that line while mine doesn't. But I still have the infinite loop problem.

Sudhakar B Over a year ago

@user1024973, I have placed the drop down on the user form and shown when the user clicked on a button. Its working(No infinite loop).

AndroidDev Over a year ago

Then how can your code say "ActiveSheet.Shapes("cmbMonths").Select"? ActiveSheet refers to a worksheet, not a userform, doesn't it?

Collectives™ on Stack Overflow

Infinite Loop in Userform Initialization

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related