UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Run-time Error '1004' Application Defined Or Object Defined Error., Office 2007    
 
   
Dan Dungan
post May 23 2019, 03:27 PM
Post#1



Posts: 319
Joined: 20-July 10
From: chatsworth, ca


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

This post has been edited by Dan Dungan: May 23 2019, 03:46 PM

--------------------
Dan
Go to the top of the page
 
 
Start new topic
Replies
Dan Dungan
post May 23 2019, 05:18 PM
Post#2



Posts: 319
Joined: 20-July 10
From: chatsworth, ca


I found the answer on ozgrid.com.

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

--------------------
Dan
Go to the top of the page
 
cheekybuddha
post May 23 2019, 05:42 PM
Post#3


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Thanks for sharing your solution.

thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
Dan Dungan
post May 23 2019, 06:46 PM
Post#4



Posts: 319
Joined: 20-July 10
From: chatsworth, ca


Ultima Thule

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

--------------------
Dan
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 11:46 AM