K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Using List Box Controls

1/6/2020

 
Picture
Excel allows you to define graphical controls within your worksheet. Buttons and other controls are placed in your worksheet by displaying the Developer tab of the ribbon and using the Insert tool in the Controls group.

One of the form controls you can place in your worksheet is called a list box. This is a scrollable list of options from which the user can choose. You might use these in a worksheet if you have a group of options from which you want the user to choose. Let's assume you have a worksheet that provides a price quote and the price is contingent on which colour of widget the user wants. Having the users type the name of a colour is open to error. For instance, they might enter the name of a colour you don't carry, misspell the colour name, or refer to a colour as "gray" when your terminology is "slate." Rather than have them type a colour in, you can present the various colours in a list from which they can choose.

To place a list box control in your worksheet, display the Developer tab of the ribbon, click the Insert tool (in the Controls group), and then click the List Box tool in the Form Controls section. You then use the mouse to define the rectangle that will hold the list box and the scroll bar at the right side of the box.

To use a list box effectively, you must link it to two separate areas on your worksheet. The first is called an input list, the options in the list. The second is the cell link, which contains the currently selected option. To set these areas, follow these steps:

  1. Choose the list box control (the one you placed on your worksheet) by clicking on it once.
  2. Click on the Properties tool in the Controls group on the Developer tab of the ribbon. Excel displays the Format Control dialog box.
  3. Make sure the Control tab is selected.
  4. In the Input Range field, supply the name or address of the cell range that contains the options to be included in the list box.
  5. In the Cell Link field, specify the cell that will contain the value indicating which option is selected.
  6. In the Selection Type portion of the dialog box you can specify how many options in the list can be selected at the same time. When you use list boxes on a regular Excel worksheet, it is important to leave this option set to Single. The other options only have meaning if you are creating a custom dialog box.
  7. Click on OK.

You should note that the Cell Link setting specifies a bi-directional relationship between the cell and the control. This means that a change in the selected option will change the value in the cell, but a change in the cell value will also change which option is selected in the list box. The value stored in the linked cell indicates the number of the option selected in the list. Thus, if you have a list of seven options and the second option in the list is selected, the value in the linked cell will be 2.


Comments are closed.

    Categories

    All
    2022
    Accountant
    Accounting Software
    Accounting Solutions
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Backup
    Bitcoin
    Blockchain
    Business Automation
    Business Continuity
    Business Intelligence
    Business Management
    Cloud Computing
    Collaborate
    Collaboration
    Colonial
    Computer
    COVID 19
    COVID-19
    CPA
    CRM
    Cryptocurrency
    Customer Relationship Management
    Cybersecurity
    Cyptocurrencies
    Dext
    Doc.It
    Ecommerce
    Emerging Technologies
    Entrepreneurs
    Excel
    Forecasting
    Forecast Sheet
    Google
    Grammarly
    HR
    Internal Controls
    Internal Priorities
    KPIs
    LET
    Links
    Management Reports
    Microsoft 365
    Microsoft Office 2021
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    Pandemic
    Paperless
    Personal Computer
    PivotTables
    Power Automate
    Power BI
    PowerPoint
    Productivity
    QuickBooks
    Quickbooks Online
    Ransomware
    Receipt Bank
    Remote Work
    RPA
    Sage 50
    Security
    Small Business
    Small Business Accounting
    Small Business Accounting Software
    Stockhistory
    Technologies
    Technology
    Tech Update
    Windows
    Windows 10
    Windows 11
    Word
    Work At Home
    Workflow
    Work From Home
    Xcm
    Zoho
    Zoho CRM

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com


    Bernie Smith
    Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.

    RSS Feed

Training & Education

Webinars
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  484 Scarlett Crescent  |  Burlington, ON L7L 5M2  |  (905) 633-9772
© 2023 K2E Canada Inc. ALL RIGHTS RESERVED.