what does "Pass the name of the current  form" mean? pass  it to where? From

where? Do what with it?

I have many forms  so I using this to populate  the combo  box. I am having to
write this as a simple  project to do what my friend requires (he is a non
vba programmer - intermediate user and this is why I am having to read data
in for a range  so he can just go and add extra items in to the combo box)

I had done something similar before but on the form I created an temp array
and then called sub which were attached to the forms code  so was very messy
and i would have to repeat the code on every form

So the objective was to take this idea and create a module  so I can call it
on any form.


Private Sub form1_Initialize()
Dim TempArray() As Variant
Call GetWorkSheetNames(TempArray)
Call BubbleSort(TempArray)
Call PopulateCombo(TempArray, cboWorksheetNames)
End Sub

Private Sub PopulateCombo(MyArray() As Variant, cbname As MSForms.ComboBox)

Dim i As Integer
For i = 1 To UBound(MyArray)
With cbname
.AddItem MyArray(i)
End With
Next

End Sub

Private function  BubbleSort(MyArray() As Variant) As Variant

Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim List As String

First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i

End Function

Private Function GetWorkSheetNames(MyArray() As Variant) As Variant

' Get All Sheet Names

Dim wksht As Worksheet
Dim i As Long
'Reset Array & Array Counter
Erase MyArray
i = 0

For Each wksht In ActiveWorkbook.Worksheets

'Skips Control Sheet
If Not wksht.name = "ZZZ Control Sheet" Then
i = i + 1
ReDim Preserve MyArray(1 To i)
MyArray(i) = wksht.name
End If
Next wksht


End Function



Perhaps a simple description of what bits you're having trouble with would
be the better way to go.


Well like i say i am trying to work out how i can pass 3 parameters so it
reuses the module to do the same job

Also, what research have you done on the Internet regarding what you want to

do? What did you find? What did you try? What did/didn't it do?

I was getting errors about Byref so I thought it was scope  issue and also
what i needed to pass to a module to ensure it populated

So I created a module and provided in a workbook for you to examine and you
might see clearer what I am trying to do

homepage.ntlworld.com/.../testmodule.xls

you will see the forms and the initialise unit and module

sorry all this is to try and write  reuseable and neater code

I don't know if i needed to pass the formname but thought i was declare it
but you will probably a better way of doing this