ListBox + With End With

[pit84]

Hi


With FormExtraSheets.ListBoxExtraSheets
ActiveWorkbook.Worksheets(.Value).Delete
End With
- subscript out of range

With FormExtraSheets.ListBoxExtraSheets
shtName = .Value
ActiveWorkbook.Worksheets(shtName).Delete
End With
- works fine

Could someone explain to me, why, please, what the difference is

 

[The Gunslinger]

because outside of the activeworkbook.worksheets function, the reference in the with statement is attached, but within it the reference inferred in the with statement is not carried through, i've come across oddities like this in the past... and have something similarly annoying bugging me right now !

 

[Fluff]

One possibility is that the list box is returning a number (say 100). So in your first code you would be trying to delete sheet(100) (ie sheet index). But in the second code if ShtName has been declared as a string you would be deleting sheet("100") (ie sheet name).

So 2 questions
1) What is the listbox value when you get the error?
2) Have you declared shtName & if so what have you declared it as?