 |
04-25-2012, 08:11 AM
|
|
Junior Contributor
|
|
Join Date: Jun 2010
Posts: 270
|
|
strange problem....
|
I've attached a picture of a problem I'm having:
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
Ideas?
I am using Excel 2002 / VB 6.3
|
|
04-25-2012, 10:28 AM
|
 |
Bald Mountain Survivor
Super Moderator
* Expert *
|
|
Join Date: Aug 2003
Location: Oregon, USA
Posts: 5,876
|
|
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.
Code:
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
~T
Last edited by Gruff; 04-25-2012 at 10:35 AM.
|
04-26-2012, 01:19 AM
|
|
Junior Contributor
|
|
Join Date: Jun 2010
Posts: 270
|
|
|
The code is in the Workbook_BeforeSave code and works perfectly fine when I manually save
When my form hides, the module that called the form then saves the workbook
This calls the Workbook_BeforeSave code, but then this problem happens
I can save manually straight after, without changing anything else on the workbook, and it works fine
|
|
04-26-2012, 06:33 AM
|
|
Junior Contributor
|
|
Join Date: Jun 2010
Posts: 270
|
|
|
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...
|
|
04-26-2012, 08:51 AM
|
|
Bald Mountain Survivor
Super Moderator
* Expert *
|
|
Join Date: Aug 2003
Location: Oregon, USA
Posts: 5,876
|
|
Have you tried this?
Code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Your code here.
DoEvents
End Sub
|
__________________
Burn the land and boil the sea
You can't take the sky from me
~T
|
04-26-2012, 08:59 AM
|
 |
Multi-Technologist
Super Moderator
* Expert *
|
|
Join Date: May 2004
Location: Michigan
Posts: 3,734
|
|
|
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?
|
__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb
|
04-26-2012, 09:25 AM
|
|
Junior Contributor
|
|
Join Date: Jun 2010
Posts: 270
|
|
|
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..
|
|
04-26-2012, 03:20 PM
|
|
Multi-Technologist
Super Moderator
* Expert *
|
|
Join Date: May 2004
Location: Michigan
Posts: 3,734
|
|
|
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.
|
__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb
|
04-27-2012, 01:13 AM
|
|
Junior Contributor
|
|
Join Date: Jun 2010
Posts: 270
|
|
|
Thanks for getting back to me, I'll remember that for the future
This workaround was pretty much what I had put in place, it's good to see it confirmed as the 'correct' method
Thanks to all
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
