Excel - Prevent ActiveX combobox from entering value outside validation list

ActiveX combo box in excel allows a user to type and auto select the word from the list. By default , if you type a word that is outside the "List fill range", combo box still accepts it.

In this post I will show you how to tweak the combo box control to restrict any input outside the "List fill range". In addition I will also show you how to make your combo box "List fill range" to be dynamic using tables in Excel.

Step 1: Insert ActiveX combo box

To insert a combo box ensure that you have enabled the Developer tab in your ribbon. If you haven't then go to File - Options - Customize Ribbon. Check the Developer tab on the right. On the Developer tab, go to the Form controls drop down and select insert combo box from ActiveX form controls. Draw the combo box on any excel sheet you want.

Step 2: Convert your list to Excel Table

In this illustration, I have drawn the combo box on sheet1 and my list is in Sheet2. Convert your list to excel table by pressing Ctrl + T.

Step 3: Assign table values as "List fill Range" for the combo box

With the Design mode on, double click the combo box to enter the code in Visual Basic Editor.

By default, your cursor will appear inside ComboBox1_Change() event handler. We will be using ComboBox1_Click() event handler to fill the combo box with the product list. Whenever the combo box is clicked, the code written in the Click event handler will assign the values in the table to the combo box.

You can copy paste the code given below

Private Sub ComboBox1_Click()

    'Variable declaration

    Dim str As String

    

    'Storing the range used by the table in the variable declared

    str = Sheet2.Range("Table1").Address

    str = "'" & Sheet2.Name & "'!" & str

    

    'Assigning the table range as ListFillRange for the combobox

    ComboBox1.ListFillRange = str

    'Linking cell A1 to show the combox value

    ComboBox1.LinkedCell = "'" & Sheet1.Name & "'!" & "A1"

    

End Sub

I renamed Sheet2 which contained the table to "List". However, in the code written I continue to use the VBA sheet name and not the literal sheet name that is visible. This will ensure that your code runs perfectly even if you change the sheet name later.

 Step 4: Preventing the combo box from accepting a value outside the ListFillRange

In order to prevent the combo box from accepting a value outside the ListFillRange, I will be using the LostFocus event handler to evaluate if a match was found or not. If a match is not found then we will set the value of the combo box to nothing.("").

Use the code given below and paste it after the Click event handler

Private Sub ComboBox1_LostFocus()

    If Not ComboBox1.MatchFound Then

        ComboBox1.Value = ""

    End If

    

End Sub

Step 5: Testing

Added a new value to the table, "Cardboard"

Typing nonsense text and checking the combobox. Cell A1 reflects the nonsense text as it is typed. However, the moment you click on any cell in the sheet combox loses focus and the handler sets its value to nothing("").

Thank you for reading!

If you liked the trick or have any questions, let me know in the comments section below. 

Inventory Turnover Ratio / Stock Turnover Ratio

Inventory Turnover Ratio / Stock Turnover Ratio measures how effectively stock or inventory of the company is managed. 

It is calculated by dividing the cost of goods sold with the average inventory for a particular period. The word "turnover" can be referred to as "the times something is sold". In this context, it the stock.

For Example:

Company ABC Ltd. manufactures pencils. For FY15-16, it manufactured 1200 units of pencils. The cost of manufacturing one pencil is Rs. 2. ABC Ltd had an opening balance of 200 pencils at the beginning of the financial year. At the end of FY15-16, it had a closing stock of 100 pencils. 

COGS = Rs. 2 X 1200 = Rs. 2400

Average Inventory = (200 + 100)/2 = 150 pencils

Average Inventory (in Rs) = Rs. 2 X 150 = Rs. 300

Inventory Turnover Ratio = 2400/300 = 8

It means that in the entire year the company was able to sell 8 times its average inventory.

A higher ratio indicates that a company is able to effectively sell its inventory. It is not stocking and managing its purchases properly. On the other hand, a company with a low turnover ratio indicates that the company is not able to sell. It is overstocking and not managing its purchases effectively. The ratio depends upon industry to industry.

In order to find how many days does it take for the turnover to be 1, we cross multiply

Turnover                 Days

8                              365

1                              x?

Number of days for 1 turnover (to sell 1 unit of pencil) = 365/8 = 45.625. Therefore, we can say that it takes 45.625 days for average inventory of pencils to be sold.

BCG Growth-Share Matrix

Who designed it?

Boston Consulting Group (BCG) Consultant Bruce Henderson developed this model in early 1970’s.

What is it used for?

The analysis helps to understand which brands or Strategic Business Units (SBUs) should the firm invest in and which ones should be divested.

Visual representation of the tool

It portrays the firm’s portfolio on a quadrant along the relative market share (horizontal axis) and speed of market growth (Vertical axis).

Industry attractiveness = Growth rate of that industry

Competitive position = Relative market share

These two dimensions reveal the likely profitability of the business portfolio in terms of cash needed to support that unit and cash generated by it.

 Explanation & how to use it?

1. Dog:
SBUs that have low market share and low growth rate are classified as dogs. This implies that
Cash consumed – Low
Cash generated – Low
Decision: Divestiture or selling off such units.

2. Question Marks:

SBUs that are growing rapidly but have a low market share are classified as Question marks. This implies
Cash consumed: High (as they are rapidly growing)
Cash generated: Low (as they have low market share)
Decision: A careful analysis of such SBUs should be undertaken as there are two possibilities that can arise.
a. Possibility that they get converted to Stars and eventually Cash Cows (Favourable): This can be done by investing more money in them so they gain market share.

b. Possibility that they get converted to dogs (Dangerous): This can happen if the market growth declines and they are not able to capture the market share. As they have already consumed a lot of cash, it might result into losses if such a situation occurs.

3. Stars
SBUs that have high market share and high growth rate are classified as Stars. This implies
Cash consumed – High (as they are growing)
Cash generated – High (as they have a high market share)
Decision: The net of cash generated and cash consumed does not significantly contribute to the profitability of the business. If the Star can maintain the high market share as the market growth declines then these Stars can be converted to Cash Cows. Organizations should have many Stars that get converted to Cash Cows as Cash Cows ensure future cash generation.

4. Cash Cows

SBUs that have high market share (Market leaders) in a mature market are classified as Cash Cows. This implies:
Cash consumed: Low (as they operate in a mature market)
Cash generated: High (as they are the market leaders)
Decision: They are the profitable segments of the business. The profits are used to cover the costs and also invested back  in business to convert Question marks to Stars.

Limitations

1.       It ignores factors other market share and growth rate that contribute to the profitability of the business.

2.       It assumes that each business unit is independent of others.

3.       The definition of market can make a huge difference as a SBU’s serving a niche can be classified as Cash cow or Dog depending upon what is considered as market (only niche segment or the entire industry).

The Seven S Framework

Who designed it?

McKinsey’s Business Consultants Robert H. Waterman and Thomas J Peters featured this framework in the book “In search of Excellence” during the late 1970s.

What is it used for?

It is used for identifying and analysing the internal strengths of a company. The 7S together form a network which serves as a competitive advantage to the company.

The logic behind 7S is that your competitor may replicate any one or more of the 7S attributes but it is near to impossible for the competitor to replicate all the 7S.

Visual Representation of the model:

As seen above it is a complex web of inter-relationships between the 7S. It helps you visualize the effects of the changes in either of the S attributes to the other S attributes.

 

Why do consultants use it?

It helps them to diagnose how organizations are created.

It also helps them to understand the ability of an organization to change after taking into account the relationship between the 7S and its effect on one another. It gives them an idea about what changes to suggest to the organization in a way that all the 7S are aligned.

Explaining each of the 7S

1.     Skills

It comprises of institutional skills as well as individual skills. It is about how the staff works.

Due to globalization, size and complexity of the organizations increased and the need of employees possessing specialized skills has increased. Organizations nowadays outsource the work that requires specialized skills.

E.g.: A textile company planning to upgrade its technology should have skilled workers to handle the same.

2.     System

System means the processes of the company. It gives you an idea about how work is done. It helps you to identify loopholes and take preventive action to prevent damage/losses.

E.g.: Risk management processes, HR processes etc.

3.     Structure

Structure refers to the Organization Structure. It aim to make the employees working in an organization understand the authority-responsibility relationship and what each employee is accountable for. It gives the clarity and scope of their role in the organization.

E.g.: Line Structure versus Team Structure.

4.     Staff

Staff refers to the people in the company. The intrinsic quality of the company. It is the talent pool. Nowadays the importance is given to diversity and thinking ability of the staff.

5.     Strategy

Strategy is straight-forward. What is the company doing to gain a competitive advantage? Strategy is dynamic and it should be adaptive.

6.     Style

It means culture of the organization. It is about how we do things in the organization. It includes the informal rules of conduct at the company. The characteristic or the style that the employees working in the company possess.

7.     Shared Values

What is the organization trying to achieve? This question usually doesn’t change overtime and is applicable for a prolonged period of time. The standards may increase. The social missions are added to “Shared Values” as it affects the organization’s reputation.

E.g.: An organization may be profit organized or not profit organized.