Automate Excel Cell Filling Based on Dropdown Selections Using VBA

Learn how to automatically fill values in an Excel sheet based on user selections from a dropdown list using VBA, and how to implement an easy cancel function.
---
This video is based on the question https://stackoverflow.com/q/75473671/ asked by the user 'Simon.G' ( https://stackoverflow.com/u/21081106/ ) and on the answer https://stackoverflow.com/a/75507690/ provided by the user 'Simon.G' ( https://stackoverflow.com/u/21081106/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Fill a cell when specific value entered in other cell

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Automate Excel Cell Filling Based on Dropdown Selections Using VBA

Excel is a powerful tool that can greatly enhance productivity, especially when using features like dropdown lists for data entry. However, users often find themselves needing to fill in additional related cells automatically based on their selection. In this guide, we’ll address the common problem of automatically filling one cell based on the value selected in another cell, such as filling in column I based on user choices in column B.

The Problem at Hand

Imagine you have a spreadsheet where column B contains dropdown options like “Sourcing,” “Phasing out,” and “History.” You want users to not only choose a value from this dropdown but also provide an associated date in column I. If they decide to cancel their entry, the values should be cleared automatically. This makes for a smoother and more efficient data-entry process.

The Solution: Using VBA in Excel

To achieve the desired functionality, you can use Visual Basic for Applications (VBA), a programming language built into Excel. This allows you to automate tasks and add custom features.

Step-by-Step VBA Code Implementation

Here’s a simple VBA script that accomplishes this task. We’ll go through the code in detail after presenting it:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Code

Worksheet Change Event:
The Worksheet_Change event is triggered whenever a change is made on the worksheet. The Target parameter refers to the cell that was changed.

Check the Column:
The script checks if the change happened in column B (specified by Target.Column = 2).

Input Handling:

If "Sourcing" is selected, it prompts the user to enter an end date for sourcing. This is done using an InputBox.

It checks if the user cancels or leaves the box empty and clears both column B and column I if true.

If a date is provided, it checks if it's in the correct format. If the format is wrong, a message box appears, and both cells will be cleared.

Handling Other Options:
Similar prompts and conditions are set for "Phasing out" and the "History" option, where the current date is inserted automatically.

Clearing Values:
If the user decides to clear their selection in column B, column I is also cleared.

Conclusion

Utilizing VBA in Excel allows for dynamic interactivity that can enhance your workflow significantly. By implementing the code provided above, you can ensure that your spreadsheet not only captures necessary data accurately but also improves user experience by handling cancellations gracefully.

Give it a try in your next Excel project and see how it simplifies your data entry tasks!

Видео Automate Excel Cell Filling Based on Dropdown Selections Using VBA канала vlogize
Fill a cell when specific value entered in other cell, excel, vba