Excel VBA - OLEObject Error When WorkBook Is Set As "Shared"

I have a command button on a worksheet (not on a UserForm). It is a save button, and it used to be simply enabled/disabled depending on whether it was eligible for use.

It has been discovered, however, the folks are not using the save button. Rather then are saving their spreadsheets using the build-in Excel Save functions. The problem is that my button does more than just save their work. It also takes pieces of information off of their spreadsheet and puts it on a summary spreadsheet that goes to management in the form of a report. There in lies the problem when they do not use my save button.

So, one suggestion was to make it really big and change the back/fore colors on it to make it standout. I did that, and here is the resulting code.

vb Code:

With cmdSaveHFR
    .Enabled = True
    .BackColor = vbRed
    .ForeColor = vbYellow
    .Height = 43.5
    .Left = 8.25
    .Top = 178.5
    .Width = 108.5
    .Caption = "Click ME To Save HFR To Disc And To Update HFR Dbase File"
End With
 
'this puts it back to normal
 
With cmdSaveHFR
     .Enabled = False
     .BackColor = &HE0E0E0
     .ForeColor = &HC00000
     .Height = 19.5
     .Left = 8.25
     .Top = 189.75
     .Width = 108.75
     .Caption = "Save HFR"
End With

This works just fine until I set the spreadsheet with the button on it to "Shared" - when it is shared, and it must be shared as it is a menu type spreadsheet used by several different people in three different departments, my button sizing code generates a 1004 error, Unable to set the Height property of the OLEObject Class (if I comment out .Height =, then it will go right through the .Left .Top and .Width properties with this same error.)

So, my question is why can I change the buttons height, left, top and width when the workbook is NOT "Shared", but get an OLEObject error when attempting to make the same change when the workbook is "Shared"?

I think with a shared workbook, you can only modify data but you cannot modify any structure or object.

To prevent user uses the Excel built-in Save button or menu you can do it like this:

In ThisWorkbook code module:

Code:

---

Option Explicit

Public bAllowSave As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If bAllowSave = False Then
      MsgBox "Please Click button [Save HFR] to Save the workbook.", _
            vbExclamation, "Cannot Save Now"
      Cancel = True
  End If
End Sub

---

In the code module of the sheet that contains the button [cmdSaveHFR] :

Code:

---

Option Explicit

Private Sub cmdSaveHFR_Click()
  ThisWorkbook.bAllowSave = True
 
  '-- your extra code before saving here
 
  ThisWorkbook.Save
  ThisWorkbook.bAllowSave = False
End Sub

---

However, remember that those won't prevent users to disable/stop macros then save with Excel built-in Save function.

Quote:

---

Originally Posted by anhn

I think with a shared workbook, you can only modify data but you cannot modify any structure or object

---

Ahhhhh....well, that is a pain - but thanks for the info.

I'll play around with your suggestions and be back.

@hack: If you remember this thread :)

http://www.vbforums.com/showpost.php...6&postcount=28

Quote:

---

Originally Posted by koolsid

@hack: If you remember this thread :)

http://www.vbforums.com/showpost.php...6&postcount=28

---

:lol: The date on that is January 2008 - I can't remember what I did this morning. :rolleyes:

Quote:

---

Originally Posted by Hack

:lol: The date on that is January 2008 - I can't remember what I did this morning. :rolleyes:

---

Yeah I know that is a very old thread :lol:

You cannot modify the height/width etc of an ole object in shared mode.

Quote:

---

There in lies the problem when they do not use my save button.

---

The link that I gave is the solution to this particular problem ;)

I tried this

Code:

---

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If SaveAsUI = False Then

    Cancel = True

    MsgBox "You cannot save this workbook.  Use Save As"

End If

End Sub

---

I put a break on the line "Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)" so I could walk myself through this.

I clicked the "Save" icon on the tool bar. I clicked File/Save and File/Save As from the dropdown menu and completed the save each of the three times. At no time was this routine ever hit. It just didn't run.

Where did you place the code... You have to place it in the workbook code area... I just tried it and it worked...

See picture attached....

The BeforeSave thing was what I was playing with and I got no stoppage and no msgbox.

Your code didn't run perhaps you were in Design mode (macro was disable/stop)

Back to your original design in post#1, you can get rid of cmdSaveHFR and alow user to use built-in Save function with:

Code:

---

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If SaveAsUI = True Then '-- with an unSaved (new) workbook this is true
      Cancel = True '-- do not allow SaveAs on shared workbook
      Exit Sub
  End If
 
  MsgBox "Your extra code"
  '-- your extra code before save here

End Sub

---

Hi Hack

I tried it and it works just fine...

Is macros enabled in your workbook?

If not set it to low

Tools=>Macro=>Security=>Low

Hope this helps...

Quote:

---

Originally Posted by koolsid

Hi Hack

I tried it and it works just fine...

Is macros enabled in your workbook?

If not set it to low

Tools=>Macro=>Security=>Low

Hope this helps...

---

Macros are enabled in all workbooks and I can't reset the the macro security. That ability is restricted to admins.

Quote:

---

I can't reset the the macro security. That ability is restricted to admins.

---

hack what is it set to?

Low/medium/High ?

Quote:

---

hack what is it set to?

Low/medium/High ?

---

as he is running other macros, i doubt that security is the issue

i assume that the code, for before_save, is in the thisworkbook code module, for the workbook that you are wanting to modify the save command

the sample by kool in post #9, i tested and works for me