MS word drop down control with Excel data

There are lots of things you can do with MS Excel and MS word. Word and Excel can be connected easily to build sophisticated use cases. Mail merge is one of the great tool, where you can leverage the data power of excel and reporting feature of the MS Word. With a bit of VBA knowledge, you can extend the mail merge for usages.

Below I explain how to use the Excel data to populate the DropDown control in the MS word. And it is dynamic, when the data in Excel got changed, the items in the drop-down will also be changed. 

First, I have this sample data in Excel.

I want this data to be displayed in a dropdown control in the MS Word. For this I am making use of this macro or VBA code.

Private Sub ld_Click()

Dim objExcel As New Excel.Application

Dim objWB As Excel.Workbook

Set objWB = objExcel.Workbooks.Open("C:\Users\krsanjee\Documents\krishtalk.com\book1.xlsx")

'Me.drop.RemoveItem (0)

Me.drop.Clear

total_rows = Cells(Rows.Count, 1).End(xlUp).Row

total_columns = Cells(1, Columns.Count).End(xlToLeft).Column

 With Me.drop

    For r = 2 To total_rows Step 1

        .AddItem objWB.Sheets(1).Cells(r, 1)

    Next r

    End With

objWB.Close

End Sub 

Note the Sub ld_Click()  Subroutine, it has been assigned to a button control. Clicking on this button you can load the dropdown control with the data from Excel

Compare the value in the first column in the Excel and the values in the MS Word dropdown. I am using the first column that is a serial number because I needed the Sl No. to manipulate the data to extract other columns from the Excel for reporting purpose. 

The code has been changed slightly to get the EMP Name in the dropdown. 

 With Me.drop

    For r = 2 To total_rows Step 1

        .AddItem objWB.Sheets(1).Cells(r, 2)

    Next r

 End With

In the .AddItem objWB.Sheets(1).Cells(r, 2) line I have changed the column number to 2 so that the EMP Name will be populated in the dropdown.

Watch the step by step tutorial in my YouTube channel 

VirtualBox and Hyper V conflicts on Windows 10

If you are using Windows 10 operating system and want to use virtualization on it, then you have 2 options.

1. Using Virtual Box
2. Using Hyper V

This post is not about how to use them but instead, what are the issues you will encounter if you have them together.

One scenario is, you have VirtualBox installed and working fine. Then you realize you want to use a docker on your Windows machine. The VirtualBox working fine until will start comparing about virtualization and acceleration. After installing the docker on your windows, all your VMs on the VirtualBox cannot start. It complains about the availability of virtualization.

"The hardware virtualization is enabled in the Acceleration section of the system although it is not supported from the host system. It should be disabled in order to start the visual system"

The error message not straight forward to understand. As you know virtualization is required for docker or VirtualBox to work. First thing you will do now is, check whether your system is virtualization enabled or not. There are multiple options to check if your system is enabled with virtualization or not. Check the BIOS settings, check the CPU section of the Task manager. 

Now you start worrying more about the possible reason for VirtualBox failure. The reason for this failure is having VirtualBox and Docker running on your system. 

If you install docker in your Windows, it will enable the Hyper V and install a virtual machine for docker to work. This is the requirement for docker on Windows. Both VirtualBox and Hyper V cannot run together. However, the later versions of docker might come up with some capability to have them together. 

To solve this problem, disable the Hyper V from optional features on your Windows 10. Remember, disabling the Hyper V will stop your docker. Only if you are not interested in docker and only need VirtualBox to run, then do this. 

Open the optional feature dialog box by entering the optionalfeatures in the run box

Then uncheck the box Hyper-V, it requires a restart. After a restart, your VirtualBox should work as before. 

Error messages:

1. Invalid settings detected. 

2. System acceleration page:

The hardware virtualization is enabled in the Acceleration section of the System page although it is not supported by the host machine. It should be disabled in order to start the virtual system.

3. General Basic page:

The virtual machine operating hint is set to 64-bit type. 64-bit guest system require hardware virtualization, so this will be enabled automatically if you confirm the changes. 

4. Raw-mode is unavailable courtesy of Hyper-V. (VERR_SUPDRV_NO_RAW_MODE_HYPER_V_ROOT

Failed to open a session for the virtual machine ubantu.
Raw-mode is unavailable courtesy of Hyper-V. (VERR_SUPDRV_NO_RAW_MODE_HYPER_V_ROOT).

Result Code:	E_FAIL (0x80004005)
Component:	ConsoleWrap
Interface:	IConsole {872da645-4a9b-1727-bee2-5585105b9eed}

Excel conditional formatting based on multiple cell values

1. Condition on a single cell

To apply conditional formatting based on the values of multiple cells. In other words, format a cell where formatting cell is different and conditional cell is different.

Click on the cell where you want to apply for formatting

Go to Home on the Ribbon, then select Conditional Formatting.

Then select the last option, "Use a formula to select which cell to format"

Now, in the formula cell write the formula for your condition

=$C$6=10

2. Conditions on multiple cells

What if you have to consider multiple cells for the condition?

Condition cell 1: Value equal to 10
Condition cell 2: Value equal to "Krishna" 

You need to build a formula with multiple conditions using AND operator. Based on your requirement, you can also use OR operator. Not just AND and OR, you can use any of the available functions in the MS Excel. The overall result should be TRUE or FALSE. It does not matter how complex your formula is, what does matter is the return value.

Now, build this formula and paste it in the conditional formatting formula bar.

=AND($B$6=10, $C$6="Krishna")