ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Referencing a UserForm (https://www.excelbanter.com/excel-programming/433310-referencing-userform.html)

Patrick C. Simonds
Referencing a UserForm
 
I an trying to pair down my code. Below is and example of some code that I
run outside of UserForm1. In an effort to reduce my file size, is there any
way to eliminate all the "UserForm1" reference?


Sub TextBoxData()

UserForm1.TextBox12.Value = UserForm1.rng(1, 4) & " " & UserForm1.rng(1,
3) ' First and Last Name

UserForm1.TextBox5.Value = Range("AB6")
' No Show Count

If UserForm1.TextBox5.Value = 2 Then
UserForm1.TextBox5.BackColor = &HFFFF&
End If

If UserForm1.TextBox5.Value = 3 Then
UserForm1.TextBox5.BackColor = &HFF&
End If

If UserForm1.TextBox5.Value 3 Then
UserForm1.TextBox5.BackColor = &H80000012
UserForm1.TextBox5.ForeColor = &HFFFFFF
End If

UserForm1.TextBox13.Value = UserForm1.rng(1, 6)
' Date of No Show
UserForm1.TextBox13.Value = Format$(UserForm1.TextBox13.Value, "ddd
dd mmm yy")


UserForm1.TextBox1.Value = UserForm1.rng(1, 7).Text
' Summary Of Conversation

UserForm1.TextBox4.Value = UserForm1.rng(1, 13)
' Date of Contact
UserForm1.TextBox4.Value = Format$(UserForm1.TextBox4.Value, "ddd dd
mmm yy")

UserForm1.TextBox3.Value = UserForm1.rng(1, 14).Text
' Time of Contact
UserForm1.TextBox3.Value = Format$(UserForm1.TextBox3.Value,
"HH:MM")

UserForm1.TextBox7.Value = UserForm1.rng(1, 17)
' Date of 1st Additional Contact Attempt
UserForm1.TextBox7.Value = Format$(UserForm1.TextBox7.Value, "ddd dd
mmm yy")

UserForm1.TextBox6.Value = UserForm1.rng(1, 18).Text
' Time of 1st Additional Contact Attempt
UserForm1.TextBox6.Value = Format$(UserForm1.TextBox6.Value,
"HH:MM")

UserForm1.TextBox9.Value = UserForm1.rng(1, 19)
' Date of 2nd Additional Contact Attempt
UserForm1.TextBox9.Value = Format$(UserForm1.TextBox9.Value, "ddd dd
mmm yy")

UserForm1.TextBox8.Value = UserForm1.rng(1, 20).Text
' Time of 2nd Additional Contact Attempt
UserForm1.TextBox8.Value = Format$(UserForm1.TextBox8.Value,
"HH:MM")

UserForm1.TextBox11.Value = UserForm1.rng(1, 21)
' Date of 3rd Additional Contact Attempt
UserForm1.TextBox11.Value = Format$(UserForm1.TextBox11.Value, "ddd
dd mmm yy")

UserForm1.TextBox10.Value = UserForm1.rng(1, 22).Text
' Time of 3rd Additional Contact Attempt
UserForm1.TextBox10.Value = Format$(UserForm1.TextBox10.Value,
"HH:MM")

UserForm1.TextBox2.Value = UserForm1.rng(1, 11)
' How many rides Canceled

If UserForm1.rng(1, 11).Text < "" Then
UserForm1.TextBox2.Visible = True
UserForm1.Label7.Visible = True
UserForm1.OptionButton10.Value = True
End If


End Sub


Leith Ross[_793_]
Referencing a UserForm
 

Patrick C. Simonds;479031 Wrote:
I an trying to pair down my code. Below is and example of some code that
I
run outside of UserForm1. In an effort to reduce my file size, is there
any
way to eliminate all the "UserForm1" reference?


Sub TextBoxData()

UserForm1.TextBox12.Value = UserForm1.rng(1, 4) & " " &
UserForm1.rng(1,
3) ' First and Last Name

UserForm1.TextBox5.Value = Range("AB6")
' No Show Count

If UserForm1.TextBox5.Value = 2 Then
UserForm1.TextBox5.BackColor = &HFFFF&
End If

If UserForm1.TextBox5.Value = 3 Then
UserForm1.TextBox5.BackColor = &HFF&
End If

If UserForm1.TextBox5.Value 3 Then
UserForm1.TextBox5.BackColor = &H80000012
UserForm1.TextBox5.ForeColor = &HFFFFFF
End If

UserForm1.TextBox13.Value = UserForm1.rng(1, 6)
' Date of No Show
UserForm1.TextBox13.Value = Format$(UserForm1.TextBox13.Value, "ddd
dd mmm yy")


UserForm1.TextBox1.Value = UserForm1.rng(1, 7).Text
' Summary Of Conversation

UserForm1.TextBox4.Value = UserForm1.rng(1, 13)
' Date of Contact
UserForm1.TextBox4.Value = Format$(UserForm1.TextBox4.Value, "ddd dd
mmm yy")

UserForm1.TextBox3.Value = UserForm1.rng(1, 14).Text
' Time of Contact
UserForm1.TextBox3.Value = Format$(UserForm1.TextBox3.Value,
"HH:MM")

UserForm1.TextBox7.Value = UserForm1.rng(1, 17)
' Date of 1st Additional Contact Attempt
UserForm1.TextBox7.Value = Format$(UserForm1.TextBox7.Value, "ddd dd
mmm yy")

UserForm1.TextBox6.Value = UserForm1.rng(1, 18).Text
' Time of 1st Additional Contact Attempt
UserForm1.TextBox6.Value = Format$(UserForm1.TextBox6.Value,
"HH:MM")

UserForm1.TextBox9.Value = UserForm1.rng(1, 19)
' Date of 2nd Additional Contact Attempt
UserForm1.TextBox9.Value = Format$(UserForm1.TextBox9.Value, "ddd dd
mmm yy")

UserForm1.TextBox8.Value = UserForm1.rng(1, 20).Text
' Time of 2nd Additional Contact Attempt
UserForm1.TextBox8.Value = Format$(UserForm1.TextBox8.Value,
"HH:MM")

UserForm1.TextBox11.Value = UserForm1.rng(1, 21)
' Date of 3rd Additional Contact Attempt
UserForm1.TextBox11.Value = Format$(UserForm1.TextBox11.Value, "ddd
dd mmm yy")

UserForm1.TextBox10.Value = UserForm1.rng(1, 22).Text
' Time of 3rd Additional Contact Attempt
UserForm1.TextBox10.Value = Format$(UserForm1.TextBox10.Value,
"HH:MM")

UserForm1.TextBox2.Value = UserForm1.rng(1, 11)
' How many rides Canceled

If UserForm1.rng(1, 11).Text < "" Then
UserForm1.TextBox2.Visible = True
UserForm1.Label7.Visible = True
UserForm1.OptionButton10.Value = True
End If


End Sub

Hello Patrick,

I have changed your Sub to take an argument which is a UserForm. The
object variable "frm" is set to reference the UserForm object passed to
the Sub. The object variable then takes the place of the hard coded
reference "UserForm1". See the changes below.

===============================
Sub TextBoxData(ByRef frm As UserForm)

frm.TextBox12.Value = frm.Rng(1, 4) & " " & frm.Rng(1, 3) ' First and
Last Name

frm.TextBox5.Value = Range("AB6")
' No Show Count

If frm.TextBox5.Value = 2 Then
frm.TextBox5.BackColor = &HFFFF&
End If

If frm.TextBox5.Value = 3 Then
frm.TextBox5.BackColor = &HFF&
End If

If frm.TextBox5.Value 3 Then
frm.TextBox5.BackColor = &H80000012
frm.TextBox5.ForeColor = &HFFFFFF
End If

frm.TextBox13.Value = frm.Rng(1, 6)
' Date of No Show
frm.TextBox13.Value = Format$(frm.TextBox13.Value, "ddd dd mmm yy")


frm.TextBox1.Value = frm.Rng(1, 7).Text
' Summary Of Conversation

frm.TextBox4.Value = frm.Rng(1, 13)
' Date of Contact
frm.TextBox4.Value = Format$(frm.TextBox4.Value, "ddd dd mmm yy")

frm.TextBox3.Value = frm.Rng(1, 14).Text
' Time of Contact
frm.TextBox3.Value = Format$(frm.TextBox3.Value, "HH:MM")

frm.TextBox7.Value = frm.Rng(1, 17)
' Date of 1st Additional Contact Attempt
frm.TextBox7.Value = Format$(frm.TextBox7.Value, "ddd dd mmm yy")

frm.TextBox6.Value = frm.Rng(1, 18).Text
' Time of 1st Additional Contact Attempt
frm.TextBox6.Value = Format$(frm.TextBox6.Value, "HH:MM")

frm.TextBox9.Value = frm.Rng(1, 19)
' Date of 2nd Additional Contact Attempt
frm.TextBox9.Value = Format$(frm.TextBox9.Value, "ddd dd mmm yy")

frm.TextBox8.Value = frm.Rng(1, 20).Text
' Time of 2nd Additional Contact Attempt
frm.TextBox8.Value = Format$(frm.TextBox8.Value, "HH:MM")

frm.TextBox11.Value = frm.Rng(1, 21)
' Date of 3rd Additional Contact Attempt
frm.TextBox11.Value = Format$(frm.TextBox11.Value, "ddd dd mmm yy")

frm.TextBox10.Value = frm.Rng(1, 22).Text
' Time of 3rd Additional Contact Attempt
frm.TextBox10.Value = Format$(frm.TextBox10.Value, "HH:MM")

frm.TextBox2.Value = frm.Rng(1, 11)
' How many rides Canceled

If frm.Rng(1, 11).Text < "" Then
frm.TextBox2.Visible = True
frm.Label7.Visible = True
frm.OptionButton10.Value = True
End If


End Sub
===============================


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=132119

Rick Rothstein
Referencing a UserForm
 
Use a With/End With statement block. For example, if this were your code...

If UserForm1.rng(1, 11).Text < "" Then
UserForm1.TextBox2.Visible = True
UserForm1.Label7.Visible = True
UserForm1.OptionButton10.Value = True
End If

You can change it to this...

With UserForm1
If .rng(1, 11).Text < "" Then
.TextBox2.Visible = True
.Label7.Visible = True
.OptionButton10.Value = True
End If
End With

Notice the dots in front of the properties that reference back to the object
of the With statement... those are required. Every statement within the
With/End With block that references a property or method of the object
specified in the With statement can have that object's name removed as shown
in my example.

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
I an trying to pair down my code. Below is and example of some code that I
run outside of UserForm1. In an effort to reduce my file size, is there any
way to eliminate all the "UserForm1" reference?


Sub TextBoxData()

UserForm1.TextBox12.Value = UserForm1.rng(1, 4) & " " &
UserForm1.rng(1, 3) ' First and Last Name

UserForm1.TextBox5.Value = Range("AB6") ' No Show Count

If UserForm1.TextBox5.Value = 2 Then
UserForm1.TextBox5.BackColor = &HFFFF&
End If

If UserForm1.TextBox5.Value = 3 Then
UserForm1.TextBox5.BackColor = &HFF&
End If

If UserForm1.TextBox5.Value 3 Then
UserForm1.TextBox5.BackColor = &H80000012
UserForm1.TextBox5.ForeColor = &HFFFFFF
End If

UserForm1.TextBox13.Value = UserForm1.rng(1, 6) ' Date of No Show
UserForm1.TextBox13.Value = Format$(UserForm1.TextBox13.Value, "ddd
dd mmm yy")


UserForm1.TextBox1.Value = UserForm1.rng(1, 7).Text ' Summary Of
Conversation

UserForm1.TextBox4.Value = UserForm1.rng(1, 13) ' Date of Contact
UserForm1.TextBox4.Value = Format$(UserForm1.TextBox4.Value, "ddd
dd mmm yy")

UserForm1.TextBox3.Value = UserForm1.rng(1, 14).Text ' Time of Contact
UserForm1.TextBox3.Value = Format$(UserForm1.TextBox3.Value,
"HH:MM")

UserForm1.TextBox7.Value = UserForm1.rng(1, 17) ' Date of 1st
Additional Contact Attempt
UserForm1.TextBox7.Value = Format$(UserForm1.TextBox7.Value, "ddd
dd mmm yy")

UserForm1.TextBox6.Value = UserForm1.rng(1, 18).Text ' Time of 1st
Additional Contact Attempt
UserForm1.TextBox6.Value = Format$(UserForm1.TextBox6.Value,
"HH:MM")

UserForm1.TextBox9.Value = UserForm1.rng(1, 19) ' Date of 2nd
Additional Contact Attempt
UserForm1.TextBox9.Value = Format$(UserForm1.TextBox9.Value, "ddd
dd mmm yy")

UserForm1.TextBox8.Value = UserForm1.rng(1, 20).Text ' Time of 2nd
Additional Contact Attempt
UserForm1.TextBox8.Value = Format$(UserForm1.TextBox8.Value,
"HH:MM")

UserForm1.TextBox11.Value = UserForm1.rng(1, 21) ' Date of 3rd
Additional Contact Attempt
UserForm1.TextBox11.Value = Format$(UserForm1.TextBox11.Value, "ddd
dd mmm yy")

UserForm1.TextBox10.Value = UserForm1.rng(1, 22).Text ' Time of 3rd
Additional Contact Attempt
UserForm1.TextBox10.Value = Format$(UserForm1.TextBox10.Value,
"HH:MM")

UserForm1.TextBox2.Value = UserForm1.rng(1, 11) ' How many rides
Canceled

If UserForm1.rng(1, 11).Text < "" Then
UserForm1.TextBox2.Visible = True
UserForm1.Label7.Visible = True
UserForm1.OptionButton10.Value = True
End If


End Sub


Jim Luedke
Referencing a UserForm
 
Patrick:

Another thing I've used--more Woodstock-generation than OOP, I'm
afraid--is a public global var.:

In module UserForm1:

Set g_UserForm = UserForm1
....
MySub

At top of module X:

Public g_UserForm as UserForm

Further down in module X:

Public Sub MySub
DoSomethingWith(g_UserForm)
End Sub

That way, you don't have to pass a param.

***


All times are GMT +1. The time now is 12:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com