Allow grouping / ungrouping while protecting the sheet
 

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

Allow grouping / ungrouping while protecting the sheet
 

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

Allow grouping / ungrouping while protecting the sheet
 

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

Allow grouping / ungrouping while protecting the sheet
 

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

Allow grouping / ungrouping while protecting the sheet
 

Here's a sample method from the same class module. It demonstrates it's use
with reference to the active cell on a worksheet. I suspect you're looking
for code to use with VBA, but since you did mention automation..

' ***********************
' Class method procedures
' ***********************

Public Sub WriteMessage()
mxlApp.ActiveCell.Value = mxlApp.Version & mxlApp.Build
End Sub

Regards,
Garry

Allow grouping / ungrouping while protecting the sheet
 

Thanks.

I am familiar with automation.

--
Regards,
Tom Ogilvy


"GS" wrote in message
...
Here's a sample method from the same class module. It demonstrates it's
use
with reference to the active cell on a worksheet. I suspect you're looking
for code to use with VBA, but since you did mention automation..

' ***********************
' Class method procedures
' ***********************

Public Sub WriteMessage()
mxlApp.ActiveCell.Value = mxlApp.Version & mxlApp.Build
End Sub

Regards,
Garry

Allow grouping / ungrouping while protecting the sheet
 

Hi Tom,

Sorry it's not what you're looking for. I did say it was from a DLL used
with my add-in, so the caller is the current running instance of Excel TO the
DLL. The DLL grabs that instances handle.

I think you mean you want to grab the handle of any/all running instances of
Excel (or any application for that matter). I did see some VB code that does
exactly that so if I find it I'll pass it on.

You could try Dan Appleman's "Visual Basic Programmer's Guide to the Win32
API" if you have it. But I'm almost certain I saw something relating
specifically to grabbing handles of running apps so as to identify which
instance is being referenced. If this is what you want then post back, and
I'll keep an eye out.

Regards,
Garry

Allow grouping / ungrouping while protecting the sheet
 

No, but thanks anyway to taking the time. I understand that given an
automation handle I can get a windows handle. I understand I can loop
through all the windows and identify windows handles for all the instances
of Excel. I understand that processes can be enumerated as well.


My question concerned using a windows handle to one of many running
instances of excel, setting an automation reference to that instance of the
application so it could be manipulated through automation.

I have seen some information on how to do it, but it if someone happend to
already have the code . . .



--
Regards,
Tom Ogilvy


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

Sorry it's not what you're looking for. I did say it was from a DLL used
with my add-in, so the caller is the current running instance of Excel TO
the
DLL. The DLL grabs that instances handle.

I think you mean you want to grab the handle of any/all running instances
of
Excel (or any application for that matter). I did see some VB code that
does
exactly that so if I find it I'll pass it on.

You could try Dan Appleman's "Visual Basic Programmer's Guide to the Win32
API" if you have it. But I'm almost certain I saw something relating
specifically to grabbing handles of running apps so as to identify which
instance is being referenced. If this is what you want then post back, and
I'll keep an eye out.

Regards,
Garry

Allow grouping / ungrouping while protecting the sheet
 

Yes, I think we've both seen this info because that's exactly what I was
referring to. What I saw had source code so I'll keep an eye out for it.

Regards,
Garry