Chart sheet causing error

[Gustaf]

Hi all,

I'm having a problem at the last line of this code:

For Each ws In Workbooks(sFile).Sheets
If ... Then
cboSheet.AddItem ws.Name
End If
Next

The problem occurs when I step into a chart sheet, which results in a type mismatch error. Why does that happen how do you handle that?

Gustaf

 

[Bob Phillips]

You probably have ws defined as a variable of type worksheet. Either change
it to Object, or outsort the charts n the code.

HTH

Bob

 

[Gustaf]

You're right. The ws variable is indeed defined as Worksheet. It's preferred, because that's how it's done in the rest of the project. However, I don't think outsorting the charts would work, because the error occurs already on the "Next" line, before returning to the top of the loop.

Gustaf

 

[Bob Phillips]

If you keep it as type worksheet, you HAVE to outsort the charts, otherwise
it will just fail as you have found.

HTH

Bob

 

[Bob Phillips]

It is easily outsorted like so

Dim ws As Worksheet
For Each ws In Workbooks(sFile).Worksheets
If ... Then
cboSheet.AddItem ws.Name
End If
Next

HTH

Bob

 

[Gustaf]

Thank you very much!

Gustaf