Ask Your Question

Macro runs twice

asked 2016-03-10 03:32:55 +0100

UncleCrust gravatar image

updated 2016-03-10 16:42:46 +0100

I attached this macro:

Sub setPayPeriodDate
    Dim selection as Integer
    selection = MsgBox("",4 + 128,"Change to the next pay period?")

    If selection = 6 then
        Dim timeCard : timeCard = ThisComponent.Sheets.getByName("Chris") 'Note you need to substitute the name of your sheet if you have renamed it.
        Dim dateCell
        Dim currentDate As Date

        ' Get current pay period's start date
        dateCell = timeCard.getCellByPosition(0,2)
        currentDate = dateCell.getValue()

        ' Change date to beginning of next pay period
        dateCell.setValue(currentDate + 14)
    End If
End Sub

to the OnPrint event. When I initiate the print, the expected Yes/No dialogue pop-up appears. but clicking either button does not close the dialogue box. Clicking a button again closes the dialogue and prints the document. Problem is, clicking the 'Yes' button twice, updates the cell twice. Why?

edit retag flag offensive close merge delete


Edited post to properly isolate the code from the post. On another subject, what's your LO version ?

rautamiekka gravatar imagerautamiekka ( 2016-03-10 16:43:38 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-01-25 21:29:25 +0100

David M gravatar image

I had a similar issue in LO Base. In Base 4.0 Handbook, some events are reported to run twice. I had this problem, when attached an event to a control using code. Not sure why, but following workaround succed.

First check if the control already has an assigned macro to the event you want to trigger from.

If not, assign the event.

If the event is already there, do nothing.

The following code might serve as an example. I'm using Access2Base. But you get the idea. I made a Close button, and assigned the macro from the module itself.

'+++ The following sub is attached to the Open Document Event. Tools -->Customize-->Events
Private Sub MyForm_OpenDocument()
        Call setControlEvents 
End Sub

Private Sub cmdClose
'+++Simple macro to close a form
    DoCmd.mClose(acForm, "myForm",0)
End Sub

Private Sub setControlEvents
    Dim ocCmdClose As Object
        Set ocCmdClose = Application.Forms("myForm").Controls("myCloseControl")

    If Len(ocCmdClose.OnActionPerformed) = 0 Then
           '+++If this event on the control has no macro attached, then do it.
    ocCmdClose.OnActionPerformed = ""
    End If

    ocCmdClose = Nothing

End Sub

After doing this, the macro is called just once per event.

edit flag offensive delete link more


Sorry, but can't see this as being any kind of solution. In the specified case, a button to close a form, have done this many times & never experienced such a problem. Events which are triggered twice do so for a reason. Most of these are in dealing with records being loaded - into a table control for example. The true solution is to examine the events (using MRI or X-Ray for example) and determine which to adhere to and bypass the unwanted in the macro.

In proceeding with your solution, and provided some of the situations dealt with on forms, the Open Document process could be large and difficult at best to manage when changes in the system were needed. I would avoid this process completely!

Ratslinger gravatar imageRatslinger ( 2019-01-25 23:38:20 +0100 )edit

answered 2016-03-10 16:37:31 +0100

Ratslinger gravatar image

Hello, I've copied your routine and using a push button attached it to an Execute Action event. Performs exactly as it should. Not having worked with print events before, it leads me to believe that timing is an issue here with the event and your message box. I see nothing wrong with your code.

edit flag offensive delete link more


What's your LO version ?

rautamiekka gravatar imagerautamiekka ( 2016-03-10 16:43:47 +0100 )edit

Tested with both and

Ratslinger gravatar imageRatslinger ( 2016-03-10 17:05:46 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-03-10 03:32:55 +0100

Seen: 196 times

Last updated: Jan 25 '19