Before Save Event Error
Sep 21, 2006
Here is my existing code...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application. ScreenUpdating=False
' Cancel user prompted save procedure
Cancel = True
' Code removed... changes some values and settings before saving
ThisWorkbook.Save
' Code removed... return the original values and settings
Application.ScreenUpdating=True
End Sub
For some reason, the workbook is not being saved when the above code is executed. It seems the problem lies with "ThisWorkbook.Save"... in that it just isn't saving! If I delete "Cancel=True", the workbook will save (obviously!).
I have spent an hour trying to work this out and have come to the conclusion that it is something other than my code... has anyone experienced this problem before?
View 9 Replies
ADVERTISEMENT
Apr 29, 2008
I want to execute as the user saves the workbook. I want to unhide the rows that may have been hidden during use, on the save. I would prefer it to just happen with no interaction with the user. They save the the book and without them even knowing the rows are unhidden and the file saves. The code it self works as I want it to, I added to a command button with no problems as soon as I add it to the before save in the Thisworkbook it will not even work even if I just try to step in.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Worksheets("Prelims").Range("A11:A511").EntireRow.Hidden = False
Worksheets("Elecs").Range("A11:A1261").EntireRow.Hidden = False
Worksheets("Civils").Range("A11:A5011").EntireRow.Hidden = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Worksheets("Civils").Select
End Sub
View 9 Replies
View Related
Jul 8, 2009
I need a before save event macro in excel that will check if sheet1 cell ref A25 = zero. If it isnt zero I want a msgbox to say "Does Not Equal Zero" to pop up, if it is zero then I do not want a msgbox displayed. In both instances I still want the file to be saved.
View 5 Replies
View Related
Jan 16, 2007
I need to use the BeforeClose event to save the workbook with the same name when a user closes it. (82 users and they all seem to want to give it their own name.) I have the following code in the BeforeClose event but would like to eliminate the prompt "this file already exists, do you want to replace it" I have tried using the Application.DisplayAlerts = False but this seems to stop the workbook from saving.
I have a public demention called wbName and is set to the workbook name in an outo open macro
Public wbName
Public Sub auto_open()
wbName = ActiveWorkbook.Name
UserForm2.Show
MsgBox "PLEASE do Not insert rows/columns or enter calculations" _
& Chr(10) & " " _
& Chr(10) & "Enter Only Account Name, Date, and Corresponding_ Calls/Details", vbCritical, "Caution"
End Sub
View 6 Replies
View Related
Aug 30, 2009
I need my macro to run when the spreadsheet is Saved. Is that process written in the macro code or is there an existing event control in Excel to accomplish this?
Running Excel 2007. I tried searching the forum & found one post that ran the macro on open, but couldn't set that up to work on save.
View 2 Replies
View Related
Jan 5, 2014
I have a command button code on a sheet that checks if all the cells in a table have been completed before saving the worksheet. If they're not all complete a userform message box pops up with a reminder then returns to the sheet without saving. I want to be able to call this from the 'BeforeClose' event as well however, even though it still does what it's supposed to do, after the userform message box has displayed and been unloaded then it still pops up the Save Dialog box.
I'm struggling to suppress the Save Dialog box and return to the sheet.
View 1 Replies
View Related
Oct 28, 2009
I have many worksheets in a workbook that need to be saved if a user changesanything on them.
These sheet names all end in "....SD" and the code needs to only run on those sheets. I have learned alot from the forum but not enough, just yet . . This is what I have so far:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sht As Object
For Each sht In ThisWorkbook.Sheets
If LCase(Right(ws.Name, 2)) = "sd" Then
MsgBox(Prompt:="You must save changes. Save now?", Buttons:=vbYesNo) = vbYes Then ThisWorkbook.Save
End Sub
It doesn't like the 2 "Then's". (Don't laugh - I'm trying.)
View 8 Replies
View Related
Jul 22, 2009
In the situation where I shut down my model (i.e. not just press Ctrl+S, but shut it down), the following code saves the model twice. I only want it to save once. I really can't see why it saves a second time.
NB: I know if you set Cancel to True in the BeforeSave event, it should prevent the model from saving after the event, but even when set to true in the below code, it still saves...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True 'Given we shut model, this public bIsClosing is set to true...
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Dim wsSht As Worksheet
Application. ScreenUpdating = 0
Splash.Visible = True 'Splash is codename for warning spreadsheet, this is left
'visible while all other sheets are to be hidden.......................
View 9 Replies
View Related
Oct 10, 2011
I have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.
The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.
Code:
Private Sub cmdImportData_Click()
Dim sFName As String
'On Error Resume Next
PrepData
CopyData
FormatColumns
'prompt the user to save the file in "*.xls" format
sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls")
End Sub
View 3 Replies
View Related
Dec 9, 2009
I was wondering what I can add so this will not error when I delete the contents of the target range,
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("C11:C29, E11:E29")) Is Nothing Then
If Target.Cells.CountLarge > 2 Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target)
end sub
View 9 Replies
View Related
Oct 28, 2006
I'm using the following code in "ThisWorkbook" of a couple of different workbooks, and the problem occurs consistently with the code use regardless of which workbook...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close False
End Sub
The code itself does exactly as it should (not save changes when closing), but shortly after the workbook has closed, any other open workbooks will freeze, then give a popup message saying an error has occurred and a log is being generated, and EXCEL shuts down (loosing any other work)...
I have searched my HDD and only found 4 .log files that reference dates that coincide with the errors, although the times don't match(?)...
Originally Posted by WinMgmt.log
(Fri Oct 27 08:12:29 2006) : core is being shut down by WinMgmt.exe, it returned 0x0
Originally Posted by wmiadap.log
(Fri Oct 27 08:17:54 2006) : Performance library winspool.drv will be ignored as it was previously disabled (WbemAdapStatus = -1).
Originally Posted by wbemcore.log
(Fri Oct 27 08:47:10 2006) : Core physically unloaded!
Originally Posted by gotomon.log
Log file started at Fri, 27 Oct 2006 08:17:46 Eastern Daylight Time
0:00:00 i: G2EnumPortsCore() -- start -- level=1 pPorts=0x0
0:00:00 i: G2EnumPortsCore() buffer available:0 buffer needed:24
0:00:00 i: G2EnumPortsCore() -- leaving early --
0:00:00 i: G2EnumPortsCore() -- start -- level=1 pPorts=0xc55980
0:00:00 i: G2EnumPortsCore() buffer available:24 buffer needed:24
0:00:00 i: G2EnumPortsCore() -- leave --
I had been using;
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Save True
End Sub
Which had the same error... when I changed it to Close, I thought it had been fixed, but it was just that the delay is longer before the freeze.
View 4 Replies
View Related
Feb 21, 2013
Have some code in the workbook open event, which when I run when the workbook is already open works fine, however when triggered when the workbook is actually opened it fails on the first line.
Code:
Private Sub Workbook_Open()
Dim lngLastRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim X As Long
Dim Y As Long
'Define Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Sheets("Sheet1").Activate
View 2 Replies
View Related
Nov 13, 2008
I'm using a macro that picks up on changes in a active cell.. This is the code
HTML Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Value <> "" Then
ThisWorkbook.Sheets("Log").Cells(Target.Row, 2).Formula = "=Today()"
End If
End If
End Sub
There's a problem with this code, when select from a range and hit delete, i get a error mismatch. I want to have the macro ignore when i select active cell range of greater then 1.
View 2 Replies
View Related
Jan 3, 2013
The following code works fine, but when I put the code in an ActiveX Combobox Change Event it gives a run-time error 1004. ("Select Method of Range class failed")The error occurs on the following line
Code:
Worksheets("SAVED").Range("A" & l).Select
Code:
Dim l As Long
Application.ScreenUpdating = False
l = Application.WorksheetFunction.Match(Worksheets("DATA").Range("O34"), Worksheets("SAVED").Range("A1:A10000"), 0)
Worksheets("SAVED").Activate
'ROSTER===
Worksheets("SAVED").Range("A" & l).Select
Selection.Resize(1, 739).Offset(1, 2).Copy
[code]......
View 2 Replies
View Related
Jul 10, 2013
Using Excel 2010. I have a ComboBox (Not ActiveX) on a worksheet
I'm trying to add an event so that when user click on a choice a macro is fired using the selected item as citeria for a filter When I click on the drop down and make a selection I receive this error msg:
Cannot find the macro "cboUserSelection_Change" The macro may not be available in this workbook or all macros may be disabled
I also tried _AfterUpdate - similar error message.
I added the Combo Box code to the Worksheet code, not a standard module
I was able to run a quick test macro so I do not believe "all macros are disabled"
View 1 Replies
View Related
May 13, 2007
This is my
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then
If Intersect(Target, Range("F6:F6000")) Is Nothing Or Intersect(Target, Range("F6:F3000")) = "" Then
If Intersect(Target, Range("I6:I6000")) Is Nothing Or Intersect(Target, Range("I6:I6000")) = "" Then
Exit Sub
Else
Do Something......
End If
End If
End If
End Sub
when excution comes to this line
If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then
a run time error (91) appears, it tells object variable or with block variable not set.
View 9 Replies
View Related
Nov 1, 2006
When I paste data in Sheet2 in the attached CF Example2 file (Just by Selecting A1:L18 Cutting it and Pasting in the same place), the action of the VBA code in the Thisworkbook is correct but I am getting the 1004 Error Message from the VBA debugger saying "Run-time Error '1004': Application-defined or object-defined error
View 2 Replies
View Related
Sep 7, 2007
I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.
The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.
The code is below;
Private Sub Workbook_Open()
DoEvents
Dim Res1 As VbMsgBoxResult
Dim GovRng As Range
For Each GovRng In Sheets("Map").Range("GovernanceMembers")
If GovRng.Value = Application.UserName Then Goto 111
Next GovRng
Exit Sub
View 6 Replies
View Related
Aug 16, 2014
I got an error message that I cannot save while debugging. But I have repeatedly hit the stop icon to stop debugging!
I have minimized all the windows (except the spreadsheet as it does not allow me select it and just beeps) to make sure there are no hidden message boxes.
I do not want to force exit and lose my work but I cannot get out of debug mode for some reason!
View 6 Replies
View Related
Dec 7, 2009
I'm trying to create a macro that will take a worksheet from an Excel workbook, and save it as a text file, with the name determined from user input, and the location being the desktop of the currnet user's computer.
First I use a function, (provided by RoyUK), to get the name of the current desktop, then I try to use that name, along with my user input, so save the file. Obviously, it's not working, or I wouldn't be here. I get a "Run-time error '1004': The file could nto be accessed". I think it's just a syntax issue with the file name, because debug takes me to the "SaveAs" portion of the macro.
View 2 Replies
View Related
Jan 12, 2010
I have got the code below, I am trying to save as file name with it ending with upload, however I am getting run time error 91,
'Worksheet save as upload name
Dim Wk As Workbook
ActiveWorkbook.SaveAs Filename:=FileDrive & Wk.Name & "Uploads" & ".xls", _
FileFormat:=xlNormal
View 9 Replies
View Related
Feb 12, 2014
I am trying to take the template I have created and after the information is entered, if all required is not filled in, it will highlight the cells that need filled in. I get a debug error on
VB:
Cell.Interior.ColorIndex = 6
And the file does not save elsewhere. It goes into never never land. Here is my whole code:
VB:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Start As Boolean
[Code]....
View 5 Replies
View Related
Jun 13, 2008
When I use this code in the workbook I want it gets as far as creating a new workbook and pasting the data, but then an error comes up saying nothing but "400", and does not complete the saving part.
Private Sub Workbook_Open()
Sheets("Open").Activate
End Sub
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet
myPath = "K:3. DesignSupply OrdersLog"
For Each sht In ThisWorkbook.Worksheets
If sht.Range("D1").Value "" Then
sht.Copy
With ActiveWorkbook
.SaveAs myPath & ActiveSheet.Range("G11").Value & ".xlsx"
.Close
End With
End If
Next sht...................
View 9 Replies
View Related
May 12, 2009
I've got some code that reformats a file that a supplier sends us. They seem to randomly change the structure, so I'm trying to make the code a bit more robust.
Part of the code uses MATCH to look for specific column headings, so I've got some error handling around this of the format:
On Error GoTo NoIncCol
Line using MATCH
On Error GoTo Exiting
where Exiting is my standard error handling (Close without Save) and NoIncCol is the same, but with an error message saying that the term the code was trying to MATCH can't be found. This works well for me.
In another part of the code, I need to check for another column heading, which so far has turned up in two different forms. At the moment I'm doing this as follows:
With Sheets("Sheet1").Range("1:1")
Set rFound = .Find("Term1")
If Not rFound Is Nothing Then
a = "Term1"
Else
This feels a bit clunky, and I don't really like having what is essentially error-handling in the middle of my code. But the only other way I can think of to do it is to use a GoTo, and I believe that's not really considered good practice either.
View 9 Replies
View Related
Mar 1, 2007
I need to:Turn off "save" and "save as" command barsPrompt user for a unique filename Save file to a specific directory on the network common to all "p:dataprc"Requery user if filename exists and if they DON'T want to overwrite.Return to worksheet (there is only one) if the user cancels from the message box.Turn commandbars back on The macro runs from an on screen button
I get bug errors on NO or CANCEL when clicked in the message box. This is my current
Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False
End Sub
Sub SaveMe()
'
' Keyboard Shortcut: Ctrl+z
'
Dim BaseDir As String
Dim NewName As String
BaseDir = "p:dataprc" ...........
View 7 Replies
View Related
Apr 18, 2009
I have a command button that runs a save macro to which I added cell validation the message box comes up and what I want it to do is go to the cell that is blank
View 2 Replies
View Related
Jan 23, 2010
I am trying to run a macro that will export the 'Results' WS & ask the user where he/she wants the .xls to be saved, though when I click 'Save' nothing happens.
In additons in the save as part it has the WB's name(and full extention) is there a way to make this blank or to have something in it? I.e the WS's name?
View 10 Replies
View Related
Nov 13, 2008
I'm trying to protect cells based on what's input in column E. Here's what I tried, but it doesn't work. It's giving me a "Type Mismatch" error on the word "Annuity" where it appears first.
View 2 Replies
View Related
Mar 30, 2014
I am executing code in a workbook that has been working successfully previously.
Suddenly I get a run time 1004 error in any macro in the workbook that tries to execute a activeworkbook.save command?
View 1 Replies
View Related
Jul 21, 2013
Im struggling to get a workbook to a specific location. I have copied 3 worksheets to a new workbook, which im trying to save to an archive folder. Here is my code.
Code:
Sub atest()
Dim strFileName As String
Dim Archivepath As String
[Code]....
The problem is i keep getting an error (runtime 1004) saying the file could not be found?? well, im trying to create it !..
View 2 Replies
View Related
