Passing control names to Functions??

[JB]

Hi Folks,

I'm trying to pass the names of controls in a form to a Module so that I can
create a generic function to handle resizing controls regardless of the form
name, list name etc but don't know how to do it......

I have a form called Form1 and a listbox in the form called ListBox1.

I've declared these in the form and then set them to the control names

Public FormName As String
Public ListName As String
Public WidthofList As String

FormName = Me.Name
ListName = Me.lstCommName.Name
WidthofList = Me.lstCommName.Width

I also have an array which I set to testit() Then I pass the array into the
function (which works fine) like..

testit() = MyArray
ResizeList testit()

All ok so far but I get an object required error when it runs in my module!
here's the code..

Public Function ResizeList(testit() As String)

Dim aTempArray() As String 'temp array to hold items for length check
Dim iCharcount As Integer 'number of characters in the committee name
Dim iHighNum As Integer 'highest number of characters so far
Dim i As Integer

aTempArray() = testit()

iHighNum = 0

For i = 0 To UBound(aTempArray, 1)
iCharcount = Len(aTempArray(i))

If iCharcount > iHighNum Then
iHighNum = iCharcount
Else
iHighNum = iHighNum
End If
Next

If iHighNum >= "120" Then
FormName.ListName.Width = WidthofList
FormName.ListName.ColumnWidths = "640"
ElseIf iHighNum >= "90" And iCharcount < "120" Then
FormName.ListName.Width = WidthofList
FormName.ListName.ColumnWidths = "440"
ElseIf iHighNum < "90" Then
FormName.ListName.Width = WidthofList
FormName.ListName.ColumnWidths = "328"
End If

End Function

Any Ideas???

Cheers

 

[JGM]

Hi JB,

Did you debug your code step by step before asking for help? If you had, you
would have noticed where the problem was in 2 seconds....

Anyway, here it is:

Public FormName As String
Public ListName As String ..
..
..
FormName.ListName.Width = WidthofList

You declare 2 string variables. Then you use one of the string as a property
for the other string (ListName on FormName), and then you try to apply an
object property to a string (Width)... No can do!

You have to declare an object like:
Public FormName As UserForm
or
Public FormName As Object
Public ListName As Control
(Sometimes, some properties, like Caption, are not returned with "As
UserForm" but will be with "As Object")

And then Set the object to an existing instance, as in

Set FormName = UserForm1
Set ListName = FormName.ComboBox1

or something along those lines...

HTH
Cheers!

 

[JB]

All well and good but I still can't get this to work after trying your
solution...
I declared the userform and control in the form then passed them into the
module but I can't seem to figure out how to get the FormName

'<------form code--->
Dim FormName As UserForm
Dim ListName As Control
Dim WidthofList As String

testit() = varrayshort

Set FormName = Form1
Set ListName = Form1.ListBox1

WidthofList = Form1.ListBox1.Width

ResizeList testit(), FormName, ListName, WidthofList
'<------end form code---->

'<-------module code---------->
Public Function ResizeList(testit() As String, FormName As UserForm,
ListName As Control, WidthofList As String)

If iHighNum >= "120" Then
FormName.ListName.Width = WidthofList
formanme.ListName.ColumnWidths = "640"
ElseIf iHighNum >= "90" And iCharcount < "120" Then
FormName.ListName.Width = WidthofList
FormName.ListName.ColumnWidths = "440"
ElseIf iHighNum < "90" Then
FormName.ListName.Width = WidthofList
FormName.ListName.ColumnWidths = "328"
End If
'<-----------end module code----------->

 

[JGM]

Hi JB,

Where is the "Form Code", i.e. is it in a button_Click event, a
Control_Change event, a Form_Initialize event...?
and the "Module Code", in which module is it?

What are you trying to achieve? If you give us a general picture, maybe that
an entirely different solution can be applied to your situation....

Cheers!

 

[JB]

Hi JGM,
The code is in the Form_Initialize event and the module code is in a
standard module.

I'm basically just trying to have the form passed as an object with the
listbox so that it can be controlled in the module, but, there are lots of
forms so I want to pass the form name and listbox name as a variable (well
that kind of Idea anyway) so that no matter the name of the form or the
listbox it can be passed to the module and handled as required.

Hope this helps

John

 

[JGM]

Hi John,

Try this:

in Initialize

_______________________________________
Private Sub UserForm_Initialize()

mytestit() = varrayshort
'from your example I do not know the purpose of this aray

Me.ListBox1.ColumnWidths = Module1.ResizeList(mytestit())

End Sub
_______________________________________

In a standard module originally named "Module1"!:

_______________________________________
Public Function ResizeList(testit() As String) As String

If iHighNum >= "120" Then
ResizeList = "640"
ElseIf iHighNum >= "90" And iCharcount < "120" Then
ResizeList = "440"
ElseIf iHighNum < "90" Then
ResizeList = "800"
End If

End Function
_______________________________________

I do not think that you can actually set (or even read) a form control
property outside of a form code module.
If so, please, someone show us how!
But, in your particular case, you do not need to pass the whole form or its
controls... just call the function to set the properties.

HTH
Cheers!

 

[Lars-Eric Gisslén]

JGM,

Private Sub UserForm_Initialize()
Module1.SetCtrlText Me.TextBox1
End Sub

' In a code module
Sub SetCtrlText(oCtrl As Object)
oCtrl.Text = "Text set by a Sub in a Module"
End Sub

Regards,
Lars-Eric

 

[JGM]

Thanks Lars,

I guess I tried to make it too complicated by passing the form, then a
control that belonged to that form, while all along all that was necessary
was to pass only the control itself! That's what happens when you see some
code and try to fit an answer to that particular code! I guess I did not
look outside the box on this one!

So , John, all you have to do is:

_______________________________________
Private Sub UserForm_Initialize()

Dim WidthofList As String

'testit() = varrayshort
Dim testit() As String
'I had to do that because I did not want to mess around
'with array variables in my tests, especially since it is not
'used in the function call...

WidthofList = Me.ListBox1.Width

ResizeList testit(), Me.ListBox1, WidthofList

End Sub
_______________________________________

and

_______________________________________
Public Function ResizeList(testit() As String, myListbox As ListBox,
WidthofList As String)

If iHighNum >= "120" Then
myListbox.Width = WidthofList
myListbox.ColumnWidths = "640"
ElseIf iHighNum >= "90" And iCharcount < "120" Then
myListbox.Width = WidthofList
myListbox.ColumnWidths = "440"
ElseIf iHighNum < "90" Then
myListbox.Width = WidthofList
myListbox.ColumnWidths = "328"
End If

End Function
_______________________________________

If you want to use the same function for, let's say ComboBox and Listbox,
then change
myListbox As ListBox
for
myListbox As Control

or
myListbox As Object

By the way, why do you have the line
myListbox.Width = WidthofList

in the function ...
It does not seem to do anything...

In the Initialize code you have
WidthofList = Me.ListBox1.Width, then you pass
WidthofList
to the function, but it does not do anything...

Just curious!

HTH
Cheers!

 

[JB]

Hi Guys,

Thanks for your time and patience! That was what I was looking for

Cheers

J