$show=home

VBA - User Forms

VBA - User Forms - Shout4Education
User Form is a custom-built dialog box that makes a user data entry more controllable and easier to use for the user. In this chapter, you will learn to design a simple form and add data into excel.
Step 1 − Navigate to VBA Window by pressing Alt+F11 and Navigate to "Insert" Menu and select "User Form". Upon selecting, the user form is displayed as shown in the following screenshot.
The user form is displayed as shown in the following screenshot Shout4Education
Step 2 − Design the forms using the given controls.
Design the forms using the given controls Shout4Education
Step 3 − After adding each control, the controls have to be named. Caption corresponds to what appears on the form and name corresponds to the logical name that will be appearing when you write VBA code for that element.
Caption corresponds to what appears on the form and name corresponds to the logical name Shout4Education

Step 4 − Following are the names against each one of the added controls.
ControlLogical NameCaption
FromfrmempformEmployee Form
Employee ID Label BoxempidEmployee ID
firstname Label BoxfirstnameFirst Name
lastname Label BoxlastnameLast Name
dob Label BoxdobDate of Birth
mailid Label BoxmailidEmail ID
Passportholder Label BoxPassportholderPassport Holder
Emp ID Text BoxtxtempidNOT Applicable
First Name Text BoxtxtfirstnameNOT Applicable
Last Name Text BoxtxtlastnameNOT Applicable
Email ID Text BoxtxtemailidNOT Applicable
Date Combo BoxcmbdateNOT Applicable
Month Combo BoxcmbmonthNOT Applicable
Year Combo BoxcmbyearNOT Applicable
Yes Radio ButtonradioyesYes
No Radio ButtonradionoNo
Submit ButtonbtnsubmitSubmit
Cancel ButtonbtncancelCancel
Step 5 − Add the code for the form load event by performing a right-click on the form and selecting 'View Code'.
Add the code for the form load event by performing a right-click on the form and selecting View Code Shout4Education

Step 6 − Select ‘Userform’ from the objects drop-down and select 'Initialize' method as shown in the following screenshot.
Select ‘Userform’ from the objects drop-down and select Initialize method as shown in the following screenshot Shout4Education

Step 7 − Upon Loading the form, ensure that the text boxes are cleared, drop-down boxes are filled and Radio buttons are reset.
Private Sub UserForm_Initialize()
   'Empty Emp ID Text box and Set the Cursor 
   txtempid.Value = ""
   txtempid.SetFocus
   
   'Empty all other text box fields
   txtfirstname.Value = ""
   txtlastname.Value = ""
   txtemailid.Value = ""
   
   'Clear All Date of Birth Related Fields
   cmbdate.Clear
   cmbmonth.Clear
   cmbyear.Clear
   
   'Fill Date Drop Down box - Takes 1 to 31
   With cmbdate
      .AddItem "1"
      .AddItem "2"
      .AddItem "3"
      .AddItem "4"
      .AddItem "5"
      .AddItem "6"
      .AddItem "7"
      .AddItem "8"
      .AddItem "9"
      .AddItem "10"
      .AddItem "11"
      .AddItem "12"
      .AddItem "13"
      .AddItem "14"
      .AddItem "15"
      .AddItem "16"
      .AddItem "17"
      .AddItem "18"
      .AddItem "19"
      .AddItem "20"
      .AddItem "21"
      .AddItem "22"
      .AddItem "23"
      .AddItem "24"
      .AddItem "25"
      .AddItem "26"
      .AddItem "27"
      .AddItem "28"
      .AddItem "29"
      .AddItem "30"
      .AddItem "31"
   End With
   
   'Fill Month Drop Down box - Takes Jan to Dec
   With cmbmonth
      .AddItem "JAN"
      .AddItem "FEB"
      .AddItem "MAR"
      .AddItem "APR"
      .AddItem "MAY"
      .AddItem "JUN"
      .AddItem "JUL"
      .AddItem "AUG"
      .AddItem "SEP"
      .AddItem "OCT"
      .AddItem "NOV"
      .AddItem "DEC"
   End With
   
   'Fill Year Drop Down box - Takes 1980 to 2014
   With cmbyear
      .AddItem "1980"
      .AddItem "1981"
      .AddItem "1982"
      .AddItem "1983"
      .AddItem "1984"
      .AddItem "1985"
      .AddItem "1986"
      .AddItem "1987"
      .AddItem "1988"
      .AddItem "1989"
      .AddItem "1990"
      .AddItem "1991"
      .AddItem "1992"
      .AddItem "1993"
      .AddItem "1994"
      .AddItem "1995"
      .AddItem "1996"
      .AddItem "1997"
      .AddItem "1998"
      .AddItem "1999"
      .AddItem "2000"
      .AddItem "2001"
      .AddItem "2002"
      .AddItem "2003"
      .AddItem "2004"
      .AddItem "2005"
      .AddItem "2006"
      .AddItem "2007"
      .AddItem "2008"
      .AddItem "2009"
      .AddItem "2010"
      .AddItem "2011"
      .AddItem "2012"
      .AddItem "2013"
      .AddItem "2014"
   End With
   
   'Reset Radio Button. Set it to False when form loads.
   radioyes.Value = False
   radiono.Value = False

End Sub
Step 8 − Now add the code to the Submit button. Upon clicking the submit button, the user should be able to add the values into the worksheet.
Private Sub btnsubmit_Click()
   Dim emptyRow As Long
  
   'Make Sheet1 active
   Sheet1.Activate
  
   'Determine emptyRow
   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  
   'Transfer information
   Cells(emptyRow, 1).Value = txtempid.Value
   Cells(emptyRow, 2).Value = txtfirstname.Value
   Cells(emptyRow, 3).Value = txtlastname.Value
   Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
   Cells(emptyRow, 5).Value = txtemailid.Value
  
   If radioyes.Value = True Then
      Cells(emptyRow, 6).Value = "Yes"
   Else
      Cells(emptyRow, 6).Value = "No"
   End If
End Sub
Step 9 − Add a method to close the form when the user clicks the Cancel button.
Private Sub btncancel_Click()
   Unload Me
End Sub
Step 10 − Execute the form by clicking the "Run" button. Enter the values into the form and click the 'Submit' button. Automatically the values will flow into the worksheet as shown in the following screenshot.
Enter the values into the form and click the Submit button Shout4Education


Comments

Blogger
Name

.NET_Interview,1,Accenture_GFT,1,Accenture_Prep,1,Advance_Excel,22,Advance_Python,10,Advanced_Linux,6,Advanced_SQL,18,Advanced_Unix,6,AMCAT,1,AMCAT_Prep,1,AMCAT_Solved_Papers,1,Android,1,Apache_Sqoop,10,Aptitude,1,AWS,2,AWS_Dumps,1,AWS_Interview,1,B_Tech,19,Banking_Exam,1,Basic_Linux,29,Basic_Python,19,Basic_SQL,24,Basic_Unix,30,Best_Websites,1,Big_Data_Analytics,70,Blog,274,Blogger,3,Blogging,2,Blogspot,1,Books,1,BTech,20,C++_Interview,1,CBSE,30,Civil_1st_Semester,1,Class_12,28,Class_12_Chemistry,4,Class_12_Computer_Science,7,Class_12_Mathematics,1,Class_12_NCERT,15,Class_12_NCERT_Solutions,15,Class_12_Physics,18,Class_12_Physics_NCERT_Solutions,15,Class_12_Science,29,Cloud_Service,1,Coding,1,Communication,2,Computer,18,Computer_Memory,2,Computer_Programming,2,Computer_Science,4,Control_System,9,Crack_Interview,3,CSE_5th_Semester,1,CSS,1,Data_Analytics,16,Data_Science,18,Data_Science_Interview,1,Database,47,Database_Interview,4,Dumps,2,ECE_1st_Semester,1,ECE_1st_Year,1,ECE_4th_Semester,9,Electrical,2,Electrical_1st_Semester,1,Electronics,1,Electronics_&_Communication,14,English,2,Ethical_Hacking,1,ETL_Tools,17,Exam_Cracker,3,Exams,5,Exams_Banking,1,Exams_Prep,1,Excel,22,Excel_Macros,22,Excel_Terms,1,Excel_VBA,22,Free_OS,1,Free_Softwares,1,Games,3,GATE,19,GATE_2019,16,GATE_2020,16,GATE_2021,7,GATE_EC,10,GATE_ECE,9,GATE_ECE_Best_Book,1,GATE_Electrical,1,GATE_Electronics,12,GATE_Made_Easy,1,GATE_ME,6,GATE_Mechanical,6,GGSIPU,1,Handwritten_Notes,10,Hardware,6,HCL_Prep,1,How_To,8,HR_Interview,3,Hyderabad_News,1,IBPS,1,IBPS_English,1,IBPS_PO,2,Informatica,1,Informatica_Interview,1,Information,20,Internet,5,Interview,20,Interview_Prep,20,IPU,1,Java,1,Java_Interview,2,Java_Questions_&_answers,1,JEE,6,JEE_Mains,6,Jobs,1,Keyboard,1,Keyboard_Shortcuts,1,Learn_VBA,22,Linux,33,Linux_Distributions,1,Linux_Interview,1,Linux_Redirections,1,Linux_Scripting,30,Linux_Shell_Arrays,1,Linux_Shell_Functions,1,Linux_Shell_Quote,1,Linux_Signals_And_Traps,1,Logical_Reasoning,1,Machine_Learning,1,Machine_Learning_Interview,1,Macros,22,Manufacturing_Processes,1,ME_1st_Semester,1,ME_Fluid_Mechanics,1,ME_Industrial_Engineering,1,ME_Machine_Design,1,Mechanical,6,Memory,1,Microsoft,1,Microsoft_Azure,1,Microsoft_Azure_Interview,1,Mobile,1,MS_Access,40,MySQL,44,Network,2,News,3,Notes,31,Open_Source_OS,1,Operating_Systems,2,Operating_Systems_Interview,1,Oracle,42,Oracle_Interview,1,Physics,2,PL_SQL,42,PL_SQL_Interview,1,Placement,21,Placement_Prep,24,Poetry,1,Programming,28,Programming_Languages,1,Python,28,Python_Built_In_Strings_Methods,2,Python_built_In_Tuple_Functions,1,Python_CAlling_a_Function,1,Python_CGI,1,Python_Class,1,Python_Data_Types,1,Python_DAte,1,Python_Decision_Making,1,Python_Dictionary,1,Python_DOM_APIs,1,Python_Features,1,Python_Files_Functions,1,Python_For_Loop,1,Python_Functions,6,Python_GUI,1,Python_History,1,Python_If_Else,1,Python_import_Statements,1,Python_Installation,1,Python_Interview,1,Python_JPython,1,Python_Lists,2,Python_Loops,1,Python_Methods,1,Python_Modules,1,Python_MySQL,1,Python_Nested_If_Else,1,Python_Nested_Loops,1,Python_Number_Type_Conversion,1,Python_Numbers,2,Python_Object_Oriented,1,Python_OOP,1,Python_Pass_By_Reference_vs_Value,1,Python_Programming,28,Python_Scripting,28,Python_Special_Operators,1,Python_Strings,2,Python_Strings_Functions,1,Python_Threading_Module,1,Python_Time,1,Python_Tkinter,1,Python_Tuples,2,Python_Tutorial,28,Python_Types_of_Loops,1,Python_Variables,1,Python_Web_Server,1,Python_While_Loop,1,Python_wxPython,1,Python_XML_Processing,1,PythonPath_Setup,1,Quantitative_Aptitude,2,RDBMS,1,Scripting,52,Security,1,Shell_Command_Manual,1,Shell_Logging_Commands,1,Shell_Scripting,31,Shell_Scripting_Interview,1,Software_Engineering_Interview,1,Solutions,1,SQL,45,SQL_Alias_Syntax,1,SQL_Alter_Table_Query,1,SQL_Alter_Table_Statement,1,SQL_AND_OR_Query,1,SQL_AND_OR_Statement,1,SQL_Architecture,1,SQL_Clone_Table,1,SQL_Commands,1,SQL_Conjunctive_Operators,1,SQL_Constraints,1,SQL_Create_Database,1,SQL_Create_Table,1,SQL_DataTypes,1,SQL_Date_Functions,1,SQL_Date_Statement,1,SQL_DCL,1,SQL_Server,39,SQL_Temporary_Table_Statement,1,SQLite,43,Sqoop,9,Sqoop_Tutorial,10,SSC,3,SSC_CGL,3,SSC_CGL_English,1,SSC_CHSL,1,SSC_CPO,1,SSC_GS,1,SSC_Quantative,1,Talend,17,Talend_ETL,15,Talend_Tutorials,16,TCS_Interview,1,TCS_Prep,1,Teradata_Interview,1,Tips_&_Tricks,18,Top_10,1,Top_50,18,Tutorials,151,Tutorials_Python,28,Tutorials_VBA,11,Unix,32,Unix_Interview,1,Unix_Scripting,31,VBA,22,VBA_Basics,22,VBA_Excel,22,VBA_Scripting,22,VBA_Tutorials,22,Visual_Basic_Application,22,Websites,1,Windows,14,
ltr
item
Shout4Education - Get Jobs, Tutorials and Notes: VBA - User Forms
VBA - User Forms
VBA - User Forms - Shout4Education - VBA Tutorials ... Best VBA Tutorials only @ Shout4Education aka Shout for Education. ... Learn VBA in an Easy and Simplified Way ... Keep Shouting For Education and Keep Learning ... VBA - User Forms - Shout4Education A User Form is a custom-built dialog box that makes a user data entry more controllable and easier to use for the user. In this chapter, you will learn to design a simple form and add data into excel. Step 1 − Navigate to VBA Window by pressing Alt+F11 and Navigate to "Insert" Menu and select "User Form". Upon selecting, the user form is displayed as shown in the following screenshot. Step 1 − Navigate to VBA Window by pressing Alt+F11 and Navigate to "Insert" Menu and select "User Form". Upon selecting, the user form is displayed as shown in the following screenshot Shout For Education Step 2 − Design the forms using the given controls. Step 2 − Design the forms using the given controls Shout4Education Step 3 − After adding each control, the controls have to be named. Caption corresponds to what appears on the form and name corresponds to the logical name that will be appearing when you write VBA code for that element. Step 3 − After adding each control, the controls have to be named. Caption corresponds to what appears on the form and name corresponds to the logical name that will be appearing when you write VBA code for that element Shout4Education Step 4 − Following are the names against each one of the added controls. Control Logical Name Caption From frmempform Employee Form Employee ID Label Box empid Employee ID firstname Label Box firstname First Name lastname Label Box lastname Last Name dob Label Box dob Date of Birth mailid Label Box mailid Email ID Passportholder Label Box Passportholder Passport Holder Emp ID Text Box txtempid NOT Applicable First Name Text Box txtfirstname NOT Applicable Last Name Text Box txtlastname NOT Applicable Email ID Text Box txtemailid NOT Applicable Date Combo Box cmbdate NOT Applicable Month Combo Box cmbmonth NOT Applicable Year Combo Box cmbyear NOT Applicable Yes Radio Button radioyes Yes No Radio Button radiono No Submit Button btnsubmit Submit Cancel Button btncancel Cancel Step 5 − Add the code for the form load event by performing a right-click on the form and selecting 'View Code'. Step 5 − Add the code for the form load event by performing a right-click on the form and selecting 'View Code' Shout 4 Education Step 6 − Select ‘Userform’ from the objects drop-down and select 'Initialize' method as shown in the following screenshot. Step 6 − Select ‘Userform’ from the objects drop-down and select 'Initialize' method as shown in the following screenshot Shout 4 Education Step 7 − Upon Loading the form, ensure that the text boxes are cleared, drop-down boxes are filled and Radio buttons are reset. Private Sub UserForm_Initialize() 'Empty Emp ID Text box and Set the Cursor txtempid.Value = "" txtempid.SetFocus 'Empty all other text box fields txtfirstname.Value = "" txtlastname.Value = "" txtemailid.Value = "" 'Clear All Date of Birth Related Fields cmbdate.Clear cmbmonth.Clear cmbyear.Clear 'Fill Date Drop Down box - Takes 1 to 31 With cmbdate .AddItem "1" .AddItem "2" .AddItem "3" .AddItem "4" .AddItem "5" .AddItem "6" .AddItem "7" .AddItem "8" .AddItem "9" .AddItem "10" .AddItem "11" .AddItem "12" .AddItem "13" .AddItem "14" .AddItem "15" .AddItem "16" .AddItem "17" .AddItem "18" .AddItem "19" .AddItem "20" .AddItem "21" .AddItem "22" .AddItem "23" .AddItem "24" .AddItem "25" .AddItem "26" .AddItem "27" .AddItem "28" .AddItem "29" .AddItem "30" .AddItem "31" End With 'Fill Month Drop Down box - Takes Jan to Dec With cmbmonth .AddItem "JAN" .AddItem "FEB" .AddItem "MAR" .AddItem "APR" .AddItem "MAY" .AddItem "JUN" .AddItem "JUL" .AddItem "AUG" .AddItem "SEP" .AddItem "OCT" .AddItem "NOV" .AddItem "DEC" End With 'Fill Year Drop Down box - Takes 1980 to 2014 With cmbyear .AddItem "1980" .AddItem "1981" .AddItem "1982" .AddItem "1983" .AddItem "1984" .AddItem "1985" .AddItem "1986" .AddItem "1987" .AddItem "1988" .AddItem "1989" .AddItem "1990" .AddItem "1991" .AddItem "1992" .AddItem "1993" .AddItem "1994" .AddItem "1995" .AddItem "1996" .AddItem "1997" .AddItem "1998" .AddItem "1999" .AddItem "2000" .AddItem "2001" .AddItem "2002" .AddItem "2003" .AddItem "2004" .AddItem "2005" .AddItem "2006" .AddItem "2007" .AddItem "2008" .AddItem "2009" .AddItem "2010" .AddItem "2011" .AddItem "2012" .AddItem "2013" .AddItem "2014" End With 'Reset Radio Button. Set it to False when form loads. radioyes.Value = False radiono.Value = False End Sub Step 8 − Now add the code to the Submit button. Upon clicking the submit button, the user should be able to add the values into the worksheet. Private Sub btnsubmit_Click() Dim emptyRow As Long 'Make Sheet1 active Sheet1.Activate 'Determine emptyRow emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 'Transfer information Cells(emptyRow, 1).Value = txtempid.Value Cells(emptyRow, 2).Value = txtfirstname.Value Cells(emptyRow, 3).Value = txtlastname.Value Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value Cells(emptyRow, 5).Value = txtemailid.Value If radioyes.Value = True Then Cells(emptyRow, 6).Value = "Yes" Else Cells(emptyRow, 6).Value = "No" End If End Sub Step 9 − Add a method to close the form when the user clicks the Cancel button. Private Sub btncancel_Click() Unload Me End Sub Step 10 − Execute the form by clicking the "Run" button. Enter the values into the form and click the 'Submit' button. Automatically the values will flow into the worksheet as shown in the following screenshot. Step 10 − Execute the form by clicking the "Run" button. Enter the values into the form and click the 'Submit' button. Automatically the values will flow into the worksheet as shown in the following screenshot Shout4Education @ Shout 4 Education , @ Shout For Education
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s640/VBA_Tutorials_Shout4Education.jpg
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s72-c/VBA_Tutorials_Shout4Education.jpg
Shout4Education - Get Jobs, Tutorials and Notes
https://www.shout4education.com/2020/04/vba-user-forms.html
https://www.shout4education.com/
https://www.shout4education.com/
https://www.shout4education.com/2020/04/vba-user-forms.html
true
7947974353386595563
UTF-8
Loaded All Posts Not Found Any Posts :( View All Read More Reply Cancel Reply Delete By Home Pages Posts View All Similar Posts Label Archive Search All Posts Not Found Any Post Match with Your Request Sorry !! Search Something Blazing :) Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Just Now 1 Minute Ago $$1$$ minutes ago 1 Hour Ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago More than 5 Weeks Ago Followers Follow :) This Premium Content is LOCKED !!! STEP 1: Share. STEP 2: Click the Link You Shared to Unlock Copy All Code Select All Code All Codes were Copied to Your Clipboard :) Can NOT Copy the Codes / Texts, Please Press [CTRL]+[C] (or CMD+C with Mac) to Copy