catch a event - OnAction...

leshem

New Member
Joined
Sep 23, 2002
Messages
49
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.....
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
L

Legacy 98055

Guest
<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

New Member
Joined
Sep 23, 2002
Messages
49
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

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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
 
L

Legacy 98055

Guest

ADVERTISEMENT

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
 
L

Legacy 98055

Guest

ADVERTISEMENT

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

Well-known Member
Joined
May 11, 2002
Messages
810
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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>
 
L

Legacy 98055

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

Forum statistics

Threads
1,144,061
Messages
5,722,287
Members
422,420
Latest member
losc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top