Excel VBA UserInterFaceOnly problems when adding listbox

mcs26
mcs26 used Ask the Experts™
on
Hi,

I have a worksheet that is password protected so I found that I could use the UserInterFaceOnly property to allow my VBA code to make changes to my sheet whilst keeping the sheet protected.

This works up till a point, I can make changes through my code but it bugs out at one point below. The line .Add xlValidateList ..... is where the bug appears. The error message is Method ‘Add’ of object ‘Validation’ failed. Now when I unprotect the sheet and take out the UserInterFaceOnly property the same code below works perfectly!

       
Set RngListBox = Range("C" & i)
        
        With RngListBox.Validation
            .Add xlValidateList, xlValidAlertInformation, xlBetween, ListWeight
            .InCellDropdown = True
        End With

Open in new window


Any help would be great, thanks

Mark
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011

Commented:
When you protect the sheet, you also need to specify the DrawingObjects:=True argument as well as the UserInterfaceOnly one.
HTH
Rory

Author

Commented:
hi rorya,

thanks for the response. I have run the macro below to protect my worksheet. However I still get the same error message. Is there anything else I am missing?

    ws.Protect Password:="TEST", DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True

Thanks,
Most Valuable Expert 2011
Top Expert 2011
Commented:
My bad - it should actually be DrawingObjects:=False.
I have to confess though that in my tests, it seems to be extremely buggy as to whether it works or not (sometimes it does, sometimes not) so I'd recommend you unprotect, add the validation and reprotect instead, to be honest.

Author

Commented:
Yeah just being thinking myself it may be best to uprotect the worksheet and add a error handler where the sheet will be password protected. Least you worked it out though! Thanks for your help

Mark

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial