For some reason, my code won't change anything on any of the worksheets when it is run in the Workbook_BeforeSave code. If I just save the workbook, it seems to work fine, but when I set it to save when a form is closed, the BeforeClose code is called, but I can't hide/unhide columns or change the colour of cells
I can step through each line, but when it gets to a line to change something, nothing happens. The code carries on as though the worksheet had been modified, but it hasn't been
The image shows a line which should change the cell shading
Before, the cell shading = 35, the line of code should change it to 3, but after the cell is still 35
It is difficult to say with the little information you provide.
Offhand I would check to be sure you are specifiying the exact workbook and sheet you are working on and not just assuming the default Sheet is the correct one.
Dim oBook as workbook
Dim oSheet as worksheet
set oBook = Application.Documents.Open("C:\Book1.xls")
set oSheet = oBook.WorkSheets("Sheet1")
Dim C as Range
Set C = oSheet.Cells(1,1)
C.Interior.ColorIndex = 3
Also You might check to see it your worksheet is visible when the "before_Save" event is firing.
Can you move your code to some other event (Say a tempory button click) to see if it works as it should?
__________________ Burn the land and boil the sea
You can't take the sky from me
Also I should add that I don't think it is a problem with being on the wrong sheet:
It is reporting the correct shading number for the cell;
There are only 2 worksheets in the workbook;
This is the only workbook open;
The other worksheet has no shading in it
As I said, since it seems to work fine on a manual save I can't work out why it's not working...
I've heard some evidence from a colleague that suggests that the BeforeSave was not intended to be used to make those modifications, as those properties may be locked to read-only at that point. I haven't tried it myself. Why not use BeforeClose instead?
I want to use BeforeSave so that after the workbook is saved, the highlighting is updated for the user to see, this is a file that is altered regularly so the BeforeSave was the most logical point
As I said, it works fine on a manual save (everything in the BeforeSave code runs and works exactly as expected), it just seems to be when the userform is closed and the code tells the workbook to save that nothing is changed..
My colleagues assertion seems to be correct (with respect to code initiated .Save, as I've tested this now and see the issue). I've even tried Application.OnTime to defer the .Save... and failed to get the sheet to update the .ColorIndex.
Therefore, you will have to replicate the code you intend to run so that it also executes after the BeforeSave event returns (into the lines immediately after the ThisWorkbook.Save statement). I've tested that approach, and it works. You could wrap up that code into a Public Sub in a Module and reference the sub from both places.