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?
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
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.
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
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.
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.
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.)
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.......................
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
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
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.
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
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.
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
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"
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.
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
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
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!
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.
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
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...................
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.
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" ...........
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
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?
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.
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 !..
