ActiveX checkbox to unlock cell and set allow override

[storminasu]

Hello everyone,
I have a worksheet that has a formula in K9, that is a looking up an account number (in k5) to check membership status. I'd like to have k9 locked and protected unless the override checkbox (activeX) is selected. If it's selected, I'd like the worksheet to unprotect, unlock the cell, then reprotect. After it's protected again with the check box selected, the box will allow an override value of Yes or No only. If the override checkbox is unselected subsequently (false), the workbook will unprotect, lock cell again, replace value with formula, then protect.

With the code below (I am very new to VBA with no previous coding experience), I keep getting an "End With" error.

Any help is very much appreciated. Thank you all!

Private Sub WineClubOverride_Click()

Dim rMyRng As Range
 With ActiveSheet.ActiveWorkSheet.Unprotect
Set rMyRng = Range("k9")


Range("K9").Locked = False
rMyRng.Formula = "=IFERROR(IF(VLOOKUP(K5,'Wine Club List'!$K$8:$L$200,2,FALSE)<>0,""Yes"",""No""),""No"")"
Range("K9").Locked = True
With ActiveSheet.ActiveWorkSheet.Protect
End With


If WineClubOverride.Value = True Then ActiveWorkSheet.Unprotect
If WineClubOverride.Value = True Then Selection.Locked = False


If WineClubOverride.Value = True Then rMyRng.Value = rMyRng.Value
If WineClubOverride.Value = True Then ActiveWorkSheet.Protect
End With


End Sub

[StephenR]

You could remove all your With and End With lines as they do not serve any purpose.

[storminasu]

Thank you very much for your quick reply.

After removal:

Private Sub WineClubOverride_Click()

Dim rMyRng As Range
ActiveSheet.ActiveWorkSheet.Unprotect
Set rMyRng = Range("k9")

Range("K9").Locked = False
rMyRng.Formula = "=IFERROR(IF(VLOOKUP(K5,'Wine Club List'!$K$8:$L$200,2,FALSE)<>0,""Yes"",""No""),""No"")"
Range("K9").Locked = True
ActiveSheet.ActiveWorkSheet.Protect


If WineClubOverride.Value = True Then ActiveWorkSheet.Unprotect
If WineClubOverride.Value = True Then Selection.Locked = False

If WineClubOverride.Value = True Then rMyRng.Value = rMyRng.Value
If WineClubOverride.Value = True Then ActiveWorkSheet.Protect

End Sub

I get the following error:
Attachment 620692

[StephenR]

Unfortunately, I can't access the picture - perhaps you could post the text?

I'm a bit confused by what you're trying to do.

If the checkbox is ticked, do you just want the formula removed from K9?

If the checkbox is not ticked, you want to add the formula?

Have I understood correctly?

(Btw ActiveWorksheet is not correct syntax.)

[storminasu]

In my original post I mentioned what I was trying to do and the fact I was very new to this, so is there anything in particular that I didn't make clear enough or I need to elaborate on? I tried to spell out my thought process there, but hopefully the summary below is more succinct

I want a protected workbook, with the option to override a cell that starts out with a locked formula, but if the checkbox is selected, the worksheet unprotects, unlocks cell and allows a value to be entered. If the checkbox is set to false afterwards, the process reverses, ending with a protected and locked formula.

I had the override portion working, but when I tried to add protection, it began to give errors.

After removing the End withs, etc. in your first post, the error was as follows:
Run-time error '438':
Object doesn't support this property or method

[Winon]

Hello storminasu,

If everything fails, check the manual!

Please attach a sample workbook (not a picture or pasted copy).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Regards.

[storminasu]

Hello storminasu,

If everything fails, check the manual!

Please attach a sample workbook (not a picture or pasted copy).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Regards.

Did the attachment actually work?

Did it clear up my question?

[storminasu]

Ok, thank you. I appreciate both of you helping very much. I have attached a workbook now, I hope it helps.