How to Disable/Enable hide entire row ability

  • Thread starter Thread starter symbiosis001
  • Start date Start date
S

symbiosis001

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
 
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
Click to expand...

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
 
Why not?
Click to expand...


The only options under protect shared workbook refer to sharing with tracked changes. Excel does not allow structure protection in a shared book.
 
The only options under protect shared workbook refer to sharing with
tracked changes. Excel does not allow structure protection in a
shared book.
Click to expand...

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
 
The only options under protect shared workbook refer to sharing with
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'!
Click to expand...

Once you share a workbook the protect workbook functions get disabled and the protect structure options get ignored.
 
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'!
Click to expand...

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.
 
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.
Click to expand...

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
 
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!
Click to expand...
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 havingdifficulty locating and unhiding again.
 
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.
Click to expand...

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
 
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.
Click to expand...

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.
 
Hide/Unhide is not allowed via the UI when sheets are protected.
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.
Click to expand...

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
 
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>
Click to expand...

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 questionhas 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").Controls("&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.
 
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").Controls("&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.
Click to expand...

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 procedure:

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)).Enabled
= gbShuttingDown
Case Is = 5

CommandBars(vCtls(0)).Controls(vCtls(1)).Controls(vCtls(2)).Controls(vCtls(3)).Controls(vCtls(4)).Controls(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
 
Hi Garry,

Many thanks for this. The example you provided works perfectly and opens the door to a lot more menu control abilities :)

Sym
 
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
 
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
Click to expand...
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)).Enabled
= bEnabled
Case Is = 5

CommandBars(vCtls(0)).Controls(vCtls(1)).Controls(vCtls(2)).Controls(vCtls(3)).Controls(vCtls(4)).Controls(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
 
Back
Top