how to add a drop down menu in excel 2016

Mark the reply as an answer if you find it is helpful. I'm glad to help and follow up your reply.īTW, please tell me your build number for Excel and the language you are using. I suggest you check if you can reproduce by manually and please provide your steps to me. As you said saved and reopened, saved and reopened, but I still didn't reproduce thisĬould you tell me did this issue is related to Code or just an Excel behavior problem? My knowledge about code is limited so I tried to create drop down lists manually, create drop down list for Sheet1 and Sheet2 A1:G20 from external Worksheet. When I tried to use the code you provided I got the error: I can delete them with VBA (delete all shapes) but that causes that the data validation did not work anymore.īased on your description, I tested steps you provided and tried to reproduce this problem, but I didn't get the result as yours, I cannot reproduce your problem. I was not able to find a way to remove this drop down fragments. Seems that cell B1 has got a fragment drop down button from an old from control during the save process.Įvery time I select a different cell in a different sheet, save, close and reopen the file a new drop down fragment is in the sheet. With Sheets(2).Range('A1:G20').ValidationĬlick the register tab of the 1st sheet, (cells A1 is selected), save, close and reopen the file.Ĭlick the register tab of the 2nd sheet, select cell A1 and you should see this: Add Type:=xlValidateList, Formula1:=R.Address(External:=True) With Sheets(1).Range('A1:G20').Validation

Run this macro to create a new file with 3 sheets, the 1st and 2nd has some cells with a data validation list, nothing unusual: Sub Makro1() A user reports in a German forum from an usual behavior in Excel 365, I was able to reproduce the behavior in Excel 2016:

How to add a drop down menu in excel 2016