Referencing a user form

[Geoff Edwards]

Previously you were very helpful when I was writing a procedure to set
the background colours of some controls.

We ended up with a procedure thus:

Public Sub SetBackgroundColours(frm As MSForms.UserForm)

(Do the stuff)

End Sub

I am now doing something similar (I don't think I need to bore you
with the details) which requires me to keep track of certain controls
in a form by building a list, and I need to check which form was last
worked over (to avoid repeating the process.


I have:

Public CurrentForm as MSForms.UserForm

Public Sub GetControls (frm as MSForms.UserForm)

If CurrentForm <> frm then
Get_A_List_Of_Controls_I_Want_From_frm
set CurrentForm = frm
End if

Colour_The_Controls_In_The_List

End Sub

When I try to compile this I get "Type Mismatch" on the line
If CurrentForm <> frm

I have tried several settings - Form, UserForm, MSForms.UserForm
(always making sure that CurrentForm and frm have the same definition)
without success.

What is the correct way to do this? I am obviously referencing the
form OK because it works in SetBackgroundColours. My problem is the
correct way of storing the value of the last frm examined.

TIA


Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

 

[Peter Hewett]

Hi Geoff Edwards

What you're actually trying to do is compare two objects. The code you are actually
executing is:
If CurrentForm.Controls.Item <> frm.Controls.Item Then

As the Controls property is the default member of UserForm and .Item is the default member
of Controls (since Controls is a collection object). VBA does a lot for you that you
frequently don't even realise.

I'm not sure if the Forms you're passing in are different instances of the same Form or
instances of different Forms. This will work for instances of different Forms:

If CurrentForm.Caption <> frm.Caption Then

HTH + Cheers - Peter

 

[Geoff Edwards]

Many thanks,

Geoff

Hi Geoff Edwards

What you're actually trying to do is compare two objects. The code you are actually
executing is:
If CurrentForm.Controls.Item <> frm.Controls.Item Then

As the Controls property is the default member of UserForm and .Item is the default member
of Controls (since Controls is a collection object). VBA does a lot for you that you
frequently don't even realise.

I'm not sure if the Forms you're passing in are different instances of the same Form or
instances of different Forms. This will work for instances of different Forms:

If CurrentForm.Caption <> frm.Caption Then

HTH + Cheers - Peter

Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

 

[Geoff Edwards]

Grrrr.

This isn't working!

If i have a breakpoint tin the initialize event for the form
frmAllocation then in the immediate window I can get:

? me.caption
Allocation to the Judiciary

as one would expect.

However, if have a breakpoint at the subroutine

Public Sub SetBackgroundColours (frm as UserForm)

and at the end of the initialize event SetBackGroundColours Me

when that break occurs, debug.print frm.caption just gives me an empty
string.

frm is obviously the right form because the code in
SetBackgroundColours does its job.

But it means I cannot do what I really want to do because whatever the
circumstances it seems that both CurrentForm.caption and frm.Caption
are empty strings!

(You were right in thinking that they are instances of different
forms, by the way)

Geoff

Hi Geoff Edwards

What you're actually trying to do is compare two objects. The code you are actually
executing is:
If CurrentForm.Controls.Item <> frm.Controls.Item Then

As the Controls property is the default member of UserForm and .Item is the default member
of Controls (since Controls is a collection object). VBA does a lot for you that you
frequently don't even realise.

I'm not sure if the Forms you're passing in are different instances of the same Form or
instances of different Forms. This will work for instances of different Forms:

If CurrentForm.Caption <> frm.Caption Then

HTH + Cheers - Peter

Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

 

[Peter Hewett]

Hi Geoff Edwards

Change the definition from:
Public Sub SetBackgroundColours (frm as UserForm)

to:
Public Sub SetBackgroundColours(Byval frm As Object)

Do this for *all* procedures that have a parameter declared as MSForms.UserForm. For some
reason passing in a reference to a Sub/Procedure/Property that declares a parameter as
MSForms.UserForm masks the Forms Caption property. For some reason it can neither be read
nor updated. All other properties work correctly though!

HTH + Cheers - Peter

 

[Geoff Edwards]

Amazing.

Many thanks.

Geoff

Hi Geoff Edwards

Change the definition from:
Public Sub SetBackgroundColours (frm as UserForm)

to:
Public Sub SetBackgroundColours(Byval frm As Object)

Do this for *all* procedures that have a parameter declared as MSForms.UserForm. For some
reason passing in a reference to a Sub/Procedure/Property that declares a parameter as
MSForms.UserForm masks the Forms Caption property. For some reason it can neither be read
nor updated. All other properties work correctly though!

HTH + Cheers - Peter

Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

 

[Geoff Edwards]

Sigh.

This still doesn't work.

First of all, the code throws an error the first time it is run
because the Object CurrentForm is empty. I have dealt with that by
trapping the error and setting CurrentForm to any old form (one which
cannot be the first to call the subroutine in any circumstances).

But I still have problem:

Public CurrentForm as Object

Public Sub SetBackgroundColours(frm As Object)

Dim e As Integer
Dim test As String
On Error Resume Next any old form in there
test = CurrentForm.Caption
e = Err.Number
Err.Clear
On Error GoTo 0
If e = 91 Then Set CurrentForm = New frmBundle

If CurrentForm.Caption <> frm.Caption Then
GetColourableControls frm
Set CurrentForm = frm
End If

Throws the error "Class does not support automation" at:
If CurrentForm.Caption <> frm.Caption Then

I must still be getting something wrong.

Hi Geoff Edwards

Change the definition from:
Public Sub SetBackgroundColours (frm as UserForm)

to:
Public Sub SetBackgroundColours(Byval frm As Object)

Do this for *all* procedures that have a parameter declared as MSForms.UserForm. For some
reason passing in a reference to a Sub/Procedure/Property that declares a parameter as
MSForms.UserForm masks the Forms Caption property. For some reason it can neither be read
nor updated. All other properties work correctly though!

HTH + Cheers - Peter

Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

 

[Peter Hewett]

Hi Geoff Edwards

To be honest I've no real idea what your code is trying to do. But experience tells me
you're going about it the wrong way. When code gets this messy it general due to poor (or
no) design. Go back to basics and define what it is you're trying to do. Then try to
develop some straightforward code to do it.

If your object CurrentForm is not initialised then rather than use error handling, perform
a specific test to see if it's unitinitialised:
If CurrentForm Is Nothing Then
' It's uninitialised
Else
' It's initialised
EndIf

Assigning it a dummy value is not good practice either, make sure the above logic can
handle the object variable when it's uninitialised.

The following code works for me:

Public Sub ShowForm()
Dim frmT1 As frmTest1
Dim frmT2 As frmTest2

Set frmT1 = New frmTest1
Load frmT1
frmT1.show

Set frmT2 = New frmTest2
Load frmT2
frmT2.show

TestCaption frmT1, frmT2

Unload frmT1
Unload frmT2

Set frmT1 = Nothing
Set frmT2 = Nothing
End Sub

Public Sub TestCaption(ByVal objT1 As Object, _
ByVal objT2 As Object)
If objT1.Caption = objT2.Caption Then
MsgBox "Same caption"
Else
MsgBox "Different caption"
End If

Set objT1 = Nothing
Set objT2 = Nothing
End Sub

HTH + Cheers - Peter

 

[Geoff Edwards]

When code gets this messy it general due to poor (or

no) design.

Oh how the truth hurts )

Many many thanks. I'll try to work it out from here.

But if you want to know what I was trying to do, for the sake of
completeness: I was trying to improve on a routine I was helped with
here where the code trotted through the controls in a form looking for
combo, list or text boxes and changing the backgrounds to grey if they
are disabled (avoids confusing among my very un-IT literate users).

I had that working OK, but then decided that it was wasteful to loop
through all the controls in the form each time a user clicked a
control which enabled or disabled a few others.

So I thought I would use the loop which was being run to build a list
of the controls which matched the criteria thus:

Private ColourableControls() as controls
Private TestCaption as string

Private Sub GetColourableControls(source As Object)
Dim ctrl As Control
Dim ctrlcounter As Integer
ReDim ColourableControls(100)
For Each ctrl In source.Controls
If TypeOf ctrl Is MSForms.TextBox Or _
TypeOf ctrl Is MSForms.ListBox Or _
TypeOf ctrl Is MSForms.ComboBox Then
ctrlcounter = ctrlcounter + 1
Set ColourableControls(ctrlcounter) = ctrl
End If
Next
ReDim Preserve ColourableControls(ctrlcounter)
End Sub

I could then have this called from each form's code when a relevant
control is clicked::

SetColourableControls(frm as Object)
If frm.caption <> TestCaption then ' Only get the controls
GetColourableControls frm ' if the haven't been
TestCaption=frm.caption ' got already
End If

Dim ctrlcounter As Integer
For ctrlcounter = 1 to UBound(ColourableControls)
With ColourableControls(ctrlcounter)
If .Enabled Then
.BackColor = &H80000005

Else
.BackColor = &H8000000F
End If
End With
Next

End Sub

Regards, (and many thanks for your time)

Geoff

Hi Geoff Edwards

To be honest I've no real idea what your code is trying to do. But experience tells me
you're going about it the wrong way. When code gets this messy it general due to poor (or
no) design. Go back to basics and define what it is you're trying to do. Then try to
develop some straightforward code to do it.

If your object CurrentForm is not initialised then rather than use error handling, perform
a specific test to see if it's unitinitialised:
If CurrentForm Is Nothing Then
' It's uninitialised
Else
' It's initialised
EndIf

Assigning it a dummy value is not good practice either, make sure the above logic can
handle the object variable when it's uninitialised.

The following code works for me:

Public Sub ShowForm()
Dim frmT1 As frmTest1
Dim frmT2 As frmTest2

Set frmT1 = New frmTest1
Load frmT1
frmT1.show

Set frmT2 = New frmTest2
Load frmT2
frmT2.show

TestCaption frmT1, frmT2

Unload frmT1
Unload frmT2

Set frmT1 = Nothing
Set frmT2 = Nothing
End Sub

Public Sub TestCaption(ByVal objT1 As Object, _
ByVal objT2 As Object)
If objT1.Caption = objT2.Caption Then
MsgBox "Same caption"
Else
MsgBox "Different caption"
End If

Set objT1 = Nothing
Set objT2 = Nothing
End Sub

HTH + Cheers - Peter

Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

 

[Geoff Edwards]

I did it by picking up frm.caption as a string.

Many thanks - I learned a lot.

Geoff

Oh how the truth hurts )

Many many thanks. I'll try to work it out from here.

But if you want to know what I was trying to do, for the sake of
completeness: I was trying to improve on a routine I was helped with
here where the code trotted through the controls in a form looking for
combo, list or text boxes and changing the backgrounds to grey if they
are disabled (avoids confusing among my very un-IT literate users).

I had that working OK, but then decided that it was wasteful to loop
through all the controls in the form each time a user clicked a
control which enabled or disabled a few others.

So I thought I would use the loop which was being run to build a list
of the controls which matched the criteria thus:

Private ColourableControls() as controls
Private TestCaption as string

Private Sub GetColourableControls(source As Object)
Dim ctrl As Control
Dim ctrlcounter As Integer
ReDim ColourableControls(100)
For Each ctrl In source.Controls
If TypeOf ctrl Is MSForms.TextBox Or _
TypeOf ctrl Is MSForms.ListBox Or _
TypeOf ctrl Is MSForms.ComboBox Then
ctrlcounter = ctrlcounter + 1
Set ColourableControls(ctrlcounter) = ctrl
End If
Next
ReDim Preserve ColourableControls(ctrlcounter)
End Sub

I could then have this called from each form's code when a relevant
control is clicked::

SetColourableControls(frm as Object)
If frm.caption <> TestCaption then ' Only get the controls
GetColourableControls frm ' if the haven't been
TestCaption=frm.caption ' got already
End If

Dim ctrlcounter As Integer
For ctrlcounter = 1 to UBound(ColourableControls)
With ColourableControls(ctrlcounter)
If .Enabled Then
.BackColor = &H80000005

Else
.BackColor = &H8000000F
End If
End With
Next

End Sub

Regards, (and many thanks for your time)

Geoff



Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

 

[Peter Hewett]

Hi Geoff Edwards

Glad you got there + CHeers - Peter

I did it by picking up frm.caption as a string.

Many thanks - I learned a lot.

Geoff





Geoff Edwards
Leeds, UK

e-mail: (e-mail address removed)
(replace the stopspambot bit with some
version of my name)

HTH + Cheers - Peter