Unprotect an excel sheet from inside a function

[Typicalalex1]

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

 

[*shudder*]

A function will simply perform a calculation and not physically perform any action. You will have to call the unprotect command from the sub, then the function and then reprotect the sheet.

 

[Typicalalex1]

Thanks for the reply.

I have created a sub unprotecting the sheet and put a call command in the function to call the procedure. But again this doesnt work. If i run the sub by itself it works but when i run it from the function it does not work.

 

[lenze]

Hi and welcome to the board!!
I believe you are approaching this in the wrong way. A UDF (or any function) is not meant for this. Simply use a simple code, maybe even a Change event. Can't say without knowing more about what you are really trying to do.

lenze

 

[Typicalalex1]

Thanks for your reply.

I have put the code into the worksheet change but no luck.

I need to have some functions on the worksheet, i cant not have them.
I have moved the one that was causing me problems into the worksheet change and it wont work. If i comment out all the functions that relate to this worksheet it works fine, but i need to have functions and the worksheet change.

 

[lenze]

I'm sure we can help you, but you need to provide a LOT more detail as to what you are doing. I don't see what Functions on your worksheet have to do with a Change Event. So, post your code(s) and explain in DETAIL what your require!!!

lenze

 

[Typicalalex1]

Ok let me explain what i need doing.
On my excel sheet a have a number of different listboxes done by validation.
I need to change the validation in one my cells when a value is selected in one of the dropdown lists.

E.G.

When "3036" is selected from the "Device" ddl, S1,S2,S3,S4 and N/V are added to the ddl in the next cell, which is called CType.

here is the code i am using for this, i have taken some of the code out from the Select case.


Private Sub Worksheet_Change(ByVal Target As Range)

Call BSEN

End Sub


Sub BSEN()
Dim Device As Range
Dim CType As Range
Dim OtherDevice As Range

Set Device = Range("K" & (ActiveCell.Row))
Set CType = Range("L" & (ActiveCell.Row))
Set OtherBSEN = Range("S" & (ActiveCell.Row))

ActiveSheet.Unprotect

Select Case Device

Case "1362"

CType.Validation.Delete
CType.Validation.Add Type:=xlValidateList, _ Formula1:="=INDIRECT(""DB_DAT!$O$288"")"


Case "3036"

CType.Validation.Delete
CType.Validation.Add Type:=xlValidateList, _ Formula1:="=INDIRECT(""DB_DAT!$P$288:$P$292"")"

End Select
ActiveSheet.Protect

Exit Sub

End Sub



Here is one of the functions that run at the same time as the worksheet_Change.

TripTime is a protected cell that is the result of the calculation and Iso, Device, iDeltaN and mS are cells that make up the calculation. This works without problems.


Private Function TripTime(Iso, Device, iDeltaN, mS)
Dim MaxTime As Integer
If UCase(InStr(Iso, "RCD")) = True Then
If mS = "" Then
TripTime = ""
Else
GoTo RCDboard
End If
End If
If Device = "" Then
TripTime = ""
Exit Function
End If
If Device = "4293" Or Device = "61009" Then
If iDeltaN = "" Or mS = "" Then
TripTime = ""
Exit Function
End If

RCDboard:
If iDeltaN = "N/V" Then
TripTime = "N/V"
Exit Function
End If
If mS = "N/T" Or mS = "N/V" Then
TripTime = "Over"
Exit Function
End If
If iDeltaN <= 30 Then
Select Case mS
Case "<40"
TripTime = "OK"
Case ">40"
TripTime = "Over"
Case Else
TripTime = "Rating?"
End Select
Exit Function
End If
If iDeltaN > 30 Then
Select Case mS
Case "<200"
TripTime = "OK"
Case ">200"
TripTime = "Over"
Case Else
TripTime = "Rating?"
End Select
Exit Function
End If
End If
End Function



So basically the functions that dont add validation to cells work fine, the only function that adds validation to cells does not work, the function i am talking about is the one i have added to the Worksheet_change at the top of this page.
It works when the sheet is not protected but it needs to work when the sheet is protected.
So what i need doing is to Unprotect the sheet somewhere in the code. I have no problem doing this normally but it seems to conflict with the functions and will not Unprotect.


Thanks alot