VB problem - obsolete command/wider issues

[cheekbones3]

Hi all,

I'm currently trying to update a macro that was written in an earlier version of Excel, and I'm getting an error that I'm not sure what to do with.

The module that's getting called early in the program starts like this following (excluding Dim statements):

  For iCount = 0 To 10
    Workbooks(MacroWorkbookName).Worksheets("OpenBookList").Cells(4 + iCount, 2).Value = ""
  Next iCount
  iCount = 0
  For Each bk In Workbooks
    Workbooks(MacroWorkbookName).Worksheets("OpenBookList").Cells(4 + iCount, 2).Value = bk.Name
    iCount = iCount + 1
  Next bk

I can see that it seems to be making a list of the worksheet names. I'm not sure why it's doing this (maybe they're called later), but anyway, my problem is the "Subscript Out Of Range" error for the Worksheets("OpenBookList") part - OpenBookList is not defined as a variable.

I'm wondering:

1) Is this a reference that worked in a previous version of VB/Excel, but is now obsolete and simply acts as a variable?
2) If so, is there a new command I can insert here?
3) The macro sits in my Personal.xls workbook, but is supposed to work on whatever workbook is active when called. I'm wondering if I've made a mistake in how I called the macro?

More fundamentally, I think this program may need a wider overhaul, and if this particular question has no solution, would someone fancy having a look at the overall code to point out what needs to be done? I suspect there isn't much that needs fixed!

Thanks,
Ian

 

[cheekbones3]

With Workbooks(MacroWorkbookName).DialogSheets("ExtractDialogue")

 

[VoG]

Sorry but I cannot see why that should give a mismatch error. I've just tried a very simplified version of that dialog sheet code and didn't get an error.

 

[cheekbones3]

Hmm, puzzling indeed. Thanks for your help!

I suppose I'll have to keep playing with it and see if I can work out what the problem is.

 

[cheekbones3]

For some reason, after not doing anything noticably different, it's started working! The only thing I can think of is that I closed a particular spreadsheet.