Home > excel, Learning, Lesson > Excel, VBA Codes and Formulas-1

Excel, VBA Codes and Formulas-1


Learn To : In Excel – Drop Down List in a cell.

I start working on a project for a friend, he has some requests to be done on a MS-Excel, so I thought why not to write several posts about MS-Excel capabilities.

MS-Excel is a great application to be used, I will not write to praise the program, but will jump to coding. In Excel we can use dozens of built in ready to use tools and Formulas. Also MS-Excel has a very good, easy to use and learn programming language called VBA (Visual Basic for Application) which with simple code we can perform some tasks and create Menus, buttons and functions that runs in background.

Starting from this post we will use some built-in functions and VBA’s one that I am using in my Excel files.
So let’s say we have a table that has a column called “Pay-Method”, the value we save in this column always is one of three: “Cash, Card, Cheque” we don’t want the user to type it every time, but to select it from a list.

To do this we can use an static approach or a Dynamic approach.

Static Approach: if we have a specific answers that can’t be more like (Yes,No) or as our example above (Cash, Card, Cheque) or similar cases then we can use an static approach. So First Select First cell in the Table, from the menu we will select “Data” then “Data Validation” and select Data Validation.



Now,

  • From the Allow box, we select List.
  • Then in Source box, we need to type the items we want to appear in each cell as a drop-down list separated by a comma. Cash, Card, Cheque
  • We need to check [Ignore Blank and In-cell Dropdown] Then Click OK.

Now we will have a small arrow on the first cell in our Table, to copy the List to other Cells (Beneath) hold the small DOT and Drag it down.

And Now we are DONE, we just create a Dropdown List in all “Pay-Method” Column in the table..



Dynamic Approach: If we have a list of items and this list is growing (We may add more items ti it), such as Fruit list in a grocery, then we will use the Dynamic drop-down list.
In this case I prefer to use a new Excel Tab call it “Setting”, so we will create one. On that Tab create a Table call it “Fruit” and list all the fruits you want to be appear in the drop-down cell list (In this example I will list down F1, F2, F3, F4, F5), Select the table and from Menu select “Format as Table” and just chose any format you want. Here is a screen shot.

Here is a screen shot

Move the Mouse next to table head until you get small Arrow, and give it a name. as in here..



Now go-back to our main Table and using the same way we did in the Static Approach (from the menu we will select “Data” then “Data Validation” and select Data Validation.) AND

  • From the Allow box, we select List.
  • Then in Source box, we need to type the Name we select for our table “F-Table_1” for the fruit table
  • We need to check [Ignore Blank and In-cell Dropdown] Then Click OK.



Done … Now if we add new Fruit to the list “F6” it will appear in our Drop-Down cell list.

🙂 Have Fun ..



Follow me on Twitter..




By: Ali Radwani




  1. March 18, 2020 at 6:19 pm

    I receive this comment from @eddyparkinson , asking for an example of adding UI to spreadsheets.

    My replay is: Yse you can create Menus and data collecting forms and more. You till me more about what you want to do, so I can help you.

    Eddy post:
    “Adding a UI to a spreadsheet – I am looking for examples of adding a UI to a spreadsheet as part of a research project. I have been working on creating a simpler way to add a UI to a spreadsheet, e.g. https…. So I am hoping to understand the demand side of adding a UI to a spreadsheet. To do that I would like examples, e.g. screenshots, or actual spreadsheets. “

    • March 19, 2020 at 3:17 am

      Thanks for the reply.

      My aim is to make it easy to add a UI to a spreadsheet. To add a UI to a spreadsheet you only need spreadsheet formulas. But some background first …

      My background … I understand VBA, I taught myself to program when I was 13. I have created a lot of software over the last 30 years. I have made a lot of mistakes, and learned from maybe half of them.

      The UI and calculation problem. A UI helps the user. A UI guides the user and helps users understand how to use an application. A UI interacts with the user. For a UI to interact with a user, calculations are needed. The calculations are like rules. e.g. a calculation is needed to workout if a button should change colour, should the button be red or grey. Calculations, aka formulas, help guide the user.

      The problem I face is understanding the demand. I don’t know what people are looking for and so I want examples of actual user interfaces that people have added to a spreadsheet. To understand the demand I what to see what others have created. Examples of people using VBA to add a menu, or input boxes or even a complex user interface with several screens. I want to understand the demand, I want to know what people have built in the past, to predict what people might want in the future.

      Thanks for your help with this.
      Eddy Parkinson PhD

      • March 19, 2020 at 8:28 am

        Aha .. I got your point now. Well, I am usually creating some type of custom and limited UI on spreadsheets for some friends, there requirement are very strate-forward such as ” I want to type the data here and a button to do Capitalizing, fixing the date format, also copping all the data to another sheet.” .

        A use ask to have a way to change the colors of columns based on some value range, then if he change his mind then he want to click a button and every thing will changed as he want. ..

        You know, to come-up with all user demand is not easy, each one will have there own requirements.

        Ali,

      • March 20, 2020 at 3:51 am

        Thanks. Do you know much about the background. Why was it important to capitalize and fix the date formatting and copy sheet data?

        >You know, to come-up with all user demand is not easy, each one will have there own requirements.

        Agree, one interesting thing about this is using information to make decisions and expert systems. So we use information to make decisions, but when you look at an expert system, you find it is full of exceptions. e.g. follow rule 1 except in case B when you should follow rule 2. Expert decision makers tend to have many rules and many exceptions. This looks to be one reason why requirements tend to be complex.

        Anyway … your help is much appreciated.

        Eddy.

      • March 20, 2020 at 11:17 am

        First for there demand [Capitalizing] for example needed so if they print the report it will looks fine.

        In some complex cases I am creating a new [Setting Tab], putting most of conditions and VBA code buttons to control all the requirements. So from my previous comment I just add 3 radio buttons titeled [All Small, All Capitals Capitalize First] and in Next Cell he/she will write the Column head to be re-format; and once button clicked range will be re-format. And so on ..

        Ali,

      • March 24, 2020 at 3:57 am

        This is great.
        So you are Capitalizing a column of data. They have data in a table and they use the data to print reports.
        Am I right in thinking tables of data are important, and so are reports. Just wondering about the bigger picture, are tables of data and reports key to the task?
        It looks like you are helping them with automation that formats the data. You are formatting the data for use in reports. Also for analysis, as well as helping maintenance … you said “copping all the data to another sheet.”.
        Overall, it looks like they want an easier way to apply formatting rules to a column of data in a data table.
        Thanks for your help. Much appreciated.
        Eddy.

      • March 24, 2020 at 9:02 am

        Yes .. all your assumptions are right. In first sheet I create a data entity form, with a button to copy the information to another sheet (say sheet2) the VBA code will reformat some data before copying them (such as Date, and capitalizing others) .. another sheet to have a basic and detail summary (based on there requirement) and some other buttons to copy reports with header and formatting page .

        Ali,

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: