How to use ActiveX to make an Excel worksheet EnableSelection be UnlockedCells?

[AlejandroZ]

Hello.

I am trying to make a VI that among other things protects a worksheet and only allows the user to select cells that are not protected.  I have not been successful in doing so, and I have tracked it down to the property node to write the "EnableSelection" property of a worksheet not working as I would expect it to.

I am attaching an image of the block diagram of a VI I made to demonstrate what is not working for me, and I am also attaching the VI I am using.

I think you could use just any Excel file to test it, but it would be better if you have an excel file that has some protected and some unprotected cells, and the worksheet is configured to be protected.

I am using LabVIEW 7.1

Any advice will be greatly appreciated.

Thanks in advance,

Alejandro

[Elsa Fung]

you have to set unlocked cells and protect the sheet.  try the attachment, it'll put a border around B2 - D6 and let you only select them.

if you're not using Office 2003, you might have to go through and relink all the property & invoke nodes.

See-Ming

[AlejandroZ]

Hello.

Thanks for your response.  I was trying something very similar to what you have attached in your message, but I found that it still does not work exactly like I want it to.

If you run the VI you attached, it is true that you can only select the cells in the range you specified and nothing else, but that is ONLY while you have the Excel file open.  When you press stop, and the VI saves the file and finishes, then, if you close the file, and open it again, the EnableSelection property is set back to NoRestrictions!!!

So, if you close the Excel file and open it again, you can select any cell you want, whether it is locked or not.  At least, you can only modify the unlocked ones, but I would prefer to restrict users to only being able to select unlocked cells.

I am starting to think that this might be a bug in Excel, but I am not sure.

Do you see the same behaviour in your computer?

Any further advice or suggestions will be appreciated.

Thanks,

Alejandro

[Elsa Fung]

maybe it's a difference in LabVIEW 7.1 and 8, because it runs totallly fine on mine.  After I press stop, close Excel, and double click to open the excel file, i can only select the cells inside the border... that's really weird...

which Office do you have?  that matters too.. i have Office 2003

See-Ming

Message Edited by Elsa Fung on 05-03-2006 02:55 PM

[AlejandroZ]

Hi.

I tried it on LabVIEW 8, and I get the same behaviour of the Excel file having its EnableSelection property set to NoRestrictions.

I did notice another difference, though.  When I opened the VI you sent in my computer, I had a broken arrow, and it was because of the "protect" invoke node.  When I reselected "Protect" on that same invoke node, I had no broken arrow, but I get the incorrect behaviour of the Excel sheet not being properly locked.  I am attaching an image of the protect invoke node that I get, so that you can see how it is different (much bigger) than the you in the VI you attached.

So, that leads me to think, that perhaps we are using different versions of Excel...

The version I am using is Microsoft Office Excel 2003 11.6355.5408 SP1

Does this differ from the Excel you are using?

Thanks,

Alejandro

[Elsa Fung]

mine says Microsoft Office Excel 2003 (11.6560.6568) SP2, but I also have Office 97 installed on my computer, and usually the older version's functions have less parameters.  So i'm guessing my VI is actually using Excel 97 instead of 2003...

I traced through my VI and realized even though I selected Excel Object 11.0 at the beginning in Open Excel, later on when I used Variant to Data to convert the Sheet to Worksheet, Excel Object 8.0 (Excel 97) was used.  I tried to change it to use Excel Object 11.0, but all I have under Worksheet is Object 8.0...  double check how your version of LabVIEW and Excel handles that.  If Object 8.0 is available, try using that and see.

also, try linking a FALSE to all the Allow----- parameters.  I don't think it matters, but it's worth a try ^_^

good luck!