OleObject-Preventing deletion

[avi]

Hello,

Is there a way that could prevent the user manually deleting an
OleObject (ActiveX) from the ActiveSheet?

Thanks
Avi

 

[Héctor Miguel]

hi, !

Is there a way that could prevent the user manually deleting an OleObject (ActiveX) from the ActiveSheet?

AFAIK embedded ActiveX objects requires excel/vba in "design mode" for the user could "manually" select & delete'em
so, beside protecting that worksheet, you need some vba like the following:

put this in ThisWorkbook code-module:

Private Sub Workbook_Open()
Design_Mode False
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Design_Mode
End Sub

and this in a standard code-module:

Option Private Module
Sub Design_Mode(Optional On_Off As Boolean = True)
Dim cBar As CommandBar
On Error Resume Next
For Each cBar In Application.CommandBars
cBar.FindControl(Id:=1605, Recursive:=True).Enabled = On_Off
Next
End Sub

hth,
hector.

 

[avi]

Thanks, but actually there is a way to select the activeX without
being in Design mode. It is one of the available methods for
OleObjects

Selecting th object enales the user to drag it, resize it and delete
it. I'm looking for away to prevent deleting

Thanks

Avi

 

[Peter T]

Please describe how a user can select an OLEObject / embedded ActiveX
without being in Design mode or using code.

You could protect objects on the sheet which would prevent deletion, though
as I'm sure you are aware sheet/workbook protection is not very secure.

Regards,
Peter T

 

[avi]

Here is how I do it:


For Each G In xlapp.ActiveSheet.OLEObjects
If TypeName(G.Object) = "SSlider" Then MySliders.Add G
Next
For i = 1 To MySliders.Count
If MySliders(i).Name = IndicatorSpecificName Then
Ind = i
End If
Next
MySliders(Ind).Select

Avi

 

[Peter T]

I don't follow, "Here is how I do it". Is that intended as an answer to my
question to you (it's not), or as an answer to something else, or for
general information, or a question about something else.

Regards,
Peter T

 

[avi]

Not sure to understand your remark

You've asked to know how can a user select an OleObject without being
in Design Mode

I therefore presented the code that enables to create an event (in my
case a simple Click on the object) that makes the object selectable
(Anchors at its corners)

Please try rephrasing your question in case I didn't understand it

Thanks
Avi

 

[Peter T]

I know it's possible to select oleObjects with code, but no need even to
select. This will delete them all -
ActiveSheet.OLEObjects.Delete

My question to you was how to select an oleObject without being in design
mode or without using *code*. In your reply to Hector you were talking about
what the "user" can do, which normally means manually.

Have you tried protecting the sheet as I suggested, you only need to protect
Objects. As I said not very secure, but nothing in Excel is totally secure

Regards,
Peter T

 

[avi]

Thanks

Protecting objects in the sheet seems indeed a good way to achieve
what I need

Avi

 

[Héctor Miguel]

hi, avi !

Protecting objects in the sheet seems indeed a good way to achieve what I need

this is part of your original post regarding (*manually* was very precise)...

"> ... prevent the user manually deleting an OleObject (ActiveX) ..."

and, this is what a first suggested...

"> ... beside protecting that worksheet ..."

hth,
hector.