Workbook Sheet Change Event Prompts Save
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
ADVERTISEMENT
Jan 26, 2007
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.
The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?
View 6 Replies
View Related
Oct 2, 2007
Workbook 1 has references to workbooks 2,3,4,5,6. When I click the top cross and choose Save All with Workbook 1 active, the deactivate event in workbook 6 runs to the end as it tries to close, but it can't close because workbook 1 references it. So nothing further happens until I click the top cross again. Then everything closes without any more prompts. Not good. If I could ensure workbook 1 closes first that should solve the problem. There may be another way of course.
I have attached 2 blank files to show the problem. test1 references test2. Open test1. It will open test2. Make a change to both files, activate test 1 and click the top cross, choose save all. Nothing happens usually.
View 4 Replies
View Related
Mar 27, 2009
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
View 9 Replies
View Related
Sep 19, 2009
I have a an Excel workbook (wb1) with 4 worksheets.
I also have a personal.xls that contains 4 worksheets.
wb1 has volatile functions.
I have calenders, dropdown lists, read from a file that is closed, etc..
When I open wb1, look at it (I do not make any changes by hand) then close it, it gives me the " Do you want to save?" prompt.
I have tried numerous codes to stop it and I can not get it.
I need it to give me the prompt if I have made a change and not give me the prompt if it is just a change from the volatile functions.
I cannot just tell it to save in the background because my boss does not want the date changed if it is just looked at.
The user opens numerous files when searching for a file that is almost what he needs, then he saves it with a new name and goes from there. Or he just opens numerous files to get ideas on how to make the new file.
The user cannot have to do anything to make it work.
I know that the code will need to be copied to any existing workbooks. I have the code to do that.
That would be done as the files are created to new ones. Also when he is starting from scratch the code would be in the template he uses. Eventually all of the files would be converted
Private Sub workbook_Open( )
Me.Saved = True
End Sub
Private Sub Workbook_Open()
ThisWorkbook.Saved = True
End Sub
View 9 Replies
View Related
Jul 21, 2009
Im trying to use an event change to change the sheet name based on a cell value, but my issue is how can I error trap if the sheet name is a duplicate? Here is what I have so far
Sub ChangeName()
On Error GoTo errhandler
Sheets(1).Name = Sheets(1).range("d10")
Exit Sub
errhandler:
MsgBox "sheet name is already exists"
End Sub
View 9 Replies
View Related
Dec 21, 2006
I have added a toolbar to an excel application that the company uses. I need this toolbar only to be accessed to this application and not any time that you open excel. I have used the following
Private Sub Workbook_Open()
Application.CommandBars("Rithin-Toolbar2006").Enabled = True
Application.CommandBars("Rithin-Toolbar2006").Visible = True
go_cover
End Sub
Private Sub Workbook_close()
Application.CommandBars("Rithin-Toolbar2006").Delete
Application.CommandBars("Rithin-Toolbar2006").Enabled = False
Application.CommandBars("Rithin-Toolbar2006").Visible = False
End Sub
This works but only If I accept yes when it prompts me to save changes to the workbook. Each time I open the workbook and close it, it prompts me to save changes to the workbook. I dont want to force a save on the workbook, meaning have code that will save the workbook before closing. I want it such that if the user opens the workbook, and makes no changes, he/she should just close it with no questions asked.
View 6 Replies
View Related
Sep 13, 2007
I have a macro which makes an renames a new sheet for me using a button, how can I copy macros currently found in only one sheet to this new sheet? Mainly used in formating and formula display.
View 5 Replies
View Related
Feb 2, 2009
I have a Combo Box on a Work Sheet that the user selects a subject from. When the Combo Box content changes I need it to run a macro. Ive tried putting this in the WorkSheet file for the sheet
View 3 Replies
View Related
Jan 16, 2007
I have the following code what it does, is when a user changes a cell it copies the row to another sheet along with the userstatus. Problem is if the user pastes more than one column of data into the sheet it copies the row more than once depending on the amount of columns the user pasted . I only want to copy the row once.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelRng As Range
If Not Intersect(Target, Range("A3:K30")) Is Nothing Then
Set SelRng = Target
Application. ScreenUpdating = False
For Each cell In SelRng
Range(Range("A" & (cell.Row)), Cells(cell.Row, Columns.Count).End(xlToLeft).Offset(0, -5)).Copy
Sheets("Tracking").Select
ActiveSheet.Cells(Rows.Count, 31).End(xlUp).Offset(1, -13).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 13).Value = ActiveWorkbook.UserStatus
Sheets("Engine 1").Select
Next cell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
End Sub
View 7 Replies
View Related
Jun 15, 2009
I need a quick code to where if a user attempts to open my workbook and they have any other workbooks open at the time that it prompts them with a yes no msgbox asking them to save before continuing. Really i just need the code to tell if there is another workbook open in excel.
View 3 Replies
View Related
Jun 27, 2008
why this code does not work when the worksheet is changed between range "B1:F5"?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:F5")) Is Nothing Then
With Range("B1:F5")
Cells(Target.Row, 7) = Cells(Target.Row, 6).Value + Cells(Target.Row, 5).Value
End With
End If
End Sub
View 3 Replies
View Related
Jun 17, 2014
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
View 2 Replies
View Related
May 28, 2013
Essentially I need to copy the first 8 cells in a row in one sheet (for example: A3:I3) when the word "Actuals" is entered into A3 from a drop down list. Then the copied data needs to be pasted to a another existing worksheet in the same workbook in the next available row. The data includes mostly values, but there is a formula in column H that creates a hyperlink out of the content in column G, friendly name in column I.
I am not stuck on the idea of having "Actuals" entered in column A as the trigger or change event and there will be times when a new copy/paste of the same data will need to be done more than once at a later date.
For further information, column B contains a serial number/productID number.
View 9 Replies
View Related
Jun 25, 2014
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
View 2 Replies
View Related
Jan 5, 2010
Is there a way to have a macro run every time a sheet in a workbook is accessed? Something similar to the workbook open event, but for worksheets.
View 2 Replies
View Related
May 8, 2009
In wrote a workbook in Excel 2003 that is causing me a problem I can not seem to figure out. The workbook consists of multiple worksheets that work using both VBA code or cell formulas. The worksheets are protected (though w/o a password) to prevent users from accidently altering a cell formula. When the code needs to write to a cell, it first unprotects the sheet, writes to the cell(s), and then reprotects the sheet.
Everything seems to work fine, often for several months. Then somehow something goes wrong. Specifically, after double-clicking on the ListBox in the worksheet “CRI Calculator” to pick a drug name, it will improperly present a dialog box (see attached screen capture 'Unprotect Sheet DialogBox.JPG') prompting the user for a password to unprotect the worksheet. Again, no password has ever been set; I have tried entering nothing, “password”, and "anonymous". Regardless of how you respond, an error code “Run-time error ‘1004’: The password you supplied is not correct…” occurs. If I press Debug, it takes me (ironically) to the following line of code
View 11 Replies
View Related
Apr 9, 2014
have a basic knowledge of VBA. The task I am trying to perform with VBA is to
1. Copy from sheet1 A1 to sheet2 E9
2. Copy from sheet1 B1 to sheet2 E11
3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls)
4. Copy from sheet1 A2 to sheet2 E9
5. Copy from sheet1 B2 to sheet2 E11
6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls)
7. Repeat the process down columns A and B to the end of the columns.
8. Columns A and B will end at the same time but the data and the last cell will change with each scan added to the columns.
This code will accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.
VB:
Sub MoveData()
sName = "temp"
Const csPath As String = "C:Documents and SettingsmcgaulcDesktopTestFile"
'MyName = ActiveWorkbook.Name
[Code].....
Here are some images of the data sheet and the saved sheet
Sheet1.jpg Sheet2.jpg Note: sheet2 in image is "temp" sheet in code
View 7 Replies
View Related
Aug 7, 2006
I have the following code, which works perfectly:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TotalDays As Integer
TotalDays = Range("C65536").End(xlUp).Row + 1
The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.
However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.
How do I add the following, to my previous code?
Range("E8").Value = Cells(7, 6) * 2.5
View 9 Replies
View Related
Nov 3, 2008
Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls
The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as
Report1_11-03-08.xls. Then just continue incrementing.
Sub CopySave()
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
View 5 Replies
View Related
May 17, 2005
How can I save only the active sheet of a workbook in VBA?
View 9 Replies
View Related
Jun 23, 2012
I have the below macro steps to save an excel sheet in PDF format in "C:UsersxxxxxDesktop" and with the name of the workbook.
But when the PDF report gets published in c: drive , the PDF file name has .xlsm added to it instead of just the file name.
I would like to know how to publish the file in PDF format just in the name of the workbook.
Find the below macro steps:
Sub Docsave()
Dim docname As String
docname = ThisWorkbook.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UsersxxxxxDesktop" & docname, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
View 1 Replies
View Related
May 5, 2014
I got a workbook with a database in sheet1. I filter that database and copy - paste values the results to a new sheet. Next, I save that new sheet to somewhere on my drive. The thing is that I want to go back to my original database, and continue filtering. But that workbook is no longer the "active workbook". This is how it works:
sub DifferentClients()
For Each FieldWorkBy In Array("Client1, Client2,...")
MyDatabase.AutoFilter Field:=CountryCode, Criteria1:=Array( _
[Code]....
View 3 Replies
View Related
Apr 2, 2009
I need to save a single sheet to a file (in a workbook of many sheets). I can't use the copy command and then save the new workbook because it truncates the longer info in some cells.
View 9 Replies
View Related
Dec 28, 2006
I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following
Sub savemeas()
Worksheets("data").Visible = True
Sheets("data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub
to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be
ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
View 4 Replies
View Related
Sep 11, 2007
I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB
View 3 Replies
View Related
Jun 30, 2008
I am in the process of setting up an Excel invoice. So far I have managed to implement the following:
On open: Incrementing invoice number
Cells cleared ready for next use
Private Sub Workbook_Open()
Sheets("Sales Invoice").Range("H2").Value = Sheets("Sales Invoice").Range("H2").Value + 1 .....................
There is only one task left I need to automate. On closing the document I would like a copy to be saved in a specific folder and be given the file name of a cell (B9). Obviously I would not want the 'saved copy' to retain the Incrementing invoice number or the clear cells function. Even if the invoice copy was exported as a flat jpeg that would be fine. I am using Office Exce 2003 (Sp2) - I have attached my invoice should anyone be kind enough to take a look.
View 3 Replies
View Related
May 20, 2006
As per the following code I'm trying to add a new sheet which should inclide the CHANGE Event Proc
Unfortunatelly, I get an error message (free translation from Hebrew):
====================
RunTime Error '1004':
"Programming entrance to the Visual Basic Project is not secured".
===========================================
Here is the code I used:
Sub AddSheetWithCode()
Dim StartLine As Long
Sheets.Add
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name).CodeModule
StartLine = .CreateEventProc("change", "worksheet") + 1
.InsertLines StartLine, _
"if not intersect(target,range(""a1:a10"")) is nothin then" & Chr(13) & _
"if target=10 then" & Chr(13) & _
"target.clearcontents" & Chr(13) & _
"msgbox (""Bla...."")" & Chr(13) & _
"end if" & Chr(13) & _
"end if" & Chr(13)
End With
Application.VBE.MainWindow.Visible = False
End Sub
how to eliminate the error.
View 8 Replies
View Related
Jan 28, 2014
Working with a macro enabled workbook that has many sheets what would be a good code that would save Sheet1 into a new MACRO enabled workbook so that the 'Buttons' (which are linked to macros) on this specific sheet will be the only macros that will be copied to the new workbook as opposed to the rest of the macros that are found in different sheets?
Here is what I g=have:
[Code] ......
View 11 Replies
View Related
Feb 28, 2014
I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.
I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.
Here is the one I was using, but it saves all tabs not just selected:
Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer
[Code].....
View 1 Replies
View Related
