Excel 2000: AutoSum or Insert Row in Protected Sheet using VB macros 
Author Message
 Excel 2000: AutoSum or Insert Row in Protected Sheet using VB macros

I have an invoice sheet in Excel 2000 that uses a Lookup from an another
sheet that contains values, I have locked only the cells containing the
formulas so that the users can't change them, then protected the invoice
sheet.

My problem is that I don't know how many items are going to be included on
the invoice sheet so I can't put a total at the bottom.

I have been trying to approach this in two different ways using Visual Basic
macros,

Firstly by moving to a fixed point at the top of the sheet above the costs,
then using "Ctrl+Down" to reach the last row with data, then a simple Down
followed by an InsertRow then a selection of cells and a Ctrl+D to duplicate
the formulas.

This means that I can place a SUM formula two rows down from the last data
row and this will automatically update when I insert the rows. This works
great umtil I protect the sheet at which point InsertRow no longer works.

The second method involved trying to create my own AutoSum function, again
using a macro to Ctrl+Down to the last data row and then moving one row down
and inserting a formula="=sum(value1, value2)". This works great when using
either absolute or non-relative terms, but not when you try to combine them.
The first value, value1, is always "F7", however the seond value is always
[R-1C0] from the active cell and both FORMULA and FORMUALR1C1 doesn't allow
mixed terms. I've seen the CONVERTFORMULA method but again that only seems
to deal with converting one whole set of terms into another.

Even if I can covert [R-1C0] into an absolute term I still can't see a way
of entering it into the cell as neither the FORMULA or the SUM method seem
to allow variables of the FORMULA="SUM(F7:value2)" type.

What is especially annoying, is that in Word I could simply use an
"=SUM(ABOVE)" and all my problems would be solved.

Does anyone out there know a solution to this deceptively simple task.

Thanks in advance

Phil Cook



Tue, 27 Jul 2004 00:09:19 GMT  
 Excel 2000: AutoSum or Insert Row in Protected Sheet using VB macros
As is always the way, after posting this I returned to the problem and found
a simple solution, for anybody having the same problem here is the code

BEGIN CODE
Dim endofvaluesrow As String
Dim StringValue1 As String
Dim StringValue2 As String
Dim StringValue3 As String
Dim StringValue4 As String

Range("F1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
endofvaluesrow = Format(ActiveCell.Row)
ActiveCell.Offset(2, 0).Range("A1").Select

StringValue2 = "F" + endofvaluesrow
StringValue1 = "=SUM(F7:"
StringValue3 = ")"
StringValue4 = StringValue1 + StringValue2 + StringValue3
ActiveCell.Formula = StringValue4
END CODE

Not very elegant, but it works even when the sheet is protected, though I
still wish I could just access the AutoSum..
Remember I know that the column is always F and the first cell is always F7
so I could just plonk these in.

Phil


Quote:
> I have an invoice sheet in Excel 2000 that uses a Lookup from an another
> sheet that contains values, I have locked only the cells containing the
> formulas so that the users can't change them, then protected the invoice
> sheet.

> My problem is that I don't know how many items are going to be included on
> the invoice sheet so I can't put a total at the bottom.

> I have been trying to approach this in two different ways using Visual
Basic
> macros,

> Firstly by moving to a fixed point at the top of the sheet above the
costs,
> then using "Ctrl+Down" to reach the last row with data, then a simple Down
> followed by an InsertRow then a selection of cells and a Ctrl+D to
duplicate
> the formulas.

> This means that I can place a SUM formula two rows down from the last data
> row and this will automatically update when I insert the rows. This works
> great umtil I protect the sheet at which point InsertRow no longer works.

> The second method involved trying to create my own AutoSum function, again
> using a macro to Ctrl+Down to the last data row and then moving one row
down
> and inserting a formula="=sum(value1, value2)". This works great when
using
> either absolute or non-relative terms, but not when you try to combine
them.
> The first value, value1, is always "F7", however the seond value is always
> [R-1C0] from the active cell and both FORMULA and FORMUALR1C1 doesn't
allow
> mixed terms. I've seen the CONVERTFORMULA method but again that only seems
> to deal with converting one whole set of terms into another.

> Even if I can covert [R-1C0] into an absolute term I still can't see a way
> of entering it into the cell as neither the FORMULA or the SUM method seem
> to allow variables of the FORMULA="SUM(F7:value2)" type.

> What is especially annoying, is that in Word I could simply use an
> "=SUM(ABOVE)" and all my problems would be solved.

> Does anyone out there know a solution to this deceptively simple task.

> Thanks in advance

> Phil Cook



Tue, 27 Jul 2004 00:55:26 GMT  
 Excel 2000: AutoSum or Insert Row in Protected Sheet using VB macros
You could try this too:

    Dim myrng As Range
    Set myrng = Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)

    myrng.FormulaR1C1 = "=sum(r7c:r[-1]c)"
   'or
    myrng.Formula = "=sum($f$7:offset(" & myrng.Address(False, False) _
                        & ",-1,0,1,1))"

the second formula looks funnier, but reacts to insertion of rows nicer.  It
essential says to go from F7 to one above the cell that's getting the formula.

If you've ever used a formula like:  =sum(F7:f22) in cell F23, then inserted a
new row 23, your formula doesn't adjust to include that row atomatically to
something like:  =sum(f7:f23)

but if you used this formula:
        =sum(f7:offset(f22,-1,0,1,1))  <--f7 to one cell above F22
and do the same insert row, then formula adjusts to
        =SUM(F7:OFFSET(F23,-1,0,1,1))  <--f7 to one cell above f23

which can be very useful.

Quote:

> As is always the way, after posting this I returned to the problem and found
> a simple solution, for anybody having the same problem here is the code

> BEGIN CODE
> Dim endofvaluesrow As String
> Dim StringValue1 As String
> Dim StringValue2 As String
> Dim StringValue3 As String
> Dim StringValue4 As String

> Range("F1").Select
> Selection.End(xlDown).Select
> Selection.End(xlDown).Select
> endofvaluesrow = Format(ActiveCell.Row)
> ActiveCell.Offset(2, 0).Range("A1").Select

> StringValue2 = "F" + endofvaluesrow
> StringValue1 = "=SUM(F7:"
> StringValue3 = ")"
> StringValue4 = StringValue1 + StringValue2 + StringValue3
> ActiveCell.Formula = StringValue4
> END CODE

> Not very elegant, but it works even when the sheet is protected, though I
> still wish I could just access the AutoSum..
> Remember I know that the column is always F and the first cell is always F7
> so I could just plonk these in.

> Phil



> > I have an invoice sheet in Excel 2000 that uses a Lookup from an another
> > sheet that contains values, I have locked only the cells containing the
> > formulas so that the users can't change them, then protected the invoice
> > sheet.

> > My problem is that I don't know how many items are going to be included on
> > the invoice sheet so I can't put a total at the bottom.

> > I have been trying to approach this in two different ways using Visual
> Basic
> > macros,

> > Firstly by moving to a fixed point at the top of the sheet above the
> costs,
> > then using "Ctrl+Down" to reach the last row with data, then a simple Down
> > followed by an InsertRow then a selection of cells and a Ctrl+D to
> duplicate
> > the formulas.

> > This means that I can place a SUM formula two rows down from the last data
> > row and this will automatically update when I insert the rows. This works
> > great umtil I protect the sheet at which point InsertRow no longer works.

> > The second method involved trying to create my own AutoSum function, again
> > using a macro to Ctrl+Down to the last data row and then moving one row
> down
> > and inserting a formula="=sum(value1, value2)". This works great when
> using
> > either absolute or non-relative terms, but not when you try to combine
> them.
> > The first value, value1, is always "F7", however the seond value is always
> > [R-1C0] from the active cell and both FORMULA and FORMUALR1C1 doesn't
> allow
> > mixed terms. I've seen the CONVERTFORMULA method but again that only seems
> > to deal with converting one whole set of terms into another.

> > Even if I can covert [R-1C0] into an absolute term I still can't see a way
> > of entering it into the cell as neither the FORMULA or the SUM method seem
> > to allow variables of the FORMULA="SUM(F7:value2)" type.

> > What is especially annoying, is that in Word I could simply use an
> > "=SUM(ABOVE)" and all my problems would be solved.

> > Does anyone out there know a solution to this deceptively simple task.

> > Thanks in advance

> > Phil Cook

--

Dave Peterson



Tue, 27 Jul 2004 07:22:11 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Using a macro to insert footer on last sheet for Excel

2. Inserting/Deleting rows on a protected sheet

3. Insert Row/Copy Columns In Protected Sheet

4. protect sheet/insert row

5. Inserting Rows in a Protected Sheet??

6. Inserting rows in a protected sheet

7. Enable Autosum in a protected sheet

8. Macro / VB to Insert Rows

9. Insert single or more rows in many sheets with macro

10. How can I know (in VB) how mutch rows a sheet has used


 
Powered by phpBB® Forum Software © phpBB Group