HOW TO TURN ON 'AUTOSUM' ON ON A PROTECTED SHEET THRO VB

[CAPTGNVR]

Dear Programmers

I have some group of cells which keeps varying and would be nicer to
have the AUTOSUM on a protected sheet. Pls suggest

BRGDS/CAPT GN VENKAT RAJARAM

 

[Earl Kiosterud]

Capt,

If you're referring to the quick calculate thing on the status bar that
instantly sums selected cells, it works with a protected sheet. The
protection (Tools - Protection - Protect Sheet) has to have allowed cell
selection.

If you mean the Autosum button that puts =SUM(here:there) into a cell, you
can't do that in a protected sheet; the Autosum button is disabled. You
could do it manually by typing =SUM( then dragging your range, then type the
closing ).

 

[CAPTGNVR]

D/EARL
Thnks ur feedback. I was wanting the autosum button which is disabled
in a protected sheet. Want to know if there are means to have a vb to
get this button enabled.

 

[Earl Kiosterud]

Captgnvr,

I don't think there's a direct way to enable the autosum button on a
protected sheet. You could have your own macro do this, and could be run by
any button you create on any toolbar.

 

[CAPTGNVR]

D/EARL

The problem is if i unprotect the sheet in macro- then users might del
a row or formulas by mistake. So let me know if it can be done. I am
now trying to make this confusing relative and absolute ref in macro
and give a button image resembling 'auto sum'. I will also have
problems in writing the code in VB how to make the program know how
many cells from active cell to top to take. Your advice pls.

 

[Earl Kiosterud]

Capt,

As an alternative, you might want to consider this. Put your totals at the
top of the sheet, e.g.: =SUM(A2:A65536). Now rows can be added and they're
included. If you freeze the row(s) containing the totals (and headings and
other pretty stuff) the totals are always visible no matter where the user
scrolls. Window - Freeze panes. SOme people get all squirmy at the idea of
having totals at the top, probably because when they were taught to add they
put the sum at the bottom, and that's the way it is. Ever shall be.
They'll come around. It works great.

If you still want to build formulas in a macro, here's some stuff:

Put one of these in a regular module:

Sub AutoSum()
'Creates =SUM() formula in cell immediately below selection. Users first
selects cells to sum
ActiveSheet.Unprotect Password:="mypass"
Cells(Selection.Row + Selection.Rows.Count, Selection.Column).Formula = _
"=SUM(" & Selection.Address & ")"
ActiveSheet.Protect Password:="mypass"
End Sub

Sub AutoSum2()
Creates =SUM() formula from active cell to top of region containing data
Dim Contents As String
ActiveSheet.Unprotect Password:="mypass"
Contents = Range(Selection.Offset(-1, 0), Selection.Offset(-1,
0).End(xlUp)).Address
ActiveCell = "=SUM(" & Contents & ")"
ActiveSheet.Protect Password:="mypass"
End Sub

Sub AutoSum3()
Creates =SUM() formula from top of worksheet to activecell
Dim Contents As String
ActiveSheet.Unprotect Password:="mypass"
Contents = Range(Selection.Offset(-1, 0), Cells(1,
Selection.Column)).Address
ActiveCell = "=SUM(" & Contents & ")"
ActiveSheet.Protect Password:="mypass"
End Sub

The user makes a selection and clicks a button you've created on a toolbar
to run the macro. It's probably a bit user-error-prone. It's a start.

You can use View - Toolbars - Customize to create toolbars and make your own
buttons, and the right-click on a button you've created to draw a face on it
and assign it to your macro.

 

[CAPTGNVR]

D/EARL
Thank you Earl and I got one more thread closed. Your adivce worked
and i am happy for the progress this site is providing.