Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a UserForm in a Module | Excel Programming | |||
Referencing Userform Controls | Excel Programming | |||
Referencing UserForm event in module | Excel Programming | |||
referencing a different tab by referencing a list in the current s | Excel Worksheet Functions | |||
Looping procedure calls userform; how to exit loop (via userform button)? | Excel Programming |