Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Unprotecting a sheet in a function
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Unprotecting a sheet in a function
Resolved · High Priority · Version 2003
Reply Mon 17 May 2010, 10:14Delegate Alex said...
Alex has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Unprotecting a sheet in a function
I have a function that adds validation to a cell when a value is selected from a listbox in another cell.
This works fine when the sheet is unprotected but for some reason it will not work when it is protected.
I have added the unprotect sheet command to the code but it still wont work. When i do this in a Sub it works fine but doesnt work in a function.
Function tester(range1, range2, range3)
activesheet.unprotect
If range1 = "2" Then
range2.Validation.Delete
range2.Validation.Add Type:=xlValidateList, _ Formula1:="=INDIRECT(""F1:F3"")"
End If
End Function
Can someone help me out please?
Thanks
For upcoming training course dates see: Pricing & availability
RE: Unprotecting a sheet in a function
Hi Alex, thanks for your query. This problem has been flagged up elsewhere on the net, particularly with regards to the sudden appearance of the error in Excel 2003. Have a look at this link:
http://www.xtremevbtalk.com/showthread.php?t=98712&pp=20
Follow the discussion to the third page, where there is a solution which may help with what you're trying to do.
Hope this helps,
Anthony
Reply Mon 17 May 2010, 12:10Delegate Alex said...
RE: Unprotecting a sheet in a function
Thanks for the reply.
But i dont see how this helps my problem.
RE: Unprotecting a sheet in a function
Hi Alex. Can you post the error message you are getting?
Anthony
Reply Mon 17 May 2010, 12:24Delegate Alex said...
RE: Unprotecting a sheet in a function
Im not getting any error message. I must be doing something wrong but every time i add validation to a cell using a function it wont work and i asume its because the sheet is protected. When i step through the code it runs fine until i get to the validation line. The sheet will not unprotect itsself when it gets to the activesheet.unprotect line.
RE: Unprotecting a sheet in a function
Alex, double check what you are passing into the Function. At the moment, you're not passing in Ranges such as "A1", it looks like you're passing in values. You've tested If Range1="2", which is a cell value not a range. You then try changing the validation properties of range2, which - if it's a cell value collected as one of the arguments of the function will cause that line to fail. If range2 in your function is "1" then your line of code effectively reads 1.Validation.Delete which will fail.
This probably works in a subroutine because you aren't passing arguments to the subroutine and you are changing the properties of actual Range objects.
I hope this makes sense. Let me know what luck you have.
Anthony
Reply Mon 17 May 2010, 12:54Delegate Alex said...
RE: Unprotecting a sheet in a function
The code i sent you was something i just made up to demonstate my problem.
This is a little project work have set me.
On the sheet there are various drop down lists with validation in them.
The ones im using in this function are these cells: BSEN, CType and OtherBSEN.
I wont send you all the code because there is alot of it but here is a bit of it.
Private Function HiddenDevice(BSEN, CType, OtherBSEN)
Select Case BSEN
Case "88"
CType.Validation.Delete
CType.Validation.Add Type:=xlValidateList, _ Formula1:="=INDIRECT(""DB_DAT!$M$288"")"
End Select
Exit Function
End Function
When the sheet is unprotected it runs through all the code with no problems.
When i protect it it gets to CType.Validation.AddType:=xlValidateList, _ Formula1:="=INDIRECT(""DB_DAT!$M$288"")"
the code just stops with no error messages.
It runs the line before it which is the delete validation.
DB_DAT is another sheet where the data comes from, ive also tried getting data from the same sheet as im working on but that doesnt work either.
|