Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > strange problem....


Reply
 
Thread Tools Display Modes
  #1  
Old 04-25-2012, 08:11 AM
iabbott iabbott is offline
Junior Contributor
 
Join Date: Jun 2010
Posts: 303
Default 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
Attached Images
File Type: png untitled.PNG (7.7 KB, 7 views)
Reply With Quote
  #2  
Old 04-25-2012, 10:28 AM
Gruff's Avatar
Gruff Gruff is offline
Bald Mountain Survivor

Super Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA
Posts: 6,353
Default

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.
Reply With Quote
  #3  
Old 04-26-2012, 01:19 AM
iabbott iabbott is offline
Junior Contributor
 
Join Date: Jun 2010
Posts: 303
Default

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
Reply With Quote
  #4  
Old 04-26-2012, 06:33 AM
iabbott iabbott is offline
Junior Contributor
 
Join Date: Jun 2010
Posts: 303
Default

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...
Reply With Quote
  #5  
Old 04-26-2012, 08:51 AM
Gruff's Avatar
Gruff Gruff is offline
Bald Mountain Survivor

Super Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA
Posts: 6,353
Default

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
Reply With Quote
  #6  
Old 04-26-2012, 08:59 AM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,864
Default

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?
__________________
Use of a mobile device to post here is problematic, at best, and perhaps negligent, at worst. I'll try to keep it to a minimum.
Reply With Quote
  #7  
Old 04-26-2012, 09:25 AM
iabbott iabbott is offline
Junior Contributor
 
Join Date: Jun 2010
Posts: 303
Default

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..
Reply With Quote
  #8  
Old 04-26-2012, 03:20 PM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,864
Default

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.
__________________
Use of a mobile device to post here is problematic, at best, and perhaps negligent, at worst. I'll try to keep it to a minimum.
Reply With Quote
  #9  
Old 04-27-2012, 01:13 AM
iabbott iabbott is offline
Junior Contributor
 
Join Date: Jun 2010
Posts: 303
Default

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
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->