Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in my VBA (newbie)
I do not normally work in Excel. We (the IT dept at my company) have an
Excel workbook that we inherited from... somewhere. Someone asked me to do one simple thing. I did it. The problem is I did it on a protected sheet. While working on the functionality they wanted, I simply unprotected the sheet so I could change it. And now I am trying to get it to work with the sheet protected. I have 2 locked cells, that need to be locked, which is why I need to protect the sheet. OK, so I have a button which does this: Range("D14") = GetTotalInterest(Range("D6"), Range("D10"), Range("D12"), Range("D8")) Where GetTotalInterest is a function which returns a double. D14 is one of the "locked" cells because the folks are not supposed to be able to type into it. So I need to protect the sheet, which will cause me not to be able to do Range("D14") = Fine. So I try this instead: Sheets("Variable").Unprotect "pwd" Range("D14") = GetTotalInterest(Range("D6"), Range("D10"), Range("D12"), Range("D8")) Sheets("Variable").Protect "pwd" Where "pwd" is a not terribly necessary password. The person who created the sheet, protected with a password. So ... I thought I would continue that trend, even though it is not terribly important to do so. I get the error: Unprotect method of Worksheet class failed (number 1004). I do not know why. I am supplying the correct password in the correct case. The help on the error message is not helpful. Anyone have any ideas what I am doing wrong? Included here is the code for GetTotalInterest. It has an unprotect and protect in it. So I note it in case it helps. Function GetTotalInterest(LoanAmt As Double, PayAmt As Double, ActualTerm As Double, InterestRate As Double) As Double Dim totalinterest As Variant Dim e10, e11, e12, e13, e17 ' Application.ScreenUpdating = False 'modUtilities.cmd_Clear_Consolidation Sheets("Consolidation").Select With Sheets("Consolidation") .Unprotect ("estimate") e10 = .Range("E10").Formula e11 = .Range("E11").Formula e12 = .Range("E12").Formula e13 = .Range("E13").Formula e17 = .Range("E17").Formula .Range("E12") = LoanAmt ' loan amount .Range("E11") = LoanAmt .Range("E13") = PayAmt ' payment amount .Range("E17") = ActualTerm ' actual term .Range("E10") = InterestRate ' interest rate totalinterest = .Range("E15") .Range("E10").Formula = e10 .Range("E11").Formula = e11 .Range("E12").Formula = e12 .Range("E13").Formula = e13 .Range("E17").Formula = e17 .Protect ("estimate") End With GetTotalInterest = totalinterest modUtilities.cmd_Clear_Consolidation Sheets("Variable").Select Application.ScreenUpdating = True End Function THANKS! S |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in my VBA (newbie)
Though I don't know why sheets would not work as it applies to both Charts
and Worksheets, but what you may want to try instead is the following code: Thisworkbook.Worksheets("Variable").Unprotect("pwd ") and do the same for your Protect line. One other possible solution which would probably be even better cause then you won't have to deal with this, and you can leave the cell protected at all times, why don't you have the function declared as a Public Function, then put in the spreadsheet as a function like the following in cell "D14": =GetTotalInterest($D$6,$D$10,$D$12,$D$8) VBA side of Excel of what the Functions First line should look something like the following: Public Function GetTotalInterest(Principle as Double,Rate as Double, Payments as Double, Periods as Long) as Double If you noticed, I have each of the arguments in the function line including the function itself declared to a particular data type. I'm not sure if that's the order your arguments is in, but I'm assuming those are the arguments you are using to determine total interest, though there's also 2 other arguments that I also know of that would impact how much interest one pays. Interest Rule: Simple - The interest you pay based on current balance. 7/8 - The interest you pay based on a fixed schedule regardless how much extra you may pay Of the 2 rules, the 7/8 rules looks like it's the same interest you pay at the time of setup as the Simple rule, but if payments are paid in advance of due dates or in greater amounts than what it was setup as, then under the simple rule, you pay less as the simple rule takes that into account while the 7/8 rule doesn't take that into account, thus you end up paying more interest under the 7/8 rule than you do under the simple rule in that scenerio. When does interest accumulate? At the Beginning or at the End of the Period? -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Stephanie S" wrote in message ... I do not normally work in Excel. We (the IT dept at my company) have an Excel workbook that we inherited from... somewhere. Someone asked me to do one simple thing. I did it. The problem is I did it on a protected sheet. While working on the functionality they wanted, I simply unprotected the sheet so I could change it. And now I am trying to get it to work with the sheet protected. I have 2 locked cells, that need to be locked, which is why I need to protect the sheet. OK, so I have a button which does this: Range("D14") = GetTotalInterest(Range("D6"), Range("D10"), Range("D12"), Range("D8")) Where GetTotalInterest is a function which returns a double. D14 is one of the "locked" cells because the folks are not supposed to be able to type into it. So I need to protect the sheet, which will cause me not to be able to do Range("D14") = Fine. So I try this instead: Sheets("Variable").Unprotect "pwd" Range("D14") = GetTotalInterest(Range("D6"), Range("D10"), Range("D12"), Range("D8")) Sheets("Variable").Protect "pwd" Where "pwd" is a not terribly necessary password. The person who created the sheet, protected with a password. So ... I thought I would continue that trend, even though it is not terribly important to do so. I get the error: Unprotect method of Worksheet class failed (number 1004). I do not know why. I am supplying the correct password in the correct case. The help on the error message is not helpful. Anyone have any ideas what I am doing wrong? Included here is the code for GetTotalInterest. It has an unprotect and protect in it. So I note it in case it helps. Function GetTotalInterest(LoanAmt As Double, PayAmt As Double, ActualTerm As Double, InterestRate As Double) As Double Dim totalinterest As Variant Dim e10, e11, e12, e13, e17 ' Application.ScreenUpdating = False 'modUtilities.cmd_Clear_Consolidation Sheets("Consolidation").Select With Sheets("Consolidation") .Unprotect ("estimate") e10 = .Range("E10").Formula e11 = .Range("E11").Formula e12 = .Range("E12").Formula e13 = .Range("E13").Formula e17 = .Range("E17").Formula .Range("E12") = LoanAmt ' loan amount .Range("E11") = LoanAmt .Range("E13") = PayAmt ' payment amount .Range("E17") = ActualTerm ' actual term .Range("E10") = InterestRate ' interest rate totalinterest = .Range("E15") .Range("E10").Formula = e10 .Range("E11").Formula = e11 .Range("E12").Formula = e12 .Range("E13").Formula = e13 .Range("E17").Formula = e17 .Protect ("estimate") End With GetTotalInterest = totalinterest modUtilities.cmd_Clear_Consolidation Sheets("Variable").Select Application.ScreenUpdating = True End Function THANKS! S |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in my VBA (newbie)
What version of Excel. Is this being called from a commandbutton.
If you are using Excel 97 and you are calling from a commandbutton, try changing the TakeFocusOnClick property of the Commandbutton to false. If not, make sure your code is in a general module and not in a sheet module. anyway, you can use many worksheet functions directly with VBA without putting data in a cell and using the formula in the cell. for example Application.FV(args) -- Regards, Tom Ogilvy Stephanie S wrote in message ... I do not normally work in Excel. We (the IT dept at my company) have an Excel workbook that we inherited from... somewhere. Someone asked me to do one simple thing. I did it. The problem is I did it on a protected sheet. While working on the functionality they wanted, I simply unprotected the sheet so I could change it. And now I am trying to get it to work with the sheet protected. I have 2 locked cells, that need to be locked, which is why I need to protect the sheet. OK, so I have a button which does this: Range("D14") = GetTotalInterest(Range("D6"), Range("D10"), Range("D12"), Range("D8")) Where GetTotalInterest is a function which returns a double. D14 is one of the "locked" cells because the folks are not supposed to be able to type into it. So I need to protect the sheet, which will cause me not to be able to do Range("D14") = Fine. So I try this instead: Sheets("Variable").Unprotect "pwd" Range("D14") = GetTotalInterest(Range("D6"), Range("D10"), Range("D12"), Range("D8")) Sheets("Variable").Protect "pwd" Where "pwd" is a not terribly necessary password. The person who created the sheet, protected with a password. So ... I thought I would continue that trend, even though it is not terribly important to do so. I get the error: Unprotect method of Worksheet class failed (number 1004). I do not know why. I am supplying the correct password in the correct case. The help on the error message is not helpful. Anyone have any ideas what I am doing wrong? Included here is the code for GetTotalInterest. It has an unprotect and protect in it. So I note it in case it helps. Function GetTotalInterest(LoanAmt As Double, PayAmt As Double, ActualTerm As Double, InterestRate As Double) As Double Dim totalinterest As Variant Dim e10, e11, e12, e13, e17 ' Application.ScreenUpdating = False 'modUtilities.cmd_Clear_Consolidation Sheets("Consolidation").Select With Sheets("Consolidation") .Unprotect ("estimate") e10 = .Range("E10").Formula e11 = .Range("E11").Formula e12 = .Range("E12").Formula e13 = .Range("E13").Formula e17 = .Range("E17").Formula .Range("E12") = LoanAmt ' loan amount .Range("E11") = LoanAmt .Range("E13") = PayAmt ' payment amount .Range("E17") = ActualTerm ' actual term .Range("E10") = InterestRate ' interest rate totalinterest = .Range("E15") .Range("E10").Formula = e10 .Range("E11").Formula = e11 .Range("E12").Formula = e12 .Range("E13").Formula = e13 .Range("E17").Formula = e17 .Protect ("estimate") End With GetTotalInterest = totalinterest modUtilities.cmd_Clear_Consolidation Sheets("Variable").Select Application.ScreenUpdating = True End Function THANKS! S |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in my VBA (newbie)
So far, so good.
Thanks! S "Tom Ogilvy" wrote in message ... What version of Excel. Is this being called from a commandbutton. If you are using Excel 97 and you are calling from a commandbutton, try changing the TakeFocusOnClick property of the Commandbutton to false. If not, make sure your code is in a general module and not in a sheet module. anyway, you can use many worksheet functions directly with VBA without putting data in a cell and using the formula in the cell. for example Application.FV(args) -- Regards, Tom Ogilvy Stephanie S wrote in message ... I do not normally work in Excel. We (the IT dept at my company) have an Excel workbook that we inherited from... somewhere. Someone asked me to do one simple thing. I did it. The problem is I did it on a protected sheet. While working on the functionality they wanted, I simply unprotected the sheet so I could change it. And now I am trying to get it to work with the sheet protected. I have 2 locked cells, that need to be locked, which is why I need to protect the sheet. OK, so I have a button which does this: Range("D14") = GetTotalInterest(Range("D6"), Range("D10"), Range("D12"), Range("D8")) Where GetTotalInterest is a function which returns a double. D14 is one of the "locked" cells because the folks are not supposed to be able to type into it. So I need to protect the sheet, which will cause me not to be able to do Range("D14") = Fine. So I try this instead: Sheets("Variable").Unprotect "pwd" Range("D14") = GetTotalInterest(Range("D6"), Range("D10"), Range("D12"), Range("D8")) Sheets("Variable").Protect "pwd" Where "pwd" is a not terribly necessary password. The person who created the sheet, protected with a password. So ... I thought I would continue that trend, even though it is not terribly important to do so. I get the error: Unprotect method of Worksheet class failed (number 1004). I do not know why. I am supplying the correct password in the correct case. The help on the error message is not helpful. Anyone have any ideas what I am doing wrong? Included here is the code for GetTotalInterest. It has an unprotect and protect in it. So I note it in case it helps. Function GetTotalInterest(LoanAmt As Double, PayAmt As Double, ActualTerm As Double, InterestRate As Double) As Double Dim totalinterest As Variant Dim e10, e11, e12, e13, e17 ' Application.ScreenUpdating = False 'modUtilities.cmd_Clear_Consolidation Sheets("Consolidation").Select With Sheets("Consolidation") .Unprotect ("estimate") e10 = .Range("E10").Formula e11 = .Range("E11").Formula e12 = .Range("E12").Formula e13 = .Range("E13").Formula e17 = .Range("E17").Formula .Range("E12") = LoanAmt ' loan amount .Range("E11") = LoanAmt .Range("E13") = PayAmt ' payment amount .Range("E17") = ActualTerm ' actual term .Range("E10") = InterestRate ' interest rate totalinterest = .Range("E15") .Range("E10").Formula = e10 .Range("E11").Formula = e11 .Range("E12").Formula = e12 .Range("E13").Formula = e13 .Range("E17").Formula = e17 .Protect ("estimate") End With GetTotalInterest = totalinterest modUtilities.cmd_Clear_Consolidation Sheets("Variable").Select Application.ScreenUpdating = True End Function THANKS! S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba newbie need help. | Excel Discussion (Misc queries) | |||
newbie ? | New Users to Excel | |||
Real Newbie newbie question | New Users to Excel | |||
newbie needs help | Charts and Charting in Excel | |||
Compile error: Ambigious name detected: Worksheet_Change **NEWBIE** | Excel Discussion (Misc queries) |