14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
OnlinePCLearning
([Link]
VBA for Beginners: VBA
Userforms
([Link]
BuildyourFirstUserformDatabase
Contents[show]
OVERVIEW
WhatisaUserform?
WhyuseaUserform?
UserformProject
InsertaUserform
UserformProperties
Toolbox
AddingControls
Writingourcode
WhatisaUserform?
AuserformisadialogboxthatyoucreateandcustomizeintheVisualBasic
[Link]
largevarietyofcontrolstotheuserform.
[Link] Opentheresourcefile 1/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
Opentheresourcefile
OpentheresourcefilethatcamewiththiseBook
[Link]
enableyoutotesteachoftheseexamples.
Hereistheformthatwewillbeusingtocollectand
addourreceipts.
WhyuseaUserform?
[Link]
[Link]
[Link].
Userformsenableavarietyofpossibleusesthatotherwisewouldbedifficult
toachieveatasheetlevel.
CommonUserformUses
Dataentrycontrol
Selectingranges
Splashscreens
Filteredworksheetdata
Addmultiplepagestotheuserform
Menuforyourapplication
PopupCalendars
Navigationforallsheets
InharmonywiththespiritofOnlinePCLearning
([Link]
bydevelopingaproject.
UserformProject
[Link]
[Link]
[Link]
[Link] 2/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
littleprojecttogetstartedwithlearningtouseuserformsinVBA.
Theillustrationbelowshowstheinterfacesheetandtheuserformreadyfor
action.
([Link]
[Link])
Addingthreedynamicnamedranges
Beforewegetstartedweneedtoaddthreedynamicnamedrangestothe
workbook.
ChangethenameofSheet3inyourworkbooktoListsandincellsD4/H4/F4
addtheheadersthatyouseebelow.
[Link]
Note:Makesurethatthereisdataundertheheadersasournamed
[Link],an
[Link].
([Link]
[Link])
Thesethreedynamicnamedrangeswillbethevaluesthatpopulateour
[Link]
theribbononyourExcelspreadsheetchooseFormulas/Name
Manager/Newthenaddthenamebelowandtheformulatocreatethree
[Link]/Company/Location
Category=OFFSET(Lists!$D$5,,,COUNTA(Lists!$D$5:$D$100))
Company=OFFSET(Lists!$H$5,,,COUNTA(Lists!$H$5:$H$100))
Location=OFFSET(Lists!$F$5,,,COUNTA(Lists!$F$5:$F$100))
[Link] 3/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
Hereisthelinktothewebsitearticleforunderstandingdynamicnamed
ranges
Note:Youcanuseastaticnamedrangehoweveryouwouldneedto
redefineiteachtimenewdataisadded.
ExcelDynamicNamedRanges:AddPowerandPunchtoExcel(/excel
dynamicnamedranges/)
Note:Youcanuseastaticnamedrangehoweveryouwouldneedto
redefineiteachtimenewdataisadded.
VisualBasicEditor
[Link]
notreadthatchapterortheinformationisalittlehazythenpleaserevisitthis
article.
UnderstandingtheVBAEditor(/vbaforbeginnersunderstandingvba/)
InsertaUserform
Howtocreateauserform!
OpenMicrosoftExcelandusetheshortcutkeyAlt+F11toopentheVisual
BasicEditor.
[Link]
[Link]
[Link]
willenableyoutoresizetheformtoyourapproximateneeds.
[Link]
[Link]
ViewtabandclickToolbox.
[Link]
WewillneedtohavequickaccesstotheToolboxandthePropertiesdialog
box.
UserformProperties
Whenyouclickinsidetheuserformyoushouldseeadottedborderaroundit.
Whenthisoccursthepropertiesdialogboxwilldisplaytheuserform
properties.
[Link] 4/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
ForourprojectwewanttochangethenameoftheuserformfromUserform1
[Link]
[Link]
useanamingconventionthatisconsistent.
InthepropertiesboxnametheuserformfrmTax_Receipt(nospaces
allowed)andaddacaptionMyTaxReceipts(spacesareallowedhere)
Hereisasampleofthenamingconventionthatiscommonlyused.
Userformsstartwithfrm
Textboxesstartwithtxt
Comboboxesstartwithcbo
Labelsstartwithlbl(ifyouarenotreferringtothelabelsinyoucode
thereisnoneedtogivethemaspecialname)
TheonlyrulesthatapplyarenottouseareservedVBAkeywordnoradd
spacesinthename.
Note:Atthisstageoftheprojectyouruserformshouldnowhaveanameand
[Link]
[Link]
VBE,closetheuserformbyclickingthexinthetoprighthandcornerofthe
userform.
Notethedottedborderwhentheuserformisselectedandthenewnamefor
theuserformintheProjectExplorer.
Toolbox
TheToolboxcontainsallofourcontrolsthatcanbeaddedtotheuserform.
[Link]
needtousecontrolsthatarenotinthetoolboxthenrightclickinsideofthe
[Link]
[Link](x)inthe
[Link]
addadditionalcontrolsaddan(x)[Link]
control,clickinsidethesquaretoremovethexandthenclickOK.
Note:NotallofthesecontrolsareavailabletoVBAExcel.
[Link] 5/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
AddingControls
AddingLabels
Wehave12labelstoaddtotheuserform.
ToaddalabelclickonthelabelintheToolboxandthenreturntoyour
Userformandclickdownontheleftmousebuttonanddragthelabeloverthe
formuntilthecontrolisthedesiredsize.
[Link]
willnoticethatspacesareallowedbetweenthewordsinthelabelsandthat
youareabletochangethefontsizeandcolourandstyletosuityourtaste.
([Link]
content/uploads/2013/11/[Link])
Nowthatyouhaveyourlabel,[Link]
abletochangethelabelnamebutmostimportantlywewanttochangethe
[Link]
wantandthefonttypeandstyle.
Note:Inthepropertiesboxyouareabletoselectthewaythattheproperties
[Link]
choiceofAlphabeticorCategorizedtabsatthetopofthePropertiesdialog
boxtochoosefrom.
TopTip:Onceyouhaveformattedonelabelrightclickandcopyiteleven
[Link]
[Link]
bereferencedintheVBAproject.
[Link] 6/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
Aswellistheninetextlabelswehavethreelabelsthatwillindicatethefields
[Link]
[Link]
theothertwoandmovethemtothecorrectplaceontheuserform,asshown
intheillustrationbelow.
Listforcontrolnames
Asyoucanseefromtheillustrationbelowwehave:
12labels
4textboxes
3comboboxes
2optionbuttonsthataregrouped
3commandbuttons
1imageframe
[Link]
boxes,comboboxes,optionbuttonsandcommandbuttonshaveexactlythe
samenamesasyouseebelowortheVBAcodethatyouwillbeaddingwill
notwork.
[Link] 7/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
([Link]
content/uploads/2013/11/[Link])
AddingTextBoxControls
Toaddatextbox,clickonthetextboxiconintheToolboxandthenreturnto
yourUserformandclickdownontheleftmousebuttonanddragthetextbox
overtheform.
Theonlythingthatweneedtodotoourtextboxcontrolsistoaddthecorrect
nametothem.
Note:
Copythecontrolnamesfromtheillustrationabove.
AddingComboboxcontrols
Addthecomboboxcontrolstotheuserforminthesamemannerasthetext
[Link]
[Link]
topopulateeachcomboboxwithadynamicnamedrangethatyou
previouslyaddedintothenamemanager.
Theillustrationbelowshowswherethedynamicnamedrangeshouldbe
typedintheRowSourceofthepropertiesofthecombobox.
[Link]
namedrangethatasyetdoesnotexistiftherangesareempty.
[Link] 8/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
Youcansettheoptiontoallowausertoadddifferentvaluesfromthoseinthe
[Link]
choose2MatchEntryNone(illustrationbelow).Ifyouallowthisthen
analyzingthedataatalaterdatecanbealittledifficult.
([Link]
content/uploads/2013/11/[Link])
OptionButtons
Aswellasassigningthecorrectnamestoouroptionbuttonsweneedto
[Link]
draggingthecurseroverbothofthemorbyholdingdowntheshiftkeyand
[Link]
andfromthedropdownmenuchooseGroup.
OptionButtons
Aswellasassigningthecorrectnamestoouroptionbuttonsweneedto
[Link]
draggingthecurseroverbothofthemorbyholdingdowntheshiftkeyand
[Link]
andfromthedropdownmenuchooseGroup.
AddingaPicture
Ifyouwishtoaddapictureoralogotoyouruserformthenfollowthesetwo
steps.
[Link]
[Link]
[Link]
openingaMicrosoftPowerPointandinsertingclipartoryourimage
(logo)thatyoufeelwouldbeappropriatetothethemeofthis
[Link]
theimagewassavedtoalocationonthedesktopbyrightclickingand
[Link]
andopenedinMicrosoftPictureManagerwhereitwasresizedto7
kb.
[Link]
illustrationbelowwecanaddthepicturebyclickingPictureandthenlocating
[Link]:
[Link]
[Link] 9/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
[Link].
Thesepropertieswillallowyoutomanipulatetheimagewithinthecontrol.
([Link]
content/uploads/2013/11/[Link])
Taborder
Tosetthetaborderforthecontrolsinyouruserform,clickontheViewTab
andchooseTabOrder(illustrationbelow).Movethecontrolsupanddownto
suittheorderthatyouwouldliketheTabkeytomovethroughthem.
WritingtheVBACode
Thefirstthingthatwewanttodoistorunasmallpieceofcodetoopenthe
Userformfromabuttonontheworksheet.
[Link]
moduleaddthissmallpieceofVBAcode.
SubShowme()
frmTax_Receipts.Show
EndSub
[Link] 10/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
NamethethreeworksheetsInterface,[Link]
sheetinsertarectangleshapethenrightclickontheshapeandchoose
[Link].
Testtheuserform
[Link]
notmoveforwardwiththerestofthisprojectuntilthisissuccessfullyworking.
Checkthatthethreecomboboxeshavethevaluescontainedinthethree
dynamicnamedranges.
Addingourcode
[Link]
clickonthecmdAddbutton.
TheclickeventprocedurebelowwillappearintheVBE.
PrivateSubcmdAdd_Click()
(VBAcodegoeshere)
EndSub
OurVBAcodewhichwilltaketheinformationfromtheuserformandadditto
thedatabaselistwillappearbetweenthesetwolines.
Sixthingsthatthisprocedurewillaccomplish:
[Link]
2.Add2variables
3.Add2Ifstatementstocheckthatadateisaddedandthatour
mandatoryfieldshavebeenfilledin.
[Link].
[Link]
[Link]
Declareourvariables
ThiscodeisdimensioningourvariableswhichsimplymeanstellingVBAthe
datatypeforeachvariable.
DimwsAsWorksheet
DimaddmeAsRange
Addthevariables
Thesetwovariablesarefortheworksheetthatcontainsthedatabaseandfor
thenextemptyrowinourdatabaselist.
Setws=Sheet2
Setaddme=[Link]([Link],4).End(xlUp).Offset(1,0)
AddtwoIfstatementstocheckthatadateisadded
andthatourmandatoryfieldshavebeenfilledin.
AcommonmistakethatmanymakewithUserformsistoaddadate
[Link]
theusertoproceed.
[Link] 11/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
IfNotIsDate([Link])Then
MsgBox"Thedatefieldmustbeaproperdate",vbExclamation,
"Dateformaterror"
[Link]=""
[Link]
ExitSub
EndIf
OurUserformhasthreemandatorilyfieldsthatneedtobecheckedtoseeifa
[Link]
belowchecksofthesethreefields.
[Link]=""[Link]=""Or
[Link]=""Then
MsgBox"[Link]
(*)",vbExclamation,"Mandatoryfieldsareincomplete"
ExitSub
EndIf
MovetheinformationfromtheUserformtothe
database.
Wereferenceourvariableaddmetofindthenextavailablecelltoaddour
[Link]
thenoffsetonecolumninthesamerowandaddourcboCompanythen
offsetonecolumnandaddtxtDescriptionthenoffsetonecolumnandadd
[Link]
/ElseIf/Elsestatementtocheckandtosendtherightvaluetoourdatabase.
[Link]
[Link]
ascurrency.
Withws
'setthedateformattosuityourarea
[Link]=Format([Link],"mm/dd/yy")
[Link](0,1).Value=[Link]
[Link](0,2).Value=[Link]
[Link](0,3).Value=[Link]
[Link](0,4).Value=Format([Link],
"$##,###.00")
[Link]=TrueThen
[Link](0,5).Value="PaperCopy"
[Link] 12/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
[Link]=TrueThen
[Link](0,5).Value="ScannedCopy"
Else
[Link](0,5).Value=""
EndIf
[Link](0,6).Value=cboLocation
[Link](0,7).Value=txtRemarks
EndWith
Sortingthedatabase
Livingintherealworldweknowthatourdatesarenotalwaysgoingtobe
[Link]
[Link]
andinascendingorder.
[Link]
Withws
[Link]("D4:K10000").SortKey1:=Range("D4"),
Order1:=xlAscending,Header:=xlGuess
EndWith
'returntosheet
[Link]
ResettheUserform
TherearethreewaystoresettheUserform.
[Link]
[Link]=
[Link]
value
[Link]
IfTypeName(ctl)="TextBox"[Link]=""
Nextctl
[Link]
UnloadMe
frmTax_Receipts.Show
Wearegoingtochoosethefinaloptionbecauseitistheeasiest.
BelowisthecompletecmdAddprocedure
[Link]
[Link]
throughthecodeagainandmakesurethatyouunderstandexactlywhat
eachpartofthecodeisdoing.
PrivateSubcmdAdd_Click()
[Link] 13/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
'dimentionthevariable
DimwsAsWorksheet
DimaddmeAsRange
'setthevariable
Setws=Sheet2
'setvariableforthedestination
Setaddme=[Link]([Link],4).End(xlUp).Offset(1,0)
'holdinmemory
[Link]=False
IfNotIsDate([Link])Then
MsgBox"Thedatefieldmustbeaproperdate",vbExclamation,
"Dateformaterror"
[Link]=""
[Link]
ExitSub
EndIf
[Link]=""[Link]=""Or
[Link]=""Then
MsgBox"[Link]
(*)",vbExclamation,"Mandatoryfieldsareincomplete"
ExitSub
EndIf
'sendthevaluestothedatabase
Withws
'setthedateformattosuityourarea
[Link]=Format([Link],"mm/dd/yy")
[Link](0,1).Value=[Link]
[Link](0,2).Value=[Link]
[Link](0,3).Value=[Link]
[Link](0,4).Value=Format([Link],
"$##,###.00")
[Link]=TrueThen
[Link](0,5).Value="PaperCopy"
[Link]=TrueThen
[Link](0,5).Value="ScannedCopy"
Else
[Link] 14/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
[Link](0,5).Value=""
EndIf
[Link](0,6).Value=cboLocation
[Link](0,7).Value=txtRemarks
EndWith
'sortthedata
[Link]
Withws
[Link]("D4:K10000").SortKey1:=Range("D4"),
Order1:=xlAscending,Header:=xlGuess
EndWith
'returntosheet
[Link]
'resettheform
UnloadMe
frmTax_Receipts.Show
'updatethesheet
[Link]=True
EndSub
ClosetheUserform
DoubleclickonthecmdClosebuttonandaddtheVBAcodebetweenthe
twolines.
UnloadMe
PrivateSubcmdClose_Click()
UnloadMe
EndSub
ResettheUserform
PrivateSubcmdReset_Click()
UnloadMe
frmTax_Receipts.Show
EndSub
SetuptheDatabase
Onthedatabasesheetaddtheseheaderstothecellreferencesshown
below.(Refertoillustrationbelow)
CellD4ReceiptDate
[Link] 15/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
CellE4Company
CellF4Description
CellG4TaxCategory
CellH4Amount
CellI4Type
CellJ4Location
CellK4Remarks
([Link]
[Link])
Testtheuserformwithdata
Returntotheinterfacesheetandclickonthebuttonthatyouaddedto
[Link]
UserformandwhenyouclicktheAddbuttonthosevaluesshouldbemoved
[Link]
[Link].
WellthatsitforourUserformpartoftheproject.
Iamnowgoingtoshowyouhowyoucanfilterthatdatabasetobeableto
[Link]
[Link]
nowwewillrunthisatasheetlevel.
SettingupTheInterface
Filteredresults(CopyTorange)
Ontheinterfacesheetaddexactlythesameheadersthatyouaddedtothe
databasesheetbutthistimestartatcellreferenceC7.
Note:Theseheadersmusthaveexactlythesamewordsthatyouusedinthe
[Link].
[Link]
database.
([Link]
[Link])
Startandfinishdates
[Link] 16/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
Directlyabovetheseheadersaddthesevaluesandformatstothecells
[Link].
CellC5StartDate
CellD5FinishDate
CellC6Formatthiscellasadatetype
CellD6Formatthiscellasadatetype
Advancedfiltercriteriablock
Totherightoftheseheadersaddthevaluesbelowtothecellreferencesand
[Link]
asthiswillformourcriteriablockfortheadvancedfilter.
CellM5ReceiptDate
CellN5ReceiptDate
CellM6=IF(C6="","",">"&C6)
CellN6=IF(D6="","","<"&D6)
Thesetwoformulaswillpickupyourstartandfinishdateandaddgreater
than(>)oraless(<)[Link]
thecellswillbeblank.
Runningtheadvancedfilter
ReturntotheVisualBasiceditoranddoubleclickonthemoduleinthe
ProjectExplorerandinserttheVBAcodethatyouseebelow.
SubFilterme()
[Link]("D4").[Link]
Action:=xlFilterCopy,_
CriteriaRange:=Range("M5:N6"),CopyToRange:=Range("C7:J7"),
Unique:=False
EndSub
Gobacktotheinterfacesheetandinsertashape,rightclickontheshape
andchooseAssignMacroandselectthemacroFilterMe.
Afteryouhaveaddedmultiplevaluestothedatabase,runsometestswiththe
advancedfilterbyaddingstartandfinishingdates.
InConclusion
[Link] 17/18
14/3/2017 VBAforBeginners:VBAUserformsOnlinePCLearning
InthisprojectwehavelearnedmanythingsabouthowaUserformworksand
[Link]
[Link]
toenhancetheuserexperience,controldatamovementanddataentryin
yourapplications.
Related
([Link]
([Link]
([Link]
pageuserform trainingmanager userformtoexport
employeedatabase/) exceldatabase/) andimportfrom
MultiPage StaffTraining access/)
Userform ManagerDatabase ExcelUserformto
Employee ExcelUserform ExportandImport
Database fromAccess
([Link]
([Link]
trainingmanager ([Link]
pageuserform exceldatabase/) userformtoexport
employee February4,2014 andimportfrom
database/) In"ExcelProjects access/)
August26,2015 UserformVBA April15,2015
In"ExcelProjects Training" In"ExcelProjects
UserformVBA UserformVBA
Training" Training"
2commentsonVBAforBeginners:VBA
Userforms
florenciadepelflorencia([Link]
December11,2013at4:37pm([Link]
beginnersvbauserforms/#comment531)
Setws=Sheets(2)
'error13,helpme
TrevorEaston([Link]
December11,2013at4:44pm
([Link]
userforms/#comment532)
[Link] 18/18
