Referencing a UserForm

  • Thread starter Patrick C. Simonds
  • Start date
P

Patrick C. Simonds

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
 
L

Leith Ross

Patrick said:
I an trying to pair down my code. Below is and example of some code tha
I
run outside of UserForm1. In an effort to reduce my file size, is ther
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
Click to expand...

Hello Patrick,

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

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

frm.TextBox12.Value = frm.Rng(1, 4) & " " & frm.Rng(1, 3) ' First an
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/)
 
R

Rick Rothstein

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.
 
J

Jim Luedke

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.

***
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Textbox input goes to 2 sheets in same wbk 0
How to reference a range from a UserForm 5
Referencing a UserForm in a Module 2
Setting a list box to a value 6
statement invalid outside type block 4
fill userform textbox from userform listbox clik event 3
Another Userform Date Problem 3
Issues with Looping macro...Do Until TextBox7.Value = 0 but it's crashing...Why? 3

Top