Problem with using an image to fire a macro from a protected chart sheet

[oCoCarbon]

I have a chart sheet which contains an image assigned to a macro. The macro should take the user to a data entry sheet where they can edit the data for the chart.

This works just fine when the sheet is unprotected, but when it is protected it won't let me click on the image. Is there any way of selectively protecting a chart sheet so the macro control image stays active?

 

[OfficeUser]

You could unlock the sheet when the code initializes using :

Worksheets("Sheet1").Unprotect password:="Password"

Then lock it after your code runs:

Worksheets("Sheet1").Protect password:="Password"

 

[oCoCarbon]

Thanks for answering.

There's no problem assigning a macro to an image in a locked worksheet though. The problem is related to a protected chart sheet.

 

[OfficeUser]

I am unsure if this is related but when you protect a sheet, you can check or uncheck the option to 'Edit objects'.

 

[oCoCarbon]

That's what I was looking for, thanks!

The line I used is as follows.

Chart1.Protect Password:=secret, DrawingObjects:=False, Contents:=True

 

[oCoCarbon]

I take that back. I thought it was working, but it actually seems to leave the worksheet basically unprotected. The user can still move the chart around, edit the colours, etc.

I can make it work with a normal button to fire the macro but wanted to use a nice-looking icon.

 

[oCoCarbon]

Ok, I've found a way around it now. The solution is to add a blank label directly over the icon and assign the macro to that.

 

[OfficeUser]

I thought the image was an inserted picture. I have recently done the same thing with a logo in a worksheet. I put a transparent label over the icon of the logo and it compares the user name in the coding against the system userid and unlocks the sheet if it is correct.