Run VBA Code When 'Checking-In'

[default_name]

Hey guys,

I am working on a workbook that is connected to a server. (The Check-In and Check-Out features are enabled)
Is there a way to run a few macros in the event that someone checks the file back in?

Initially I was using something like this to run some routines when the workbook was being closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call CopyExtra
    Call ResetData
End Sub

But then I realized that it would be better to run/call the routines when the workbook is being Checked In (not when it is being closed)
Here is my failed attempt:

Private Sub Workbook_Before.CheckIn(Cancel As Boolean)
    Call CopyExtra
    Call ResetData
End Sub

Some help with the syntax would be appreciated!

Thanks in advance for your help!

 

[Joe4]

Not really sure what you mean by "Check-In" and "Check-Out" features. Is that some custom stuff added to the workbook?

Note that all event procedures are pre-defined and must follow an exact naming format. They are not just something you can make up and expect to work automatically.
Also, all VBA code in Excel is dependent on the Excel file being open.

To see the list of Workbook event procedures available to you, go into the VB Editor, and double-click on the "ThisWorkbook" module.
Then, in the box above the editor window that says "(General)", click on the drop-down and select "Workbook".
Then, in the other box above the editor window that now says "Open", click on that drop-down and you can view all the "workbook event procedures" available to you, and you can select the one you want to give you the shell of the code.

Note that you CANNOT change anything in the pre-populated "Private Sub..." line of each event procedure. They must appear exactly as shown in order to run automatically. Any edits to that line, will cause it NOT to be recognized as event procedure code, and therefore it will NOT run automatically.

 

[default_name]

Not really sure what you mean by "Check-In" and "Check-Out" features. Is that some custom stuff added to the workbook?

The check-in button "checks in the working copy of the project from a local computer to the SharePoint document library, and sets the local project to read-only so that it cannot be edited locally."
See Here

In other words, this workbook is on a SharePoint shared server. Only one person can edit it at a time. When a user opens the workbook and checks it out (File>Check Out) then they have the ability to edit.
Once the user is finished making their edits/changes they can go to File>Check In and their changes are saved/uploaded to the shared server. The workbook remains open (as read-only) until the user decides to close it.

To see the list of Workbook event procedures available to you, go into the VB Editor, and double-click on the "ThisWorkbook" module.
Then, in the box above the editor window that says "(General)", click on the drop-down and select "Workbook".
Then, in the other box above the editor window that now says "Open", click on that drop-down and you can view all the "workbook event procedures" available to you, and you can select the one you want to give you the shell of the code.

Note that you CANNOT change anything in the pre-populated "Private Sub..." line of each event procedure. They must appear exactly as shown in order to run automatically. Any edits to that line, will cause it NOT to be recognized as event procedure code, and therefore it will NOT run automatically.

An option related to Check In is not in that dropdown list. Does that mean that it is not possible to do anything based on a check-in?

 

[Joe4]

An option related to Check In is not in that dropdown list. Does that mean that it is not possible to do anything based on a check-in?

Not in that manner. You can only use the pre-defined event procedures that are listed there, if you want automated code.

However, depending on how you want this to work, you may be able to come at it a different way, by using another event, and then using the "Workbook.CheckIn" method.
Information on that can be found here: Workbook.CheckIn method (Excel)

Unfortunately, I have never used SharePoint, so don't really have the ability to test anything or offer any other advice on it. Hopefully, you can do something with incorporating the information contained in the link above.