Grouping versus outline and copying data onto new row
Posted by Edwin on September 15, 2001 12:51 AM
This is a repost of questions that were unanswered in this forum
1) I have constructed a spreadsheet that has certain formulas that I would like to have protected and hidden, and as well I have grouped and outlined those rows containing the formulas.
The problem occurs when I have protected the sheet (hence protecting the formulas) but I am thus unable to expand and collapse the outlined rows. Is there anyway I am able to achieve both my objectives of allowing a freely expanding and collapsing outlines of rows in the sheet as well as protecting and hiding formulas in certain cells that are within those rows that are collapsed.
2) I have another sheet where there is a new set of information posted onto it daily. I would like to construct a button that will allow me to at the press of the button and have these selected cells be copied and pasted onto a new sheet as a new row.
Eg. My sheet is used to track stock count in my warehouse. Thus every morning, I would have a few figures namely, opening stock, closing stock and say net stock. I would like to have the button to copy these data (which might not be on the same row or same column on the sheet but rather scattered throughout the sheet) onto a new sheet as a new row. Thus the new row should look like (from left to right) date, opening stock, offtake, closing stock and net stock.
Furthermore, I would like that new sheet with the new row to calculate the change in the net stock from a day to day basis.
3) I would also like to have the workbook to be such that everytime the sheet is printed, the footer would show something like "Written by Edwin", to claim authorship to the workbook. I did receive a response from someone on this forum who suggested me setting some sort of VBA code to enable this, but since I am unfamiliar with VBA code, I am unable to come up with the code. Furthermore, I need to know, how can we prevent someone from changing this code. I know Excel has a in-built function which allows you to set the header or footer, which is basically what I want, but is there a way to prevent others from changing it, by a password or otherwise.
I would appreciate any help rendered. Thanks.
Posted by Damon Ostrander on September 15, 2001 1:16 PM
Re: Grouping versus outline
Hi Edwin,
Regarding your first question, there are two ways to do this:
1. If you have used the grouping and outlining just to make the formulas invisible, there is another way to do this. Simply unprotect the sheet, select the cells whose formulas you want hidden, go to Format -> Cells -> Protection tab -> check the Hidden box. Then re-protect the worksheet. The formulas for these cells will no longer be visible on the formula toolbar.
2. If you want to retain grouping/outlining, simply add a couple buttons to your sheet and assign them to macros that do the grouping/ungrouping. At the beginning of each macro add a line of code to unprotect the sheet, and a line at the end to re-protect it. You can password protect this code so that nobody can see it (and what the worksheet password is) in the Visual Basic Editor (VBE) by going to Tools -> VBAproject Properties... -> Protection tab, and checking the Lock Project for Viewing box and entering a password.
I hope this is helpful. I don't have time to address questions 2 and 3 at the moment, but they are not difficult, and hopefully someone else can answer them shortly (Ivan Moala does this stuff in his sleep).
Good luck.
Damon
Posted by Edwin on September 15, 2001 6:51 PM
Re: Grouping versus outline
Hi Damon,
I think suggestion 2 makes more sense since what I am trying to achieve with grouping and outlining is not to hide those cells with formulas but more so to make my sheet look more compact and presentable when necessary.
As for option 2, kindly advise as to how I can still use the default 1,2,3,4 buttons that appears at the top left hand corner when one is using outlining and to get a macro to handle the auto protect, unprotect procedure. I am unfamiliar with VBA so a more thorough approach complete with VBA code will really make it so much easier for me. Thanks.
Edwin
Posted by Damon Ostrander on September 17, 2001 9:47 AM
Re: Grouping versus outline
Hi again Edwin,
This is a bit involved to explain because of your unfamiliarity with VBA, but here goes:
1. Here are 4 VBA routines that set the outline levels to 1,2,3,4 respectively.
Sub ShowOutline1()
With ActiveSheet
.Unprotect password:="mypassword"
.Outline.ShowLevels rowlevels:=1
.Protect password:="mypassword"
End With
End Sub
Sub ShowOutline2()
With ActiveSheet
.Unprotect password:="mypassword"
.Outline.ShowLevels rowlevels:=2
.Protect password:="mypassword"
End With
End Sub
Sub ShowOutline3()
With ActiveSheet
.Unprotect password:="mypassword"
.Outline.ShowLevels rowlevels:=3
.Protect password:="mypassword"
End With
End Sub
Sub ShowOutline4()
With ActiveSheet
.Unprotect password:="mypassword"
.Outline.ShowLevels rowlevels:=4
.Protect password:="mypassword"
End With
End Sub
Go into the Visual Basic Editor (VBE) by going to Tools -> Macros... and then create a new code module using Insert -> Module. Paste these 4 routines into the code window that appears.
You should change the password string to whatever the password is on your sheet.
2. Create 4 buttons on the worksheet of interest, one to run each macro. You must use Forms buttons (rather than ActiveX Controls buttons) to do this. To get a Forms button in Excel, bring up the Forms toolbar (View -> Toolbars, select Forms). On the toolbar click on the Button button, then draw the button on the worksheet (works just like a drawing object). As soon as you do this, a dialog will pop up that will ask you to assign a macro to the button, and list the four macros you previously inserted. Pick ShowOutline1.
3. Give the button a caption by simply selecting the button text and typing the text you want (e.g., "Level 1"). If the button is not selected, you can select it by holding down the control key when you click on it. You can also change the font of the text as you edit it.
4. Repeat the process until you have a button for each outline level assigned to the corresponding macro.
5. Have a cup of coffee and enjoy your work.
Incidentally, I have assumed that you are grouping rows, but if you want to use the same methodology to group columns, just change rowlevels to columnlevels in the routines above.
Damon
