catch a event - OnAction...

[leshem]

Hi, Good Morning.
maybe someone will have an Idea for the following:
I have a protected worksheet. I am trying to draw a chart on it , so after the whole flow of DialogBoxes I get an Error Message says: you can not create a chart on a protected sheet. for Unprotect use Tools--> ...etc.
I have succeeded to remove the protction by:
CommandBars(1).Controls("C&hart...").OnAction = activesheet.unprotect
Now I need to protect the sheet again when the action is ended, the chart is drawn.
How can I protect the sheet again right on the end of the action, only by Code?

Thanks for your Quick answer.....

 

[Legacy 98055]

<pre>
Good morning leshem.
At the beginning of your procedure...

ActiveSheet.UnProtect

at the end

ActiveSheet.Protect



The syntax for including a password is as follows.

At the beginning of your procedure...

ActiveSheet.UnProtect "YourPassWordInQuotes"

at the end

ActiveSheet.Protect "YourPassWordInQuotes"

Tom

</pre>

 

[leshem]

Hi TsTom,
my problem is different then you think.
the Unprotect should operate the minute the user choose the Chart option from the menuBar "Insert" and the protect should be operate the minute the user's action is ended. it should be, as I see it, regards the command bar control action. it is not a procedure I wrote.
if it will be a procedure of protect/unprotect, I would have call it when the user choose the option from the menu and call it again automatically when the action is ended. in that case my question would be : how would I make the system know that the operation is ended, so that the procedure will be called automatically throw the code?

Did I make myself clearer now?

Thanks' any way for your answer.

 

[XL-Dennis]

leshem,

Difficult task You would like to achieve.

How about following approach:

1. Identify when the user select the Chart option from the menuBar then
- unprotect the sheet.
2. Store the activecells address and check it when it's changed then
- protect the sheet again.

Following event may be of interest:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Another approach is to check if the selection is a a range or not:

If TypeName(Selection) = "Range"

Kind regards,
Dennis

 

[Legacy 98055]

I definitely missed your problem.
Anyway, here is a workaround. I could not get the xlDialogChartWizard to show for some reason??? So this is a double-workaround.
This code will replace the default Execution of the Chart button with the public procedure named "InsertChart". When you click on the Chart button, the activesheet is unprotected, a temporary commandbar is created, though never shown, and then the chart wizard is started. When the user is finished and closes the wizard, the temporary commandbar is deleted and the activesheet is protected once again. This should solve your problem. Place all of this code in the workbook module.<pre>
Private Sub Workbook_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert") _
.Controls("Chart...").OnAction = "ThisWorkbook.InsertChart"
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert") _
.Controls("Chart...").Reset
End Sub

Public Sub InsertChart()
Dim cbrCustBar As CommandBar, ctlTempChart As CommandBarButton
ActiveSheet.Unprotect
Set cbrCustBar = Application.CommandBars.Add("Temp")
Set ctlTempChart = cbrCustBar.Controls _
.Add(msoControlButton, Application.CommandBars("Worksheet Menu Bar").Controls("Insert") _
.Controls("Chart...").ID, , , True)
cbrCustBar.Visible = False
ctlTempChart.Execute
'execution is halted here until the modal, wizard window is closed
cbrCustBar.Delete
ActiveSheet.Protect
End Sub</pre>

Tom
This message was edited by TsTom on 2002-10-13 11:14

 

[stevebausch]

Wouldn't a run-time error occur?

And then off to an error-handler?

 

[Legacy 98055]

Hi Steve.
Error handling should be included, but for the sake of simply hitting the main problem head on, this runs correctly as is. Anyone know why I could not show the Chart Wizard dialog? The following code should work in place of the above but I kept on getting a runtime error "show method failed" when trying to show the dialog.
<pre>
Private Sub Workbook_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert") _
.Controls("Chart...").OnAction = "ThisWorkbook.InsertChart"
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert") _
.Controls("Chart...").Reset
End Sub

Public Sub InsertChart()
ActiveSheet.Unprotect
Application.Dialogs(xlDialogChartWizard).Show
ActiveSheet.Protect
End Sub

</pre>
Tom

 

[stevebausch]

I didn't mean to imply a fault in any posted code. My idea was to take advantage of the error-handler being called. Go to a label, unprotect the sheet, then Resume.


When the code no longer needs an unprotected sheet, protect the sheet.

 

[Ivan F Moala]

On 2002-10-13 16:22, TsTom wrote:
Hi Steve.
Error handling should be included, but for the sake of simply hitting the main problem head on, this runs correctly as is. Anyone know why I could not show the Chart Wizard dialog? The following code should work in place of the above but I kept on getting a runtime error "show method failed" when trying to show the dialog.
<pre>
Private Sub Workbook_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert") _
.Controls("Chart...").OnAction = "ThisWorkbook.InsertChart"
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert") _
.Controls("Chart...").Reset
End Sub

Public Sub InsertChart()
ActiveSheet.Unprotect
Application.Dialogs(xlDialogChartWizard).Show
ActiveSheet.Protect
End Sub

</pre>
Tom

Tom not all Dialogs suport the Show command ?
Try

<pre/>
Public Sub foo()
ActiveSheet.Unprotect
Application.CommandBars.FindControl(ID:=436).Execute
ActiveSheet.Protect
End Sub
</pre>

 

[Legacy 98055]

Thanks Ivan...
What other use for the xlDialogChartWizard constant id it cannot be shown?
Tom