VBA to lock cell and insert formula

[cjkohler88]

I am having some trouble getting my VBA to work. I am trying to unprotect a worksheet, set a cell to lock, insert a formula if the cell ="us" and then reprotect the cell. if the cell is not equal to "US" than I want to clear the cell. I can get the code to unlock, set the protection or unprotect, and then relock using the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
If [c4] = "US" Then
ActiveSheet.Unprotect ("eXpense")
[c7].Locked = True
ActiveSheet.Protect ("eXpense")
'Remove locked property if B3's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("eXpense")
[c7].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("eXpense")
End If
End Sub

how do i get it to either insert a function or clear the cell. I have tried a lot of variations and it get an error or a loop every time

 

[lenze]

Hi and welcome to the board!!!
I don't really follow what you are asking! Explain in staright talk what you are doing and what you want to happen. Like
If I enter "US" in cell C4, then lock C4 etc.
whatever
I don't see where C7 and B3 are coming into play, so we need more info.
You are using a Change_Event. What cells or range do you want it to work with?
lenze

 

[cjkohler88]

sorry, B3 was a mistake, it should be C4. here is what I want to do. In cell C4 I have a drop down box that includes a bunch of countries. If soemone picks US I want it to unprotect the sheet, insert the function "=US_Mile_Rate" into c7, lock the cell, and then reprotect the sheet. if they choose any other option, I want it to unprotect the sheet, clear the value in c7, and reprotect the sheet. right now all i can get it to do is unprotect the sheet, set the cell as locked or unlocked and reprotect the worksheet. does that help?

 

[cjkohler88]

here is my updated code:

Private Sub Worksheet_Change(ByVal Target As Range)
If [c4] = "US" Then
ActiveSheet.Unprotect ("eXpense")
[c7].Locked = True
ActiveSheet.Protect ("eXpense")
Else
ActiveSheet.Unprotect ("eXpense")
[c7].Locked = False
ActiveSheet.Protect ("eXpense")
End If
End Sub

 

[lenze]

What is "=US_Mile_Rate"
A formula? A Named Range? Text?

lenze

 

[cjkohler88]

sorry, it is a named range.

 

[lenze]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$4" Then Exit Sub
ActiveSheet.Unprotect "eXpense"
If Target <> "US" Then
    Range("$C$7").ClearContents
Else
    Range("$C$7") = Range("US_Mile_Rate")
    Range("$A$7").Locked = True
End If
ActiveSheet.Protect "eXpense"
End Sub

I assume the Named range is on the same sheet. If not you may need to reference the sheet it is on.
lenze

 

[cjkohler88]

thanks, that helps