Setting up list datavalidation in onSelectionChanged event: works in Web vs does NOT work on Win32

[ALDriehuis]

Hi guys,

We would like to add list datavalidation to the cell currently selected in the OnSelectionChanged event. The idea is that based on several factors, a list is created and that list should be used for datavalidation in the current active cell (range). On WEB/Online this is working (there is a slight delay) working On Win32/Desktop this is NOT working. Sometime you see a dropdown icon next to the cell, but is disappears also. Also the key-combination ALT-Down does NOT show the created list.

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: Working on Office on the Web. NOT working on PC Desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 2105 (Build 14025.20308)
• Operating System: Windows 10
• Browser (if using Office on the web): Edge Version 91.0.864.59 (Official build) (64-bit)

Expected behavior

I would see that a list that is composed in the onSelectionChanged event, is uses as a list datavalidation (with inCellDropDown : true)

Current behavior

On web it is working On desktop I sometime see for a very short period a dropdown icon, but it is disappearing again and so not selectable/visible anymore.

Steps to reproduce

name: List Datavalidation not working on desktop description: List Datavalidation not working on desktop host: EXCEL api_set: {} script: content: | run();

async function run() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.onSelectionChanged.add((eventArgs) => {
      return Excel.run((context) => {
        let clickedCell = context.workbook.getActiveCell();
        clickedCell.dataValidation.clear();
        clickedCell.dataValidation.rule = {
          list: {
            inCellDropDown: true,
            source: "a,B,c,D,e,F"
          }
        };
        return context.sync();
      });
    });

    await context.sync();
  });
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
    console.error(error);
  }
}

language: typescript template: content: |

Run

language: html style: content: |- section.samples { margin-top: 20px; }

section.samples .ms-Button, section.setup .ms-Button {
    display: block;
    margin-bottom: 5px;
    margin-left: 20px;
    min-width: 80px;
}

language: css libraries: | https://appsforoffice.microsoft.com/lib/1/hosted/office.js @types/office-js

office-ui-fabric-js@1.4.0/dist/css/fabric.min.css office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

core-js@2.4.1/client/core.min.js @types/core-js

jquery@3.1.1 @types/jquery@3.3.1

Link to live example(s)

Use scriptlab script from above on desktop. Click on any cell in sheet, it should give a dropdown list with: a,B,c,D,e,F

In web this will work, so I expect that this should also work on desktop

Context

We want to go live soon, but this is an important feature because we use list datavalidation a lot. But we started developing in web, were it works. But our users prefer desktop.

Many thanks guys, BTW we love your API!!!! Lou Driehuis

[qinliuMSFT]

Thanks for bringing this to our attention. We could repro it and it has been put on our backlog bug#5243677. Will back to you once we have updates.

[gingerjia]

@ALDriehuis , thanks for reporting the issue. We have found the root cause and working on the fix. To unblock your side first, you can try to comment out the clear API as below:

let clickedCell = context.workbook.getActiveCell(); //clickedCell.dataValidation.clear(); clickedCell.dataValidation.rule = { list: { inCellDropDown: true, source: "a,B,c,D,e,F" } }; return context.sync(); }); });

Thanks again!

[gingerjia]

@ALDriehuis and @lindalu-MSFT , the fix code also has been check-in. When deploy to PROD, I will update the thread to let you know. Thanks!

[ALDriehuis]

Guys, many thanks for looking into and solving this! And so quickly too! We are looking forward to the updates!

Thanks again!

[gingerjia]

Verified at PROD with below build and it works as expected now:

Thanks!