Problem passing a string array
Posted by Edward Clutter on February 23, 2001 11:49 AM
Hi Everyone, Please help me!
I cannot figure out how to pass a string array properly. I have a Userform which has a listbox on it. The listbox contains the names of all of the current worksheets and the user is asked to select which worksheet to delete. Everything works fine until I double click on the worksheet I want to delete at which point it tells me "out of range". It seems to be having trouble with sSheetNames array in the Private Sub lstWorksheets_DblClick routine listed below. Please tell me what I am doing wrong in the way I am passing this string from sub to sub.
thanks very much.
edward
-------
( in general module )
'
'
' Data Management Subroutines
'
'
Option Explicit
Dim cSheets As Integer
Dim sSheetNames() As String
(under userform for deleting data)
Private Sub UserForm_Initialize()
Dim ws As Object 'worksheet
ReDim sSheetNames(1 To 10)
lstWorksheets.Clear
cSheets = 0
For Each ws In ActiveWorkbook.Sheets
cSheets = cSheets + 1
'Redimension arrays if necessary
If UBound(sSheetNames) < cSheets Then
ReDim Preserve sSheetNames(1 To cSheets + 5)
End If
'Save name of sheet
sSheetNames(cSheets) = ws.Name
'Add sheet name to list box
lstWorksheets.AddItem sSheetNames(cSheets)
Next
End Sub
(under same user form)
Private Sub lstWorksheets_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' i tried this on 0223 when i could not get the delete button to work,
' i might end up removing this later
' DeleteDataSet
ReDim sSheetNames(1 To 10)
Dim i As Integer
For i = 0 To lstWorksheets.ListCount - 1
If lstWorksheets.Selected(i) Then
ActiveWorkbook.Sheets(sSheetNames(i + 1)).Delete
End If
Next
End Sub