We have an Excel Product that we are trying to get to work on the Mac. One of th

Question

We have an Excel Product that we are trying to get to work on the Mac. One of the worksheets displays a disclaimer, and if the user clicks on the I Don't Accept radio button and then clicks next, the workbook is supposed to close itself. The code is quite simple:

Public Sub HandleNextButton()

Dim filename As String

Dim opened_workbook As Workbook

If AcceptsDisclaimerChecked Then

FinishSetup

Else

   ThisWorkbook.Saved = True ' Realize this is redundant based on false on the next line.

ThisWorkbook.Close False

End If

End Sub

All of our product sheets are protected by a block of code attached to the Workbook_BeforeClose event:

Only allow a save if privileged, otherwise mark the workbook as saved

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next

If ThisWorkbook.Saved Then Exit Sub

' If this user is not allowed to save, then cancel the save

CM_Settings = LoadRegistrySettings

If (CM_Settings And 4096) = 0 Then

Me.Saved = True

Else

   ' If the workbook is saveable, then turn on the image to protect the data.

   ' saving the workbook triggers the Workbook_BeforeSave which shows the veil

   Me.Save

End If

End Sub

This works fine on the PC. On the Mac, the workbook does not close. I can see the button fire, and when it gets to the point where the workbook is to be closed, nothing happens. However, when I put a break point on the first line of the Workbook_BeforeClose, I can briefly see it reach the breakpoint (the line turns yellow), and then the workbook immediately closes. A breakpoint anywhere in this routine causes the same behaviour, the close works correctly. No breakpoint, no close. Commenting out the Resume Next has no effect. The only thing that will cause the work book to close, is to put a break point on on the On Error Resume Next in the code. If the workbook.Saved=True is commented out, the Workbook_Close code will exit, at the first if statement, but the workbook will not close.

BTW, the reason that the spreadsheet is modified is that the Option Button that the user clicks, marks the sheet as modified. We don't seem to have a way around that. Previously, we were popping up a form on the PC, but that did not work on the MAC either.

Does anyone have any clues to this bizarre VBA behaviour???

thanks in advance!!,

Explanation / Answer

My experience has been when code on a Mac will only run properly when debugging breakpoints are inserted that indicates a timing problem between the VBA application and the OS. Try inserting a pause in the thread's execution and see if that helps.

Dim PauseTime, Start

PauseTime = 1 ' Set duration in seconds
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop

What probably needs to happen is to have the Stacy cleared and all running subs ended. So instead of an in line delay, use an on.time to run another sub to close the workbook.   

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.