Hi!

I am having one VB exe through which I am protecting the work sheet.
However, from the resultant sheet, I still want to allow user to Group /
Ungroup the columns.

Can you please tell me is there any flag that I can use for? The limitation
is I can not use Macros in the file.

Please help, Thanks!

Regards,
Sachin Vaishnav

If I were doing this in VBA, I could do this:

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

Maybe you could have your VB code protect the worksheet with the
userinterfaceonly and enableoutlining set the way you want. (I don't speak the
VB.)



Sachin Vaishnav wrote:

Hi!

I am having one VB exe through which I am protecting the work sheet.
However, from the resultant sheet, I still want to allow user to Group /
Ungroup the columns.

Can you please tell me is there any flag that I can use for? The limitation
is I can not use Macros in the file.

Please help, Thanks!

Regards,
Sachin Vaishnav

--

Dave Peterson

Hi Sachin,

You can manage sheet protection how you want from VB very easily. You
should, however, include a check to see if the target worksheet belongs to
your VB project before applying the protection. This will prevent disturbing
other worksheets that may not use protection. <just a safeguard

I assume you already have established a variable reference to the running
instance of Excel via the appropriate APIs (FindWindowA and SetWindowLongA),
in your VB project.

Following is some standard code that I use for applying sheet protection,
modifying it as required. ***It should be noted that the protection
parameters you are looking to manage are only available in Excel v10 and
later***

How I use it is, I cut&paste the parameters I require so they run
concurrent, then comment out the ones I don't want (as shown). The entire
list of available parameters is always there to pick & choose the order. It
doesn't matter what the order is.

Here's the code:

Sub wksProtect()
With ActiveSheet
If val(Application.Version) = 10 Then
.Protect Password:=" ", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Userinterfaceonly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True ', _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowEditRanges:=True, _
AllowFormattingCells:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
AllowUsingPivotTables:=True
Else
.Protect Password:=" ", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Userinterfaceonly:=True
End If
' .EnableSelection = xlUnlockedCells
' .EnableSelection = xlNoSelection
End With
End Sub

Note that the current parameters being used stop after
AllowFormattingRows:=True and the ' comments out the remaining ones. It's
important that the ' is placed before the comma after the last one being
used, and the very last one can never have a comma after it.

Note also the xlEnableSelection constants are listed. These are also
commented out to leave only the one I require. You can have only one, or none
(which, as in the example above, defaults to xlNoRestrictions). These are
Worksheet level constants, and not part of the protection. They are, however,
only effective if a worksheet is protected.

Be sure you qualify your references. This code resides in an Excel add-in
file, so all references are (internal) to the Excel Application, whether
implicit or explicit. If, for example, your variable for the running instance
of the Excel.Application is xlApp, then to use this code in VB you need to
include it as follows:

In the line With ActiveSheet you would use something like:
With xlApp.ActiveSheet

In the line If Val(Application.Version)... you would use something like:
If Val(xlApp.Version)...

Alternatively, (not tested with this code) you could try something like this
in a sub:
With xlApp
wksProtect
End With
where the reference carries, so you can just run the code "as is" by calling
it after establishing the reference. I know this works with other code I've
used with VB6 DLLs, but I can't say that for sure about this specific code.

HTH
Regards,
Garry

Lets say I have 3 instances of Excel open and GetObject doesn't return the
correct one (checked by looking at the caption). As I read your post, you
suggest he

I assume you already have established a variable reference to the running
instance of Excel via the appropriate APIs (FindWindowA and
SetWindowLongA),
in your VB project.

that you have code that will convert the windows handle returned with these
functions into the automation handle needed to automate the application
(such as returned by getobject). Something like

Dim xlApp as Excel.Application

' hwnd found by using FindWindowA and caption name
set xlApp = Somefunction(arg, arg, hwnd)

I would guess.

I would be very interested in seeing your code if you have the inclination
as this would be a useful capability often asked for here.

--
Regards,
Tom Ogilvy


"GS" wrote in message
...
Hi Sachin,

You can manage sheet protection how you want from VB very easily. You
should, however, include a check to see if the target worksheet belongs to
your VB project before applying the protection. This will prevent
disturbing
other worksheets that may not use protection. <just a safeguard

I assume you already have established a variable reference to the running
instance of Excel via the appropriate APIs (FindWindowA and
SetWindowLongA),
in your VB project.

Following is some standard code that I use for applying sheet protection,
modifying it as required. ***It should be noted that the protection
parameters you are looking to manage are only available in Excel v10 and
later***

How I use it is, I cut&paste the parameters I require so they run
concurrent, then comment out the ones I don't want (as shown). The entire
list of available parameters is always there to pick & choose the order.
It
doesn't matter what the order is.

Here's the code:

Sub wksProtect()
With ActiveSheet
If val(Application.Version) = 10 Then
.Protect Password:=" ", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Userinterfaceonly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True ', _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowEditRanges:=True, _
AllowFormattingCells:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
AllowUsingPivotTables:=True
Else
.Protect Password:=" ", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Userinterfaceonly:=True
End If
' .EnableSelection = xlUnlockedCells
' .EnableSelection = xlNoSelection
End With
End Sub

Note that the current parameters being used stop after
AllowFormattingRows:=True and the ' comments out the remaining ones. It's
important that the ' is placed before the comma after the last one being
used, and the very last one can never have a comma after it.

Note also the xlEnableSelection constants are listed. These are also
commented out to leave only the one I require. You can have only one, or
none
(which, as in the example above, defaults to xlNoRestrictions). These are
Worksheet level constants, and not part of the protection. They are,
however,
only effective if a worksheet is protected.

Be sure you qualify your references. This code resides in an Excel add-in
file, so all references are (internal) to the Excel Application, whether
implicit or explicit. If, for example, your variable for the running
instance
of the Excel.Application is xlApp, then to use this code in VB you need to
include it as follows:

In the line With ActiveSheet you would use something like:
With xlApp.ActiveSheet

In the line If Val(Application.Version)... you would use something like:
If Val(xlApp.Version)...

Alternatively, (not tested with this code) you could try something like
this
in a sub:
With xlApp
wksProtect
End With
where the reference carries, so you can just run the code "as is" by
calling
it after establishing the reference. I know this works with other code
I've
used with VB6 DLLs, but I can't say that for sure about this specific
code.

HTH
Regards,
Garry