Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Run-time Error '1004' Application Defined Or Object Defined Error.

Posted by: Dan Dungan May 23 2019, 03:27 PM

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?


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

Posted by: Dan Dungan May 23 2019, 05:18 PM

I found the answer on

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

Posted by: cheekybuddha May 23 2019, 05:42 PM

Thanks for sharing your solution.


Posted by: Dan Dungan May 23 2019, 06:46 PM

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

Posted by: cheekybuddha May 23 2019, 07:14 PM

wary.gif laugh.gif