Hello everyone,
I have an unexpected problem that i hope some of you can help me with.
I have recently made an Excel Workbook with some Makros inside, to help some of my coworkers plan and log their daily activities,
My Makros, which are placed in the entire workbook, not in modules or at single sheets, looks like this:
Private Sub Workbook_Open()
If (Range("CC156") <> (Range("CA156"))) Then
Call NewWeek
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets(1).Activate
ActiveWorkbook.Save
End Sub
In short, the first makro checks if we're entered a new week when the workbook is opened, and if its the case, then it calls another makro called NewWeek, which creates a new sheet for them to write notes in, and names that sheet (Week 41 etc) and so on,
For this to work i need to make sure that the workbook is always closed on the most recent sheet, so it will check if the most recent sheet week name is matched by the current week, otherwise it would keep making new sheets every time the workbook is opened, if the users close it down while working inside earlier weeks like week 38.
This is the reason for the last part of the shown makro above.
Now to the problem, this concept worked perfectly for me during test phase, and once i was ready to release the workbook, i shared the workbook for all of our sales personal to use, but once i decided to share the workbook, the Workbook_BeforeClose simply stops being executed when anyone closes the workbook(at most it executes only onces, the first time anyone closes it, but then never again), this results in multiple episodes where people would close the workbook down while on earlyer week sheets, causing the workbook to create new sheets every time its opened, the second i unshared the workbook, all of my makros would run perfectly once again. So why does it disable this one importent makro once the file is shared? and how can i fix it?
If the problem is simply an issue with shared workbooks, and cannot be helped at all, maybe i should look into another way to check if the latest sheet was created in the current week, no matter which sheet is currently active once the workbook is opened, any thoughts?
I have an unexpected problem that i hope some of you can help me with.
I have recently made an Excel Workbook with some Makros inside, to help some of my coworkers plan and log their daily activities,
My Makros, which are placed in the entire workbook, not in modules or at single sheets, looks like this:
Private Sub Workbook_Open()
If (Range("CC156") <> (Range("CA156"))) Then
Call NewWeek
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets(1).Activate
ActiveWorkbook.Save
End Sub
In short, the first makro checks if we're entered a new week when the workbook is opened, and if its the case, then it calls another makro called NewWeek, which creates a new sheet for them to write notes in, and names that sheet (Week 41 etc) and so on,
For this to work i need to make sure that the workbook is always closed on the most recent sheet, so it will check if the most recent sheet week name is matched by the current week, otherwise it would keep making new sheets every time the workbook is opened, if the users close it down while working inside earlier weeks like week 38.
This is the reason for the last part of the shown makro above.
Now to the problem, this concept worked perfectly for me during test phase, and once i was ready to release the workbook, i shared the workbook for all of our sales personal to use, but once i decided to share the workbook, the Workbook_BeforeClose simply stops being executed when anyone closes the workbook(at most it executes only onces, the first time anyone closes it, but then never again), this results in multiple episodes where people would close the workbook down while on earlyer week sheets, causing the workbook to create new sheets every time its opened, the second i unshared the workbook, all of my makros would run perfectly once again. So why does it disable this one importent makro once the file is shared? and how can i fix it?
If the problem is simply an issue with shared workbooks, and cannot be helped at all, maybe i should look into another way to check if the latest sheet was created in the current week, no matter which sheet is currently active once the workbook is opened, any thoughts?
