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"?
Re: Excel VBA - OLEObject Error When WorkBook Is Set As "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.
Last edited by anhn; Apr 13th, 2009 at 09:44 AM.
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
Re: Excel VBA - OLEObject Error When WorkBook Is Set As "Shared"
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.
Re: Excel VBA - OLEObject Error When WorkBook Is Set As "Shared"
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
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
Re: Excel VBA - OLEObject Error When WorkBook Is Set As "Shared"
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Reply With Quote
Originally Posted by anhn
Resolved", if the query is solved
The date on that is January 2008 - I can't remember what I did this morning. 
