Hi UA,

I have a password protected sheet for calculating prices. I use vlookup to pull prices from other sheets. These prices are then used to calculate the total price.

I had a checkbox on the spreadsheet the unlocks the a cell so the user could change the percent of markup. It works.

Then the user asked for another checkbox so she can change the setup price.

I added it to the same code as the first, but the setup if statement errors when i try to paste the formula to the cell.

I running this sub because when the user's entering the updated value wipes out the formula. So when she completes the quote this sub puts the formula back in the cell and protects the sheet for the next quote.

How can I add another checkbox without getting the error?

Thanks

CODE

Sub MarkupProtect()

'Some quotes need to have a different markup.

'Added checkbox to unlock cell B75 for entering the new amount.

'This checks to see if the cell is unlocked, pastes the formula

'and locks the cell and protects the sheet. The same with

'the reset button: cmdReset

If Range("B75").Locked = False Then

ActiveSheet.Unprotect Password:="pricing"

Range("B75").Formula = "=IF(ISERROR(VLOOKUP($E$2,tblFormulas!$A:$Z,25,FALSE)),0,IF(VLOOKUP($E$2,tblFormulas!$A:$Z,25,FALSE)=0,0,VLOOKUP($E$2,tblFormulas!$A:$Z,25,FALSE)))"

Range("B75").Locked = True

ckbReviseMarkup.Value = False

End If

'added same functionality to B80 to allow editing setup.

If Range("B81").Locked = False Then

ActiveSheet.Unprotect Password:="pricing"

Range("B81").Formula = "=IF(ISERROR(VLOOKUP($E$2,tblFormulas!$A:$Z,24,FALSE)),"",IF(VLOOKUP($E$2,tblFormulas!$A:$Z,24,FALSE)=0,"",VLOOKUP($E$2,tblFormulas!$A:$Z,24,FALSE)))"

Range("B81").Locked = True

ckbReviseSetup.Value = False

ActiveSheet.Protect Password:="pricing"

End If

End Sub

'Some quotes need to have a different markup.

'Added checkbox to unlock cell B75 for entering the new amount.

'This checks to see if the cell is unlocked, pastes the formula

'and locks the cell and protects the sheet. The same with

'the reset button: cmdReset

If Range("B75").Locked = False Then

ActiveSheet.Unprotect Password:="pricing"

Range("B75").Formula = "=IF(ISERROR(VLOOKUP($E$2,tblFormulas!$A:$Z,25,FALSE)),0,IF(VLOOKUP($E$2,tblFormulas!$A:$Z,25,FALSE)=0,0,VLOOKUP($E$2,tblFormulas!$A:$Z,25,FALSE)))"

Range("B75").Locked = True

ckbReviseMarkup.Value = False

End If

'added same functionality to B80 to allow editing setup.

If Range("B81").Locked = False Then

ActiveSheet.Unprotect Password:="pricing"

Range("B81").Formula = "=IF(ISERROR(VLOOKUP($E$2,tblFormulas!$A:$Z,24,FALSE)),"",IF(VLOOKUP($E$2,tblFormulas!$A:$Z,24,FALSE)=0,"",VLOOKUP($E$2,tblFormulas!$A:$Z,24,FALSE)))"

Range("B81").Locked = True

ckbReviseSetup.Value = False

ActiveSheet.Protect Password:="pricing"

End If

End Sub

I found the answer on ozgrid.com.

When a formula has "" as criteria .formula needs """"

Thanks for sharing your solution.

https://www.google.com/search?q=ultima+thule&rlz=1C1GGRV_enUS750US750&source=lnms&tbm=isch&sa=X&ved=0ahUKEwjt4pbK6rLiAhVmwlQKHbuRAxUQ_AUIDygC&biw=923&bih=639#imgrc=VbjRLUlLARvNxM:

Every time I see a picture of this, I think of you.