How can I add drop-down list in excel file using matlab

I use actxserver to create excel files using matlab. But I can not find a way to create cells with drop-down list(or any way to restrict the input values). Any one can help me?

Thanks a lot!

[Yan]

Great Answer!

I have solved my problem , thanks a lot.

Actually, I have used :

ex.Selection.Validation.Add('xlValidateList',1,1,'=A1:A7')

to have more than one enum values.

Answer 1

[Friedrich]

Hi,

Create a Macro, look at the source code, look at the Microsoft documention, implement it in MATLAB:

VBA:

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="1;2;3;4"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Microsoft:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.validation.add%28v=office.11%29.aspx#Y342

Combine these and you get the MATLAB part:

ex = actxserver('excel.application');
ex.visible = 1
wb = ex.workbooks.Add

ex.Range('A1').Select

ex.Selection.Validation.Delete;
ex.Selection.Validation.Add('xlValidateList',1,1,'1;2;3;4')

Important: First delete the Validation, otherwise you get an error if there is already a Validation created. And even more important, you have to work with the enum values for the second and third argument. With the enum strings it doesn't work. (I guess they are ordered like in the Microsoft link above, so simply count to get the correct number^^)

[vcmorini]

Hello! This is a little difficult for me!

Why you need to look at VBA and Microsoft documentation and merge it? Where can I find information to study?

Another question, what is these two ones in bold? What do they do?

ex.Selection.Validation.Add('xlValidateList', 1,1,'1;2;3;4')

Thanks!

:)

[Image Analyst]

You can talk to Excel directly with ActiveX. See attached demo.

Answer 2

[Image Analyst]

That's getting a bit advanced. You might try looking over the MSOffice developers resources on their web site, or ask in a newsgroup devoted to Excel programming. It actually has little to do with MATLAB and is pretty much all Excel programming.