Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
Hiya,
I am looking for a way to stop users from being able to hide/unhide rows in a shared spreadsheet. Can anyone help out please? Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
Hiya,
I am looking for a way to stop users from being able to hide/unhide rows in a shared spreadsheet. Can anyone help out please? Many thanks You can apply sheet protection! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
Unfortunately Sheet protection will not work on a shared spreadsheet.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
Unfortunately Sheet protection will not work on a shared spreadsheet.
Why not? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
On Thursday, June 26, 2014 10:04:27 PM UTC+1, GS wrote:
Unfortunately Sheet protection will not work on a shared spreadsheet. Why not? The only options under protect shared workbook refer to sharing with tracked changes. Excel does not allow structure protection in a shared book. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
On Thursday, June 26, 2014 10:04:27 PM UTC+1, GS wrote:
Unfortunately Sheet protection will not work on a shared spreadsheet. Why not? The only options under protect shared workbook refer to sharing with tracked changes. Excel does not allow structure protection in a shared book. I wasn't referring to 'protect shared workbook'! You can apply sheet protection to a shared workbook, which doesn't have anything to do with 'protect shared workbook'! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
The only options under protect shared workbook refer to sharing with
tracked changes. Excel does not allow structure protection in a shared book. I wasn't referring to 'protect shared workbook'! You can apply sheet protection to a shared workbook, which doesn't have anything to do with 'protect shared workbook'! Once you share a workbook the protect workbook functions get disabled and the protect structure options get ignored. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
I wasn't referring to 'protect shared workbook'! You can apply sheet protection to a shared workbook, which doesn't have anything to do with 'protect shared workbook'! Hi Garry, Won't this also disable the insert rows function? I need to be able to keep the ability to insert Rows and delete rows and just disable the hide row functions. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
I wasn't referring to 'protect shared workbook'! You can apply sheet protection to a shared workbook, which doesn't have anything to do with 'protect shared workbook'! Hi Garry, Won't this also disable the insert rows function? I need to be able to keep the ability to insert Rows and delete rows and just disable the hide row functions. Look at the options in the 'protect sheet' dialog to see all what you can allow. Certainly 'anything' is possible if your project manages data table rows/cols via VBA. This is how I handle protected sheets because it ensures that when users insert/delete rows/cols the worksheet's 'design structure' doesn't get 'broken'! Otherwise, if your project's worksheets don't implement a 'design structure' then all bets are OFF! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
On Thursday, June 26, 2014 10:34:49 PM UTC+1, GS wrote:
I wasn't referring to 'protect shared workbook'! You can apply sheet protection to a shared workbook, which doesn't have anything to do with 'protect shared workbook'! Hi Garry, Won't this also disable the insert rows function? I need to be able to keep the ability to insert Rows and delete rows and just disable the hide row functions. Look at the options in the 'protect sheet' dialog to see all what you can allow. Certainly 'anything' is possible if your project manages data table rows/cols via VBA. This is how I handle protected sheets because it ensures that when users insert/delete rows/cols the worksheet's 'design structure' doesn't get 'broken'! Otherwise, if your project's worksheets don't implement a 'design structure' then all bets are OFF! Hi Garry, This is not a question of preserving structure, the code I have written is not dependent on specific row numbers etc. I just need to prevent more advanced users from hiding rows of data that less experienced users are having difficulty locating and unhiding again. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
This is not a question of preserving structure, the code I have
written is not dependent on specific row numbers etc. I just need to prevent more advanced users from hiding rows of data that less experienced users are having difficulty locating and unhiding again. Hide/Unhide is not allowed via the UI when sheets are protected. Also, user input can only be in 'unlocked' cells. I was refering to sheets that *are structured* for design layout since that's what most of my projects are configured. I use code to hide/unhide and/or use Outlines to expand/collapse ranges. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
Hide/Unhide is not allowed via the UI when sheets are protected. Also, user input can only be in 'unlocked' cells. I was refering to sheets that *are structured* for design layout since that's what most of my projects are configured. I use code to hide/unhide and/or use Outlines to expand/collapse ranges. Hi Garry, Thank you but as I have mentioned I still need the ability to insert and delete rows (which the Sheet Protection will disable). I already know that the Show/Hide Sheet functions can be turned on/off through VBA and am looking for similar code to do the same thing to the hide/unhide row functions only. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
Hide/Unhide is not allowed via the UI when sheets are protected.
Also, user input can only be in 'unlocked' cells. I was refering to sheets that *are structured* for design layout since that's what most of my projects are configured. I use code to hide/unhide and/or use Outlines to expand/collapse ranges. Hi Garry, Thank you but as I have mentioned I still need the ability to insert and delete rows (which the Sheet Protection will disable). I already know that the Show/Hide Sheet functions can be turned on/off through VBA and am looking for similar code to do the same thing to the hide/unhide row functions only. And so now you see where the 'design structure' comes into play!!! The first thing about setting sheet protection is to set *UserInterfaceOnly=True* via VBA when protection is applied. Unfortunately, this is not a persistent property and so must be reset each time the file opens. This requires unprotecting protected sheets, then re-apply protection. Once reset, your code can hide/unhide rows/cols without having to toggle protection off/on. If you want to hide specific rows/cols then give those ranges a local scope defined name to use in code. If you need to insert preformatted rows/cols then position them starting at index 1 and hide them so they're not part of the UI. These are refered to as 'program rows/cols' that code uses during runtime. These also are given local scope defined names so code can unhide/copy/insert/hide as needed. The easiest way to do this is to use custom menus added to the 'Cells' (right-click popup) menu to allow users to hide and/or unhide selected rows/cols. In your case of just needing this functionality for rows, this is a trivial task. You must add the menuitems at startup and remove them at shutdown so they don't persist when your file is not open. You manage visibility by toggling the *Hidden* property of the range. So for example, if you select cells (or entire rows) C4:C7 and right-click the selection the user can select "Hide selected rows" and your code executes the following... Selection.EntireRow.Hidden = True OR if you want the same macro to just 'toggle' visibility... With Selection.EntireRow .Hidden = Not .Hidden End With ...which works well if you want to manage named ranges intersecting the selection. In the case of Outlines, I usually change Settings to put summary rows at the top, then use additional code in the Worksheet_BeforeDoubleClick event to toggle the underlying named range so it expands/collapses same as using the right-click menuitems. This requires using program cols for 'tagging' ranges when multiple ranges exist. An example of this would be a Profit/Loss sheet that expands/collapses top level accounts to unhide/hide detailed distributions that make up the total for the top level account. (Assumes, of course, that top level accounts are 'non-posting') So the success of implementing this functionality on protected sheets rests entirely on a sheet's 'design structure' and how that structure is managed by code. In this case, 'collapsed view' displays only summary data and 'detail view' can be all, selected, or individual ranges. Detail view is indented. Double-click the top level to expand collapsed details; double-click any cell in the 'outline group' (including top level items) collapses the group. All this is managed via sheet events. You asked for code to hide/unhide rows and I gave it. I also gave you the 'design structure' info required to make that work for your needs. All you need to do now is decide how you want your project to work and code for that however you like. If you need more assistance please post back with sheet design info sufficient to provide the correct help!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
You asked for code to hide/unhide rows and I gave it. I also gave you the 'design structure' info required to make that work for your needs. All you need to do now is decide how you want your project to work and code for that however you like. If you need more assistance please post back with sheet design info sufficient to provide the correct help!<g Hi Garry, I feel you are missing the point of this thread. The code requested was not to hide and show rows rather to disable the hide/show row function only. This must be achievable since the sheet protection can disable the function. Unfortunately along with other needed functions. The sheet in question has no use for outlines or summaries. I already know that the show hide sheet function can be disabled using the following Application.CommandBars("Worksheet Menu Bar").Controls("F&ormat").Controls("S&heet").Contr ols("&Unhide...").Enabled = False or a variant thereof I am looking for a similar line that will do the same for show/hide rows. The other issue is that this is a shared workbook and as such you can not alter protection properties once sharing has been set either manually or through VBA. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
You asked for code to hide/unhide rows and I gave it. I also gave you the 'design structure' info required to make that work for your needs. All you need to do now is decide how you want your project to work and code for that however you like. If you need more assistance please post back with sheet design info sufficient to provide the correct help!<g Hi Garry, I feel you are missing the point of this thread. The code requested was not to hide and show rows rather to disable the hide/show row function only. This must be achievable since the sheet protection can disable the function. Unfortunately along with other needed functions. The sheet in question has no use for outlines or summaries. I already know that the show hide sheet function can be disabled using the following Application.CommandBars("Worksheet Menu Bar").Controls("F&ormat").Controls("S&heet").Contr ols("&Unhide...").Enabled = False or a variant thereof I am looking for a similar line that will do the same for show/hide rows. The other issue is that this is a shared workbook and as such you can not alter protection properties once sharing has been set either manually or through VBA. Sorry if I've wasted your time! I admittedly do not work with 'shared workbooks' and so I'm not familiar with the nuances associated with that. As for the code to disable hide/unhide.., what you example above is what you want. You'll need to do same for each menu/commandbar that gives UI access to those features. Also, be sure to undo changes to those menus at shutdown so Excel's 'tlb' file isn't messed up when Excel closes. I normally build a delimited string of the menuitems so code uses a simple loop. This string will contain value pairs like this... commandbar=control ...where each value holds the name of the object. I use a global variable as a 'flag' to set control state at startup/shutdown (ergo open/close) so my projects know what AppMode they're in. Dim gbShuttingDown As Boolean In my Auto_Close sub... Sub Auto_Close() gbShuttingDown = True: ShutdownApp End Sub In my ShutdownApp sub... Sub ShutdownApp() '... ManageMenus RestoreExcelSettings '... End Sub In the ManageMenus sub I set the Enabled property of controls to the value of gbShuttingDown... Sub ManageMenus() '... EnableDisable_BuiltinCtrls '... End Sub Sample EnableDisable_BuiltinCtrls procedu Const gsMenusToDisable$ _ = "Worksheet Menu Bar:F&ormat:&Row:&Unhide" _ & ",Worksheet Menu Bar:F&ormat:&Row:&Hide" _ & ",Worksheet Menu Bar:F&ormat:&Column:&Unhide" _ & ",Worksheet Menu Bar:F&ormat:&Column:&Hide" _ & ",Row:&Hide,Row:&Unhide" _ & ",Column:&Hide,Column:&Unhide" Sub EnableDisable_BuiltinCtrls() Dim vSz, vCtls, ctl As Object On Error Resume Next '//can't toggle disabled context menus For Each vSz In Split(gsMenusToDisable, ",") vCtls = Split(vSz, ":") Select Case UBound(vCtls) Case Is = 1 CommandBars(vCtls(0)).Controls(vCtls(1)).Enabled = gbShuttingDown Case Is = 2 CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Enabled = gbShuttingDown Case Is = 3 CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Enabled = gbShuttingDown Case Is = 4 CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Controls(vCtls(4)).En abled = gbShuttingDown Case Is = 5 CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Controls(vCtls(4)).Co ntrols(vCtls(5)).Enabled = gbShuttingDown End Select Next 'vSz End Sub ...where at startup gbShuttingDown = False, thus sets Enabled to False. At shutdown the variable reverts to True and so enables the controls. Note that when working with some properties of builtin menus, errors can be raised trying to change them and so are escaped. An example is your sample code for Sheets; if there are no hidden sheets then that menuitem is disabled and trying to change it throws an error. You could just list the commandbar name once to shorten the string, but then a 3rd delimiter would be required along with another variant to contain the 3rd Split() and another For Each loop. I used to use a worksheet table for this chore before I started using automated instances of Excel wherein my menus and toolbars are the only one's available. The table was constructed same as my commandbar table so was easy to manage in a similar manner! This would be overkill for your needs!<g HTH -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
Hi Garry, Many thanks for this. The example you provided works perfectly and opens the door to a lot more menu control abilities :) Sym |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
incidentally... if the code is also placed in the following
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) gbShuttingDown = True: ShutdownApp End Sub Then the menus will unlock when the user swaps to a different workbook and using the code below will lock again when the user swaps back to this workbook. Private Sub Workbook_WindowActivate(ByVal Wn As Window) gbShuttingDown = False: ShutdownApp End Sub Sym |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Disable/Enable hide entire row ability
On 06/30/2014 5:02 PM, symbiosis001 wrote:
incidentally... if the code is also placed in the following Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) gbShuttingDown = True: ShutdownApp End Sub Then the menus will unlock when the user swaps to a different workbook and using the code below will lock again when the user swaps back to this workbook. Private Sub Workbook_WindowActivate(ByVal Wn As Window) gbShuttingDown = False: ShutdownApp End Sub Sym This changes the structure of your project such that EnableDisable_BuiltinCtrls is no longer used exclusively at startup/shutdown. In this case you need to make it generic so it can be called from anywhere in your project. This means changing your project as follows... In a standard module named "m_OpenClose": Option Explicit Public Const gsMenusToDisable$ _ = "Worksheet Menu Bar:F&ormat:&Row:&Unhide" _ & ",Worksheet Menu Bar:F&ormat:$Row:&Hide" _ & ",Worksheet Menu Bar:F&ormat:&Column:&Unhide" _ & ",Worksheet Menu Bar:F&ormat:&Column:&Hide" _ & ",Row:&Hide,Row:&Unhide" _ & ",Column:&Hide,Column:&Unhide" Public gbShuttingDown As Boolean '...any other global scope variables/constants use by this project Sub Auto_Open() '... 'Disable builtin menus/controls not to be used EnableDisable_BuiltinCtrls gbShuttingDown '... End Sub Sub Auto_Close() gbShuttingDown = True: ShutdownApp End Sub Sub ShutdownApp() 'Restore disabled builtin menus/controls EnableDisable_BuiltinCtrls gbShuttingDown '...other shutdown code End Sub Sub InitGlobals() ' Used to initialize global variables with runtime values '... End Sub ...where this should (IMO) be the default standard module for all projects. I don't use the 'Microsoft Excel Objects' for any purpose and so Excel events are handled by a Class Module. I can provide code for this if you wish, but for now I'll just example how to code your Window Activate/Deactivate events in the 'ThisWorkbook' component of your project... Option Explicit Private Sub Workbook_WindowActivate(ByVal Wn As Window) EnableDisable_BuiltinCtrls False End Sub Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) EnableDisable_BuiltinCtrls True End Sub Finally, change EnableDisable_BuiltinCtrls as follows. Sub EnableDisable_BuiltinCtrls(bEnabled As Boolean) Dim vSz, vCtls, ctl As Object On Error Resume Next '//can't toggle disabled context menus For Each vSz In Split(gsMenusToDisable, ",") vCtls = Split(vSz, ":") Select Case UBound(vCtls) Case Is = 1 CommandBars(vCtls(0)).Controls(vCtls(1)).Enabled = bEnabled Case Is = 2 CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Enabled = bEnabled Case Is = 3 CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Enabled = bEnabled Case Is = 4 CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Controls(vCtls(4)).En abled = bEnabled Case Is = 5 CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Controls(vCtls(4)).Co ntrols(vCtls(5)).Enabled = bEnabled End Select Next 'vSz End Sub -- Regards, Garry Free Usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is it possible to disable the ability to save a file unless the u. | Excel Discussion (Misc queries) | |||
Disable ability to Protect | Excel Programming | |||
Hide Enable/Disable Startup | Excel Programming | |||
Hide Enable/Disable Macro pop-up when file opens | Excel Worksheet Functions | |||
enable / disable macros | Excel Programming |