MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Dave Hawley on February 23, 2001 12:57 PM

Data Management Subroutines 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


Edward, Im not sure I follow what it is you are trying to do here.


To populate your list box with all sheet names why no use:


Private Sub UserForm_Initialize()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
ListBox1.AddItem (Sht.Name)
Next
End Sub


And to delete the select Sheet(s) why not use:


Private Sub CommandButton1_Click()
Dim i As Integer, ShtName As String
Application.DisplayAlerts = False
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
ShtName = ListBox1.List(i)
Sheets(ShtName).Delete
End If
Next i
Application.DisplayAlerts = True
End Sub


This is for a Listbox with Multiselect set to true.


Dave
OzGrid Business Applications