error in my VBA (newbie)

[Stephanie S]

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

 

[Ronald Dodge]

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?

 

[Tom Ogilvy]

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)

 

[Stephanie Stowe]

So far, so good.

Thanks!

S

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

ActualTerm