table of contentsindex-of.co.uk/ofimatica/mastering excel user forms by...mastering excel user forms...
Post on 20-Jul-2020
6 Views
Preview:
TRANSCRIPT
TableofContents
TitlePage
Acknowledgments
Copyright
UsingForms
ExcelModelDataStructure
FormObjects
OtherLessons
MasteringExcelUserFormsMarkMoore
AcknowledgmentsIwouldliketothankSharonDeitchforherinvaluablehelpintesting,proofreadingandmakingthisinformationasclearaspossible.Itendtoskipstepshereandthere.Sharonkeepsmeinline.
Copyright©2015byMarkMoore.Allrightsreservedworldwide.Nopartofthispublicationmaybereplicated,redistributed,orgivenawayinanyformwithoutthepriorwrittenconsentoftheauthor/publisherorthetermsrelayedtoyouherein.
UserInterfaceWhyshouldyouuseaform?Becauseitgivesyoumorecontroloverdatacollection.ByusingExcel’sbuilt-informobjects,youcanlimitwhattheusercaninput.Formsalsogiveyouanalternatewaytodisplayoptionstheusercanmake.YourExcelmodelcanthenreacttothechoicesmadebytheuser.Withoutforms,userinputsarelimitedtotypinginnumbersortextinacell.Usingformsgivestheuseraricherexperience.
Youcandownloadthefollowalongworkbookatmywebsite:http://markmoorebooks.com/user-forms/
Youwillneedtosignupandtheclickonthelinkintheconfirmationemail.Thenyouwillreceiveanemailwiththeworkbooksattached.EmailsmightgetcaughtinyourSpamfoldersomakesureyoucheckitifyoudon’tgettheemailwithinafewminutes.
AddingFormsAlltheformobjectsarelocatedontheDevelopertab.
IfyoudonothavetheDevelopertabvisible,dothefollowing(inExcel2013):
1–OntheFiletab,clickOptions.
3–IntheExcelOptionsdialogbox,clickonCustomizeRibbon.
4-Inthewindowontheright,selectDeveloper.
ToshowtheDevelopertabinExcel2007:
1. ClicktheOfficebutton,andthenclickExcelOptions.2. ClickPopularatthetopoftheleftwindow,andthenclickShowDeveloperTabin
the
Ribbon.
3.ClickInsert,andyouwillseethisboxappearintheRibbon:
FormControlsvs.ActiveXControls
You’llnoticethatthedrop-downboxhastwosections,FormControlsandActiveXcontrols.
FormControlsarebuiltintoExcel.TheyarebackwardscompatibletoExcel5.Youcanuseformcontrolswithorwithoutmacros.TheyaresimpletouseandcanmeetmostofregularExcelusers’needs.
ActiveXControlsarebasedontheActiveXtechnology(developedbyMicrosoft).ThesecontrolsareloadedbyExcelandhavemuchmoreflexibilitythantheFormControls(youcancontroljustaboutanythingonanActiveXControl,fontstyle,fontcolor,etc.).However,ActiveXControlshaveafewdrawbacks.SomeActiveXControlscannotbeuseddirectlyonaworksheetbutratherneedacustomform(wewillcoverthislater).ManycomputerswillnotallowActiveXtorunnatively,soyouwillhavetoaddthefiletotheTrustedSites(eventhentheITdepartmentmightnotallowit).Lastly,theycanbeunstableandcrashunexpectedly.
Therefore,fortherestofthelesson,youwillbeusingFormControls.
UsingFormsToreallyunderstandhowtouseforms,youaregoingtousethesampledatainthefollowalongworkbooktobuildseveralmini-dashboards.Eachdashboardusesoneformcomponent,soyoucangetexperiencewitheachobject.IbuilddashboardsaparticularwaythatleveragesExcel’sabilitytoextractdatafromadatabase.IexplainedthistopicindetailintheMasteringExcel:PivotTableslesson.Ifyouhavenotreadthatlessonorit’sbeenawhilesinceyoureadit,here’saquickreview.
ExcelModelDataStructureToleverageyourtimeandmakeyourExcelmodelasflexibleaspossible,youshouldthinkofbuildingyourmodelwiththreedistinctlayers.
DataLayer
Thisisthefoundationofyourreports.ThisisanextractfromGreatPlains,Oracle,SAP,orwhateversystemyouuse.Eachfieldneedstohaveatitleandmustcontainsimilardata.Forexample,ifthecolumnhasatitleofName,theentirecolumnneedstohavenamesinit.Don’tsticksomeone’semployeeIDinthere.AddanothercolumnforEmployeeID.Itmakesnosenseto‘save’acolumn.Exceldoesn’tcareandyouhave16,000columns,useasmanyasyouneed.
Thedatawillendatthefirstcompletelyemptyrowandthefirstcompletelyemptycolumn.Donotaddemptyrowstomakethedatamorereadable.Dataisnotsupposedtobereadable.Itissimplyusedtostorerandomfacts.Yourreportsarewhatneedstobereadable.
Hereisascreenshotofasampledatalayer:
ReportLayer
Thisisthelayerthateveryonesees.Ithasthecharts,PivotTable,reports,prettycolors,logos,etc.Youmakethisoneshineandlookgreat.Thisisthelayerthatgetsprintedanddisplayedinpresentations.
BusinessLogic
ThisiswhattiestogethertheDatalayerandtheReportlayer.Thesearetheformulas,
charts,andothercalculationsthatextractdatafromthedatalayerandconvertitintoinformation.
Thelayersarewhatiscalled‘looselycoupled’.Thismeansthatalthoughtheycommunicatewitheachother,youcanchangeonelayerwithoutaffectingtheother.Forexample,youwon’thavetorebuildyourentiremodelwhenanewfieldisaddedtothedatalayer.
Howdoformsfitin?FormsliveintheReportlayer.Theyallowyouruserstohavesomedecision-makingcapabilitiestochangetheinformation.Forexample,ifyoubuiltareportthatshowedJanuaryresultsthatwouldonlyworkforapersonthatwantedtoseeJanuarydata.YouwouldhavetobuildanotherreporttoshowFebruarydata.Attheendoftheyearyouwouldendupwith12reports.Instead,Isuggestyoubuildareportthatallowstheuserstochoosewhichmonththeywanttosee.Onereportnowcanbesentto12people,eachofwhomwantstoseeadifferentmonth.Withalittlebitofadvanceplanning,youhaveincreasedtheefficiencyofthisreport12timesover.Makesense?
ButtonThebuttonisthesimplestofformobjectstouse.Theprincipalfunctionofthebuttoncontrolistorunamacrobyclickingthebutton.
Eventhoughthislessondoesnotgointomacros,youcancreateabuttoncontroltoseehowitworks.
Youcandownloadthefollowalongworkbookatmywebsite:http://markmoorebooks.com/user-forms/
1-OpentheForms.xlsxfollowalongworkbook.
2-SelecttheButtonworksheet
3–OntheDevelopertab,clickInsert,andthenclickthebuttoniconunderFormControls.
Yourmouseiconwillchangetoasmallcross-hair.
6-Clickanddragthecross-hairtocreateabutton
Whenyoureleasethemouse,theAssignmacrodialogboxappears.
Ifyouhadamacrorecordedinthisworkbook,youcouldassignittothisbutton.Thenallyouneedtodotorunthemacroisclickthebutton.
7-Eventhoughyoudon’thaveamacro,clickOK
Thisishowmybuttonlooks:
Noticehowthebuttonhasthesmallsquaresaroundtheedges?Thatmeansyoucaneditthebutton.Draggingthesquarehandlesletsyouresizethebutton.Ifyouclickonthetext‘Button1’,youcanchangethetextinsidethebutton.
Ifyoudonothavethesquarehandles,youcannotedit(i.e.,Design)thebutton.Togetintodesignmode,right-clickthebuttontomakethesquarehandlesappear.
WhenyouarenotinDesignmode,themousecursorwillturnintoasmallhandindicatingyoucanclickthebutton.
Isthisparticularexample,ifyouclickthebutton,itreturnsanerror.
Thisisbecauseyoudidnothaveanymacrosavailabletoassigntothebutton.
ComboBoxAcombobox(shortforcombinationbox)isaboxwheretheusercaneithertypeinavalidvalue(likeatextboxthatwewillcoverlater)orshowalistofacceptablevalues(likealistbox,whichwewillalsocoverlater).
YouaregoingtobuildareportthatallowsuserstoseeSalesbySalespersonbytheStatetheyselect.
1-OpentheForms.xlsxfollowalongworkbook.
2-SelecttheComboBoxworksheet
3-OntheDevelopertab,ClickInsert,andthenclickComboBox.
4-Clickanddragtocreatethecombobox
Thecomboboxhasbeencreatedbutitdoesn’tdoanything;youneedtochangethepropertiestomakeitfunctional.
5-Right-clickthecomboboxandthenclickFormatControl
Inthiswindow,youcanadjustallthebehaviorsandappearanceofthecombobox.Allformitemsyouwillbuildwillhaveasimilarpropertiesboxwhereyouwillmakeitemsfunctional.Let’srevieweachtab.
Size
Thesizetabiswhereyoucanchangetheheightandwidthofthecombobox.Youcanalsoresizeusingthesquarehandlesontheobjectitself.However,thistabgivesyouprecisesizingcontrol.
Protection
Lockingacontroldoesnothinguntilyouprotecttheworksheet.Ifaformobjectislocked(allobjectsarelockedbydefault),whenyouapplyExcelprotection,otherswillnotbeabletochangetheformobject.Thisisusuallythebehaviorthatyouwant.Ifyouunlocktheformobject,thenwhenyouprotecttheworksheet,userswillstillbeabletochangetheformobject.
Properties
Selecting‘Movebutdon’tsizewithcells’meansthatifyouinsertseveralcolumnsorrows,theformobjectwillalsomoveintandemwiththeinsertions.Ifyouselectthesecondoption,‘Don’tmoveorsizewithcells,’theobjectwillnotbeaffectedbyanyinsertionsordeletionsofrows/columns.Theobjectwillstaywhereyouputit.
AltText
IfyouaregoingtopublishyourExcelfiletotheweb,somebrowsersmightnotbeabletodisplaytheformobject.Forthosecases,youcaninsertexplanatorytexthere.
FormatControl
Thisiswherethemagichappens!
InputRange:Thisiswhatthecomboboxwilldisplay
CellLink:Thecellthatwillbedesignatedtostorethechosenvalue
Dropdownlines:Howmanylinestoshow
3-Dshading:Addsabitofshadingtothebox
Inthismodel,Ibuiltaworksheettostoreallthevariouslookupsyouwillbeusing.ThiscomboboxwillbepopulatedwithalltheUSStates.Youcouldhaveputthelistof50StatesdirectlyintheComboBoxworksheetbutwhydothat?Ifnextweekyouneedtoaddmorecharts,pivottables,orwhatevertothismini-dashboard,youaregoingtohavetorememberthatyouhavealistthatneedstobehiddenormovedover.Itismucheasiertohaveonespecificworksheetwithallyourlookupsandthenhidetheentireworksheetbeforedistributingthefiletoothers.
7-ClicktheInputrangebox.
8-SelecttheLookupssheet
9-SelectrangeA2:A51
10-ClicktheCelllinkbox
11-SelectcellF2intheComboBoxsheet
YourFormatControltabshouldlooklikethis:
12-ClickOK
ChooseaStateandlookatwhathappens.
Thecomboboxdoesn’tputthestateyouchoseincellF2.ItputthenumberofthechoiceyoumadeincellF2.Inotherwords,Arizonaisthethirdmemberinthebox’schoicessotheboxputsinthenumber3inthecell.Yes,it’skindofannoyingbutwecandealwithit.
13-Bestpracticetip:Shadethecelllinkcell!Whenyouhavemanyformobjectswithmanycelllinks,itiseasytoforgetwheretheyare.Shadethecellacertaincolorsoyoucanidentifythemeasily.
GoaheadandshadeF2withanycoloryoulike.Iamalsogoingtolabelthecelllinkcell.Right-clickthecomboboxandlookattheNameboxtoseethenameExcelhasgiventhecombobox.MycomboboxisnamedDropDown4.
Onemorethingbeforewemoveon.Thecelllinkcelldoesnothavetobeinthecomboboxworksheet;youcanputitinthelookupworksheettopreventanyclutterofthedashboardworksheet.However,untilyougetcomfortablewithusingforms,Iwouldsuggestyouputthecelllinkonthesameworksheet.
OK,nowyouhaveanumberthatrepresentstheState.YouneedtoconvertthatintothenameoftheState.Therearemanywaystodothis.Theyarealljustascorrect.Ifyouknowofanotherway,goaheadanduseit.Insteadofjustgivingyoumyanswer,Iamgoingtothinkthroughithere.
LookattheLookupsworksheet.
Thecomboboxreturneda3forArizona.Arizonaisonrow4.Theextrarowisrow1,whichhasthecolumnlabel.IfyoutakeF2+1,youwillgetthecorrectrownumber.Thislistisnotgoingtochangeanytimesoon(Yeah,Ihearyou.PuertoRicomightbecomeaState.They’vebeensayingthatforyears!).
Hmmm,youcan’tuseVLOOKUPbecauseyouhavethenumber3,notthename.IntheLookupworksheet,youcouldmakecolumnAnumbersandthencolumnBtheStates.ThatwouldworkbutIwanttoshowyouanewformula:INDIRECT.
Indirectisafunnyformula.Ittakesatextrepresentationofacelladdressandgoestothatcelladdress.Itkindof‘reads’yourwords.Forexample,=INDIRECT(“B5”)wouldreturntheresultofwhateverwasinB5.
Inthiscase,youarereturningavaluefromtheLookupsworksheet,youknowthattheStateswillalwaysbeincolumnA.YoualsoknowthattherownumbercanbeexpressedasF2+1.DoyouseewhereI’mgoingwiththis?Ifnot,you’llseesoon.
Wearegoingtobuildatextstringthatevaluatestothecellchosenbytheuser.Let’suseArizonaasanexample.TheaddressofthecellthatcontainsArizonaintheLookupsworksheetis:
Lookups!A4
Tomakethatupusingtextvaluesyouwoulduse”Lookups!A4”.
Almostthere.However,youneedtoconnectthetexttothecombobox.Don’thardcodethe4.InsteaduseF2+1.
“Lookups!A”&F2+1
Note:IfyouareusingtheCONCATENATEfunctiontojointexttogether…STOPIT.Use&instead.Itdoesthesamethingandisfastertotype.
NowputthatderivedcelladdressinsideINDIRECT
=INDIRECT(“lookups!A”&F2+1)
14-InputtheaboveINDIRECTformulaintocellG2intheComboboxworksheet
15-Testitout.ThecomboboxwillchangethenumberandtheINDIRECTformulawillpullthecorrectStatename.
NowyouneedaSUMIFSformulatogetthesumofSalesbySalespersonandState
16-IncellC5,inputthisformula:
=SUMIFS(Data!G:G,Data!D:D,‘ComboBox’!$B5,Data!F:F,‘ComboBox’!$G$2)
(IgoindepthonhowtouseSUMIFandSUMIFSinmyotherlesson,MasteringExcelFormulas:SUM,SUMIF)
ThisiswhatIgetafterinputtingtheformula:
17-Prettyupthedashboardabit.Youcouldhidethelabelsandcelllinkcolumns.That’scertainlyaviableoption.WhatIliketodoisresizethecomboboxandmoveitontopofthecellsIneedtohide.
Aftermoving/resizingitlookslikethis:
Kindasneakyhuh?Nohiddenrowsorcolumns.OnceIprotecttheworksheet,noonewillbeabletomovetheobjecttoseebehindit.
DataValidation
Somemoreexperiencedreadersmightbeasking,‘whygothroughallthiswhenIjustuseDataValidationtobuildanin-celldropdownbox?”Granted,buildingaDataValidationBoxhasfewerstepsthanwhatyoujustdid,butthedrawbackisthatthedrop-downisnowboundtoacell.Changingthedashboardwouldtheninvolveinsertingrows/columns,resizingcolumnwidths,etc.togetitpositionedjustright.Itisquitetedious.Withacombobox,youjustrightclickandmovetheboxtowhereveryouwant.
CheckBoxCheckboxesareusedtoselectanoptionfromasetofoptions.Checkboxesallowuserstomakemultipleselections.
YouaregoingtoaddacheckboxtotheCheckBoxworksheet.ThischeckboxwillinteractivelyhideordisplaytheStateacustomerislocatedin.
1-IntheForms.xlsxfollowalongworkbook,selecttheCheckBoxworksheet
2-Insertacheckboxcontrol(Developertab>Insert>Checkbox)
3-Clickanddragthecross-hairtocreatethecheckbox
4-Clickinthebox,andthendeletethe“Checkbox1”textandtypein“DisplayState,”
Notethatyoucanonlychangethetextwhensquareresizehandlesarevisible.Iftheyarenotvisible,right-clickthecheckbox.
Thecheckboxhasbeencreatedbutitisnotfunctionalyet.Youneedtoalterthecheckboxpropertiestomakeitwork.
5-Right-clickthecheckbox,andselectFormatControl
ColorsandLines
ThecheckboxcontrolhasoneextratabthattheButtoncontroldidnot,theColorsandLinestab.
Fill:Thisoptionletsyousetthebackgroundcolorofthecheckbox.‘NoFill’meansthecheckboxistransparentanduserswillbeabletoseethroughit.
Line:Thisoptionletsyousetacoloredborderaroundthecheckbox
6-SettheFilltoawhitebackgroundandaddablackborder
7-ClicktheControltab.
Acheckboxcanhavethreestates,Unchecked,Checked,andMixed(acombinationofonandoffstateswhenthereareseveralcheckboxesselectedinagroup).Theoptionsinthistabarethedefaultstatethecheckboxwillhave.LeavethedefaultasUnchecked.
8-ClicktheCelllinkbox,andthenselectcellF2.
10-ClickOK.
Clickthecheckboxafewtimes.YouwillseethatthevalueincellF2changesfromTruetoFalsebasedonthecheckbox.Thecheckboxisnowworking.Thenextstepistochangetheformulastorespondtothecheckboxvalue.YouwilldothisusinganIFstatement.
11-ShadecellF2(tokeeptrackofthelinkedcell)
12-SelectcellD5
CellD5hasthefollowingformulathatpullsovertheStateofthecustomerincolumnB
=VLOOKUP(B5,Data!E:F,2,FALSE)
WhatyouneedtodoishavetheformulaworkonlyifcellF2saysTRUE.IfitsaysFALSE,thenthecellshouldbeblank.Todothis,nesttheentireVLOOKUPformulainsideanIFstatement.Here’saquickrefresheronIFstatements:
=IF([logicalconditionthatresultsinTrueorFalse],[resultifconditionisTRUE],[resultifconditionisFALSE])
ThisIFstatementisatinybiteasierthananormalonebecauseyoualreadyhaveacellthathasTRUEorFALSE(thecelllinkcell,F2).
13-ChangetheformulainD5toreadasfollows:
=IF($F$2=TRUE,VLOOKUP(B5,Data!E:F,2,FALSE),””)
Whatissaysis:ifthevalueofF2isTRUE,thengoaheadanddotheVLOOKUP,elsedon’tdoanything.
Testouttheformulabyclickingthecheckboxtoturnitonandthentoturnitoff.F5say“Alabama”whenthecheckboxisselectedandisblankwhenthecheckboxisclear.
Note:Youneedtouse$F$2,notF2.IfyouuseplainoldF2,whenyoucopytheformuladownitwillchangetoF3,F4,etc.Also,onewaytohaveExcelreturnnothingistousetwoquotations“”
14-CopytheformulaincellD5(theoneyoujustchanged)
15-SelectrangeD6:D21
16-RightclickandselectPasteSpecial>Formulas(Don’tdragdownorjustpaste,itwillmessuptheformatting)
Youarealmostdone.Thisishowthereportlookslikenow.
Youaregoingtoputsomefinishingtouches.FirstyouaregoingtohidetheStatelabelandthenyouaregoingtohidetheCellLinkcell.
17-ClickcellD4.IthasthewordStateinit.
18-AddanIFstatementthatwilldisplaythewordifcellF2saysTRUE.
=IF($F$2=TRUE,“State”,””)
Note:Bearinmindthattheword“TRUE”isdifferentthanthekeywordTRUE.TrueandFalsearekeywordsinExcel.TheyactuallymeanTrue(a1behindthescenes)andFalse(a0behindthescenes).Sincetheyarekeywords,youdonothavetoenclosetheminquotations“”.
Nowwhenyouclickthecheckbox,thetextinthecolumnwillappear/disappear.
19-MovethecheckboxovercellF2tohideitfromview.(Tip:right-clickittogetthehandlesandthendragitover.)
Whatabouttheformatting?Whenthecheckboxisuncheckedyouwillseeanemptyformattedrow.
TherearetwowaystohidecolumnDfromtheusers:
1-WriteamacrothatreactstothevalueinF2andhidescolumnD
2-WriteconditionalformattingrulesthatwillchangethecellformatsbasedoncellF2
Thislessonisn’tgoingtocoverhowtodoeitherofthosebecausetheyaren’twithinthescopeoflearninghowtouseforms.
SpinButtonThespinbuttonisadouble-headedarrowthatuserscanclicktoincrementordecrementanumber.Toincreasethevalue,theuserclickstheuparrow;todecreaseavalue,theuserclicksthedownarrow.
Thisexercisewillcenteronthisphrase:
Thexlargestcustomerisy
Youwilluseapinbuttontolettheuserdeterminehowmuchthenthlargestcustomerpurchased(5thlargest,3rdlargest,etc.).Youwillalsogetpracticeonhowtocombineformularesultswithtext.
1-ClicktheSpinButtonworksheet.
2-Clickthespinbutton(Developertab>Insert).
3-Clickanddragthecross-hairtocreatethespinbutton
4-Right-clickthespinbutton,andthenclick“FormatControl.”
Becausethebuttoncontrolonlyeitherincreasesordecreasesanumber,theoptionsareprettyself-explanatory.
6-Settheminimumvalueto1(What?Youwanttoseethe0thlargestcustomer?Thatmakesnosense!)
7-Clickinthecelllinkbox
8-SelectcellF2
9-ClickOK
10-ShadecellF2tokeeptrackofit(Iknowitseemsuselesstodobutit’sagoodpracticetogetinto.Whenyouhavemultipleformobjectseachwithdifferentcelllinkcells,theshadingwillreallyhelp.)
11-ClickOK.
12-Clickthespinbuttonafewtimestoseehowitworks.Whenthenoveltywearsoff,comebackhere.:-)
NowyouaregoingtostartchangingthetextincellB3toupdateitselfbasedonthespinbutton.
13-MakethetextinB2aformula.Inserta=beforethe‘The’andenclosethewholesentenceinquotations“”
Itshouldlooklikethis:
=“Thexlargestcustomerpurchasedy”
ThexinthesentenceisgoingtobethevalueinF2.Youcan’tjustdothis:
=“TheF2largestcustomerpurchasedy”
ExcelwilltreattheF2aspartofthesentence.Itdoesn’tknowthatF2isacellreference.Cellreferencesneedtobeoutsidethequotations.Youhavetosplitthesentenceintotwo;thepartbeforethexandthepartafterthex.Youwilljointhesentenceandthecellreferencewithaampersand.It’llmakemoresensewhenyouseeit:
=“The“&F2&”largestcustomerpurchasedy”
Theformulastartswith‘The’,joinsthevalueofF2,andthenjoinstherestofthesentence.Allthestaticwordsareenclosedinquotations,whilethecellreferenceisnot.
Myspinnerboxisshowingnumber3.Howdoyougetthethirdlargestcustomer?YouusetheLARGEfunction.Thesyntaxis:
=LARGE(array,k)
Array:Thisistherangeofnumberstoevaluate
k:thisisthekthnumbertoreturn
Toseethe3rdlargestnumberintherangeA1:10,usethis:
=LARGE(A1:10,3)
Ihopeyouseewherewearegoingwiththis.YouaregoingtousetheLARGEfunctionontheInvoiceTotalcolumnandthekwillbeF2.YouaregoingtodothisincellB3.
14-ChangetheformulainB3toread:=“The“&F2&”largestcustomerpurchased“
Noticethatthereisaspaceafterthewordpurchased.Keepittheresothesentencereadsnormally.
15-Typeinan&attheendoftheformulainB3.
16-TypeintheLargefunction.ItshouldlookupdatainData!G:GandthekparameterwillpointtocellF2
=“The“&F2&”largestcustomerpurchased“&LARGE(Data!G:G,‘SpinButton’!F2)
Nowusethespinnertochangethesentence.
Myworksheetlookslikethis:
Youcan’tpresentsomethinglikethat.Thenumbersaren’tformattedcorrectly.Unfortunately,youcan’tjustformatthenumbersusingthenumberformatbuttonbecausethetextandformulasconfuseExcel.Instead,youaregoingtouseaformulatoformatthenumber.
TheTEXTfunctionwillapplythespecifiedformattotheformula/numberthatisinsideit.Thesyntaxis:
=TEXT(value,format_text)
value:Thenumbertoformat
format_text:Thecustomnumberformatpatterntoapply
Customnumberformattoapply?What’sthat?Excelusesspecialcharacterstoapplynumberformatting,YouwillneedtoinputthosespecialcharactersasthesecondparameteroftheTEXTfunction.It’sveryeasytoseethecharacters.
ToFindtheCustomNumberFormatString
A-OntheHometab,clicktheNumberFormatbox,andthenselectMoreNumberFormats..
B-ClickCustom
Thecustomformatsaredisplayedinthepaneontheright.Ifyouselectedacellwithanumber,theSampleboxatthetoprightshowsyouwhatthenumberwilllooklike.
Youdon’thavetoremembertheformatstring.Asyoucansee,theycangetprettycomplicated.YoucanselecttheoneyouwantthenhighlightitintheType:boxandthencopyit.Iamgoingtousethe#,##0format.(Commasandnodecimals)
OKbacktotheexercise…
Onceagain,theformattextisgoingtobe#,##0.YouaregoingtocombinetheLARGEfunctionwiththeexistingfunction.
Thefunctionyouhaveisthis:
=“The“&F2&”largestcustomerpurchased“&LARGE(Data!G:G,‘SpinButton’!F2)
YouneedtoputtheLARGEfunctioninsidetheTEXTfunctionandusetheformatstring,likethis:
TEXT(LARGE(Data!G:G,‘SpinButton’!F2),“#,##0”)
Theentirefunctionwillnowbethis:
=“The“&F2&”largestcustomerpurchased“&TEXT(LARGE(Data!G:G,‘SpinButton’!F2),”#,##0”)
Andtheresultisthis:
17-ThelaststepistohidetheCellLinkcell.Youcanmakethespinboxwiderandplaceitoverthecelloryoucanhidethecolumn.It’syourchoice.
ListBox
Alistboxisaboxthatdisplaysalistofitemsfromwhichausercanchooseoneitem.Thisisagoodcontroltousewhentherearemanychoicesandyouwanttodisplaythemalltotheuser.
Let’slookattheFormatControloptionsfirstandthenworkthroughthisexercise.
InputRange:therangethatpopulatesthelistbox
CellLink:thecellthatwillstoretheselection(s)
SelectionType:
Single:Thisoptionenablesonlyasinglechoicefromtheitemsinthelist
Multi:Thisoptionallowseitheroneormultiplechoicesbutthechoicesmustbenexttoeachother
Extend:Thisoptionallowsonechoice,severalchoicesnexttoeachotherandchoicesnotnexttoeachother
Unfortunately,whentheMultiandExtendoptionsareselected,theCellLinkvalueisignoredandyouhavetousemacros(VBA)toworkwiththelistboxobject.YouaregoingtoworkwiththeSingleselector.
TheListBoxworksheethasthisreport:
YouaregoingtoaddalistboxthatdisplaystheUSStatesandthenwriteaformulathatextractsthatinformationfromtheDataworksheet.
1-SelecttheListBoxworksheetintheForms.xlsxworkbook.
2-CreateanewListBox(DeveloperTab>Insert)
3-Clickanddragthecross-hairtocreatetheListBox.
4-Right-clicktheListBox,select“FormatControl,”andselecttheControltab.
5-ClicktheInputRangeboxandselecttheSalespersonintheLookupworksheet(B2:B11).
6-ClicktheCellLinkboxandselectcellF2.
7-ClickOK
8-ShadecellF2inacolorofyourchoice.
9-LeavetheSelectiontypeasSingle.
10-ClickOK.
11-Right-clicktheListBoxandadjusttheheightsoyoucanseetheentirelistofsalespeople
Asyouclickdifferentsalespeople,youwillnoticethattheListBoxpopulatescellF2withthenumberoftheitemselected,nottheitemitself.Youaregoingtowriteaformulathatretrievesthesalesperson.Thentheformulainthetablewillretrievethesumfortheselectedsalesperson.
12-TypeinthisformulaincellG2:
=INDEX(Lookups!$B$2:$B$11,F2)
TheINDEXfunctionreturnsthecellfromarangeofcellsbasedontherowandcolumnnumber.It’sanotherkindofVLOOKUP.Whatthisformulaissayingis:Fromrange$B$2:$B$11,returnthevaluefromtherowshowninF2.
NowyouneedtoinputaSUMIFSformulainrangeC5:C54.
13-SelectcellC5
14-Inputthisformula:
=SUMIFS(Data!G:G,Data!D:D,$G$2,Data!F:F,B5)
15–CopythisformulatoalltheotherrowswithUSStatesincolumnC
Youneedtoupdatethereporttitletobedynamic.
16-SelectcellB2
17-Changethetexttothisformula:
=“SalesbyStatefor“&G2
18-Movethelistboxtocoverthecelllinkcell
OptionButton
Theoptionbuttoncontrolisverysimilartothecheckboxcontrolinthattheyarebothcontrolsthatletusersmakeaselection.Themaindifferenceisthatcheckboxesallowuserstomakemultiplechoices;thatis,theycancheckmultipleboxes.Optionboxes,ontheotherhand,aredesignedtoallowonlyonechoice.Ifyouhaveasetofthreeoptionboxes,selectingonewillautomaticallydeselecttheothers.
YouaregoingtocreatetwooptionbuttonsthatwillallowyoutodisplayeithertheOrderDateortheInvoiceDateinasamplereport.
1-OpentheForms.xlsxworkbook(ifitisn’topen).
2-GototheOptionButtonworksheet.
3-Createtwooptionbuttons(DeveloperTab>Insert).
4-Right-clickOptionButton1andselect“EditText.”
6-ChangethetextfromOptionButton1toInvoiceDate
7-RepeatthepreviousstepstochangethetextofOptionButton2toOrderDate
8-Right-clickonInvoiceDate,andselect“FormatControl.”
10-SelecttheControltab.
TheOptionButtononlyhastwovalues,UncheckedandChecked.
CelllinkthecellthatwillstorethevalueofthecheckedoruncheckedOptionButton.
11-ClickinsidetheCelllinkboxandselect….youguessedit,cellF2.
12-ClickOK.
13-ShadecellF2anycoloryouliketokeeptrackofit.
14-Right-clicktheOrderDateoptionbutton,andselect“FormatControl.”
16-SelecttheControltab,andthenclickinside…whoatheCelllinkisalreadypopulated?
Yes,whenyouhaveseveraloptionbuttonstheyallinheritthecelllinkcellofthefirstone.Thatcell,F2inyourcase,willhavethenumberoftheoptionbuttonthatwasclicked.HavingthesamecellstorethevalueofalltheoptionboxesishowExcelpreventsauserfrommakingmultipleselections.
Clickeachoftheoptionbuttonstoseehowthenumberchangesfrom1to2.
NowyouhavetoputaformulaincolumnDthatwillretrieveeithertheInvoiceDateortheOrderDatebasedonthevalueinF2.
18-SelectcellD5.
19-Inputthisformula:
=IF($F$2=1,INDEX(Data!$A$2:$A$100,MATCH(‘OptionButtonComplete’!B5,Data!$C$2:$C$100,0)),INDEX(Data!$B$2:$B$100,MATCH(‘OptionButtonComplete’!B5,Data!$C$2:$C$100,0)))
OK,Iknowit’sahugeformula.Youdon’thavetotypeitin.Iputitinatextboxinthefollowalongworkbookforyou.CopytheformulaandpasteitintocellD5.Thencopytheformulatoalltheothercells.
LaststepistoputanIFstatementincellD4tohavethecolumntitleupdateautomatically.
20-SelectcellD4.
21-Inputthisformula:
=IF(F2=1,“InvoiceDate”,“OrderDate”)
Nowyouhavetohidethecelllinkcell.Ifyouwanttohavetheoptionbuttonshidethecelllinkcell,youhavetochangetheirtransparency.
22-Right-clicktheInvoiceDateoptionbuttonandselect“FormatControl.”
24-SelecttheColorsandLinestab.
25-Changethe‘NoFill’colortoAutomatic.
26-ClickOK.
27-DothesametotheOrderDateoptionbutton.
28-Move/Resizethebuttonstocoverthecelllinkcell.
29-Optional:Removethegridlinestogivethereportacleanerlook
Note:WhataboutthatHUGEformula?WhatisINDEX,whatitisdoing?LookattheIndexworksheetinthefollowalongworkbooktolearnhowitworks.Iknowitlooksintimidatingbutitreallyisn’t.
GroupBox
TheGroupboxisacategorizationobjectthatyoucanusetogroupsimilarformobjects.Forexample,theimagebelowshowsfouroptionbuttonsthathavebeengroupedinsideaGroupBoxwiththetitleRegion.
GroupBoxesandOptionButtonsinteractinaspecialway.GroupBoxeschangehowOptionButtonsfunction.
Considerthissituation(thisisintheGroupBoxworksheet):
Youwanttheusertochooseoneoptionfrom(North,South,East,West)andaseparateoptionfrom(Red,Orange,Yellow,Green,Blue,Indigo,Violet).Asyoulearnedinthelastsection,Optionbuttonsareformsthatallowonlyonechoice.Whenyouselectone,alltheothersaredeselected.Tryit.SelectaRegionandassoonasyouselectaColor,theRegionwillbedeselected.Additionally,alltheOptionButtonswillhavethesameCellLinkcell.
Howcanyouresolvethis?ByusingtheGroupBox.WhenaGroupBoxenclosesseveraloptionbuttons,ExceltreatsthoseOptionButtonsasadistinctgroup.UserswillbeabletochooseonlyoneoptionfromthegroupinsidetheGroupBox.EachgroupwillhaveitsownCellLinkcell.
YouaregoingtoaddtotwoGroupBoxestoseparatetheOptionbuttonsintotwogroups.
1-SelecttheGroupBoxworksheet.
2-InontheDevelopertab,clickInsert,andthenclicktheGroupBoxbutton.
4-Clickanddragthecross-hairtocreatetheGroupBoxthatsurroundstheRegionoptionbuttons
5-Right-clicktheGroupBoxtitle,andthenselect“EditText.”
7-ChangetheGroupBoxtitletoRegions.
8-CreateanotherGroupboxaroundthecoloroptionbuttons,andthenchangeitstitleto“Colors.”
12-Right-clicktheRedOptionButton,andthenselectFormatControl.
14-ClickinsidetheCelllinkbox,andselectcellH2.
Thisistricky!!!TheGroupBoxmustenclosetheENTIREOptionbuttoncontrol.
Lookatthis:
IrightclickedtheBlueoptionbuttonbutitisnotentirelysurroundedbythegroupbox.Thismeansitisnotpartoftheset.Ineedtoresizethegroupboxortheoptionbuttonsothebuttonfitsinside.NowIhavetochangetheCelllinktoH2fortheBlue,IndigoandVioletoptionbuttons.
16-Makesuretheentireoptionbuttonsfitinsidetheirrespectivegroupboxes(bothRegionsandColors)
17-ChangethecellLinkforeachgroup(RegionslinktoF2,ColorslinktoH2)
Sometimes,Excelgetsconfused.Ifyoucan’tgettheCelllinktostick,deletetheGroupBoxanddrawanotherone.
NowyoushouldbeabletoclickoneregionandseetheresultincellF2andthenclickacolorandseetheresultincellH2.
Labels
Labelsdon’t‘do’anything.Theyhavenofunctionalityotherthantoconveyinformation.Theyaresimilartocommentsandtextboxes.OnceyoucreateaLabel(followingthesamestepswehaveusedinapreviousexercises)justclickinsidethelabelandputwhatevertextyouwantinit.
Labelsareverysimple,sothereisnoworksheetfortheminthefollowalongworkbook.
ScrollBarScrollbarsareeverywhereinWindows.ScrollbarsaretheobjectsyouclickinExcel,Firefox,andInternetExplorertopageupanddown.Scrollbarscanbepositionedhorizontallyorvertically.
TheScrollbarworksheethasthisreportonit.Youaregoingtochangethisreportsothatuserscandecidehowmanycustomerstheywanttosee.
1-SelecttheScrollBoxworksheet.
2-IntheDevelopertab,clickInsert,andthenclicktheScrollBarbutton.
4-Clickanddragthecross-hairtocreateascrollbar(itcanbehorizontalorvertical)
5-Right-clickthescrollbarcontrol,andthenselect“FormatControl.”
7-SelecttheControltab.
Mostoftheitemshereareself-explanatory.ThePagechangeitemdetermineshowmanypagestoscrolldownwhenthegraypartofthescrollbarisclicked.TheIncrementalchangeitemdeterminestheincreasewhenthetoporbottomarrowisclicked.
8-Filloutthesettingspertheimagebelow.Sinceyouwanttheuserstoselectanumberbetween1and10,thosewillbetheminimumandmaximumvalues,respectively.Thepagechangeis2sowhentheuserclicksinthatareathevalueswillincreaseby2.
9-ClickOK.
10-ShadecellF2anycoloryoulike
NowyouhavetoconnectthereporttocellF2.Iputtheformulasinthereforyou(cellsC5:C14havetheLARGEfunctioninthem),allyouneedtodoisputanIFstatementaroundtheformulassotheyinteractwithcellF2.
11-SelectcellB3(thetitle),andchangeitstextto
=“Top“&F2&”InvoicedCustomers”
13-SelectcellB5.
ThegoalistoshowthecountofcustomersthatareincellF2.Thatisthelimit;youdonotwanttoshowanymorecustomersthanthat.Toaccomplishthis,youaregoingtocomparethenumberincolumnAtothenumberinF2.IfthecolumnAnumber>F2,donotdisplayanything.
14-ChangetheformulaincellB5tothis:
=IF(A5>$F$2,””,INDEX(Data!E:E,MATCH(ScrollBar!C5,Data!G:G,0)))
15-CopytheformuladowntherangetocellB14.
16-SelectcellC5.
YouaregoingtoputtheC5formulainsideanidenticalIFstatement
17-ChangetheformulaincellC5tothis:
=IF(A5>$F$2,””,LARGE(Data!G:G,ScrollBar!A5))
18-CopytheformuladowntocellC10
Nowclickthescrollbarandseehowthereportreactstothechanges.
19-Thelaststepistohidethecelllinkcell.Youcanresizethescrollbarandmoveitoverthecelloryoucanhidethecolumns.It’syourchoice.
MovingandPositioningControls
Havinganicelookingformisimportanttoyourusers.However,gettingformobjectsalignedjustrightisverytediousandtimeconsuming.I’mgoingtoshowyousomeExceltrickstohelpyougeteverythingpositionedeasily.
1-SelectthePositioningworksheet.
Noticehowtheformobjectsarenotalignedcorrectly.
Selectingobjects:ClickandholdtheCTRLkeywhileclickingonseveralobjectstoselectandmovethem
OR
IntheHometab,selectFind&Select>SelectionPane
ClickingontheSelectionPaneletsyouselectanyorallobjects(usetheCTRLkeytoselectmultipleobjects).Youcanalsohideobjects(Justhide,notdelete)byclickingtheeyeiconontherightofthepane.Ifyouselectandobject,thendoubleclickit,youcanchangeitsTitlerightintheSelectionPane.Forexample,youcandoubleclickCheckBox7andrenameittoTulips.
1-SelectalltheOptionButtons.
AnewFormattabappearswhenyouselectaformobject.
2-ClicktheDrawingTools,Formattab.
3-ClicktheAlignbutton.
Youwillseetheseoptions:
Usetheseoptionstogettheselectedobjectspreciselyaligned.Mostofthechoicesareself-explanatory.Thereareafewthatneedsomeclarification.
DistributeHorizontally/DistributeVertically
-Theseoptionsdonotmovethetoporbottomitems.Rather,theyspreadoutallthe‘inbetween’itemssotheyareevenlyspacedout.
SnaptoGrid
-Ifyouclickthison,whenyoumoveanobject,itsnapstothenearestgridintersection
SnaptoShape
-Ifyouclickthison,whenyoumoveanobject,itsnapstotheedgesofthenearestobject
OtherLessonsMasteringExcel90InterviewQuestions
(http://www.amazon.com/dp/B009SZYDOC)
MasteringExcelFormulaTipsandTricks
(http://www.amazon.com/dp/B00G4USD5E)
MasteringExcelFormulasIF,AND,OR
(http://www.amazon.com/dp/B009FD7J2K)
MasteringExcelFormulasSUM,SUMIF
(http://www.amazon.com/dp/B00A9JFER6)
MasteringExcelFormulasVLOOKUP
(http://www.amazon.com/dp/B009M5F5IG)
MasteringExcel:Autofilter,AdvancedAutofilter
(http://www.amazon.com/dp/B00ASJBZFW)
MasteringExcel:ConditionalFormatting
(http://www.amazon.com/dp/B00K7USCH6)
MasteringExcel:MSQuery
(http://www.amazon.com/dp/B00DT5FH5G)
MasteringExcel:NamedRanges,OFFSETandDynamicCharts
(http://www.amazon.com/dp/B00B6I25BW)
MasteringExcel:PivotTables
(http://www.amazon.com/dp/B00C56564M)
MasteringExcel:SharingWorkbooks
(http://www.amazon.com/dp/B00KVGRI4Y)
MasteringExcelMacros:Introduction
(http://www.amazon.com/dp/B00O2OOJ7A)
MasteringExcelMacros:Introduction(Book1)
(http://www.amazon.com/dp/B00O2OOJ7A)
MasteringExcelMacros:Debugging(Book2)
(http://www.amazon.com/dp/B00OE4821W)
MasteringExcelMacros:BeginningtoCode(Book3)
(http://www.amazon.com/dp/B00PFWDZXC)
MasteringExcelMacros:IfStatements(Book4)
(http://www.amazon.com/dp/B00QGWP8PI)
MasteringExcelMacros:Looping(Book5)
(http://www.amazon.com/dp/B00SCPTJH0)
MasteringExcelMacros:ObjectVariables(Book6)
(http://www.amazon.com/dp/B00TSN7IP0)
top related