Is there any way to solve this Protecting Shapes problem?

[nimo]

Hi

I am using ActiveSheet. Protect OR ActiveWorkbook.Protect to Protect all the Data in the Workbook.........now, I have some Shape Objects and I want to be able to click them, so that after clicking some MACROs trigure.But I should not b able to change DATA on those objects. Now, if I protect, then clicking also gives me Run-time errors......is there any way to solve this?

Thanxc

 

[rmikesmith]

nimo,

I created a shape object on a worksheet, assigned a macro, then protected the worksheet. Clicking the shape runs the macro, no problem. Perhaps you can supply more info, relevant macro code and which run-time error you are seeing.

Regards,
Mike

 

[nimo]

Hi...

I am using a Macro that uses This line and I get runtime error in this line when I use this Macro after protecting the Sheet(I am not Copy-Pasting, so there might b a little error in the code here, but it explains the problem I think ):

"
ActiveSheet. Protected DrawingObjects := True
ActiveSheet.Shapes(Application.Caller).Select 'I get error
With Selection
.Autotext = True
.............
.............
"
Thanx

 

[RobBroekhuis]

Yup, I reproduced the error. But the error only occurs when you try to select the object. Do your processing without selecting:

with ActiveSheet.Shapes(Application.Caller)
.Autotext = True
...etc...
end with

Rob

 

[nimo]

Hi..

I tried your way....Still problems. Actually I have a Button on a Excel Sheet. And, there is .OnAction with the Button objest. So when I click the button on the Excel Sheet, a MAcro called "Text" will run. Now thios macro is like this:

Sub text()
Dim Msg, Response
ActiveSheet.Protect Password:=1234, DrawingObjects:=True, Contents:=True, Scenarios:=True
With ActiveSheet.Shapes(Application.Caller)
If .ShapeRange.Width <> 60 Then ' GET ERROT
Call Re_size
Else
.ShapeRange.ZOrder msoBringToFront ' GET ERROR
.AutoSize = True ' GET ERROR
End If
End With
End Sub

Sub Re_size()
h = 25.5
w = 60
With ActiveSheet.Shapes(Application.Caller)
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = h
.ShapeRange.Width = w
End With
End Sub

I think if I am using With, then .AutoText is also not working........i get error on every line..........can U check what is wrong please?

 

[rmikesmith]

nimo,

Why are you protecting the worksheet at the start of the macro? Once you've done this, you cannot make changes to the Shape object. Normally, a macro that needs to make changes to a workksheet's contents, or objects on the worksheet will temporarily unprotect the sheet, make its changes, then protect the sheet again. Alternatively, you could set the DrawingObjects parameter to False.

Regards,
Mike

 

[nimo]

The problem with setting the DrawingObjects parameter to False is that then any one can change the Text on the Button PObjects or even delete the Buttons.....and this is the reason the Sheet has to be protected from the start, so that no Change is possible, actually it has to be protected just when the Buttons are drawn(I will do that later in the code) ..but in that case Application.Caller does not work................hu, looks like a problem. Can you think of any idea?

 

[LoNeRaVeR]

nimo:

As rmikesmith stated, why don't you protect the sheet after you create the buttons the way you want. This way the sheet will still be protected so the user cannot make changes, and you won't receive your errors.

Regards, LoNeRaVeR

 

[nimo]

I got the idea..i will try it ..thank you all for yr time...