peakoverload
New Member
- Joined
- Aug 19, 2014
- Messages
- 10
I'm a real newbie when it comes to VBA and macros so apologies in advance if I'm using the wrong terminology in some of this.
I've created a workbook called a Job Sheet Generator. We use this to enter information about a particular job which when we click on a button, saves the workbook as a new one, which we call a tracking sheet and also updates a couple of other external workbooks.
Both the Job Sheet Generator and the Tracking Sheets it creates have several macros in them written in a VBA module. The macros do various things from resetting all cells, to performing more advanced time calculations and then further updating other external workbooks.
Both the Job Sheet Generator and the resulting Tracking Sheets are saved in xlsm format and all PC's on which these are used are set to always enable macros.
We are finding though that at random the macros are getting deleted at some point when the tracking sheet is updated and saved. No notification is shown on screen saying that this is happening and the first we know about it is when the next person open the tracking sheet and tries to click on one of the buttons only to be told that the macros are missing. When I look in VBA I see that the module object has been deleted.
Yesterday this happened on one file and I was able to look at all the versions of it that had been created each time the tracking sheet had been worked on.
The first version, the version first created by the job sheet generator was fine, all macros present
The next 7 versions as staff members kept updating the sheet with their progress on the particular job was fine too, all macros present
The 8th version though, although still in xlsm format, now didn't have any macros and the module object was deleted
I know that if the workbook is saved in xlsx format that will delete the macros but it's still in xlsm format so I can't workout how the module object is being deleted.
This has happened now on 5 separate workbooks in the past fortnight.
Does anyone know what could be causing this?
I've created a workbook called a Job Sheet Generator. We use this to enter information about a particular job which when we click on a button, saves the workbook as a new one, which we call a tracking sheet and also updates a couple of other external workbooks.
Both the Job Sheet Generator and the Tracking Sheets it creates have several macros in them written in a VBA module. The macros do various things from resetting all cells, to performing more advanced time calculations and then further updating other external workbooks.
Both the Job Sheet Generator and the resulting Tracking Sheets are saved in xlsm format and all PC's on which these are used are set to always enable macros.
We are finding though that at random the macros are getting deleted at some point when the tracking sheet is updated and saved. No notification is shown on screen saying that this is happening and the first we know about it is when the next person open the tracking sheet and tries to click on one of the buttons only to be told that the macros are missing. When I look in VBA I see that the module object has been deleted.
Yesterday this happened on one file and I was able to look at all the versions of it that had been created each time the tracking sheet had been worked on.
The first version, the version first created by the job sheet generator was fine, all macros present
The next 7 versions as staff members kept updating the sheet with their progress on the particular job was fine too, all macros present
The 8th version though, although still in xlsm format, now didn't have any macros and the module object was deleted
I know that if the workbook is saved in xlsx format that will delete the macros but it's still in xlsm format so I can't workout how the module object is being deleted.
This has happened now on 5 separate workbooks in the past fortnight.
Does anyone know what could be causing this?
