Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Forms Populate Combobox From Range Module

  Asked By: Pedro    Date: Jan 24    Category: MS Office    Views: 1092
  

I have got a bit of a problem which I would like to expand with people I can
see what I need to do and write the pseudo code for it but not too sure how
to write for real and i think i have got some simple scope issues



The objective



Using VBA forms

Initialise the form

Calls (module) PopulateComboBox (formname ,comboboxname,
nameofrange)

Pass the name of the current form

Pass the name of combo box on the form

Pass a named range

Take the data

Sort It

Populate the combo box on the form



Not too sure if i need to pass the formname but thought it was a better
practice.



Difficulties I having is getting to grasps with is the declarations of type
and the scope they require

.. is the better written as function or a sub ?

.. not sure on the byref / byval

.. not too sure about where the msforms.combobox should be used



Any other ideas and concepts link will be appreciated

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Volney Fischer     Answered On: Jan 24

This pseudo code  is too imprecise to tell me what you're trying to do. E.g.
what does "Pass the name of the current  form" mean? Pass it to where? From
where? Do what with it?

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

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?

 
Answer #2    Answered By: Sophie Campbell     Answered On: Jan 24

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

 
Didn't find what you were looking for? Find more on Forms Populate Combobox From Range Module Or get search suggestion and latest updates.




Tagged: