I wrote a code that is suppose to save the workbook every time a change is made but so far I have been unsuccessful at getting it to work. The workbook contians 7 worksheets.
This is the code I wrote to try and accomplish this task ....
Trying to design a check that makes a user insert an integer between 0 and 90. So far I can use this:
View 3 Replies View RelatedI have a workbook that is shared between 10 users and this work book is known as Master, what I don't want to happen is for one of the users to over write and save it. So is there a way one not allowing the user to save but allow the user to save as.
View 2 Replies View RelatedI have a workbook, "A", that while open I need to be able to detect any type of activity within other open Excel workbooks "B...Z" that are simultaneously open.
I understand that I would need to use the Class Module with a Public WithEvents function but I am stuck here.
All sheets are basically the same except some minor values, names and addresses. There is one page per sheet and all pages are in portrait format. The print preview shows all the pages in order but when I print it, it makes multiple print jobs of 1 to 2 pages each and prints them all out of order. I don't know why it is splitting up the workbook or why it changes the order. I print and reorder these weekly, which is a major pain. It comes out in the same order each time but it is the wrong order.
View 1 Replies View RelatedI have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
View 4 Replies View RelatedI have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
View 6 Replies View RelatedI have written a sub to open a txt file, but I want to then save it as Excel, with a filename (a variable). The variable is public, and is set by other subs. I have added the standard "save as..." button to my custom toolbar to use for this and the following code (taken from previous thread)
Public Sub SaveFile(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
Application.EnableEvents = False
Application.Dialogs(xlDialogSaveAs).Show MyVariable, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Cancel = True
Application.EnableEvents = True
End If
End Sub
But it comes up with the original filename in the save as dialogue and txt file type. Any help would be great, this is used by inexperienced users and I don't want the original file saved as it's prevoius name and type
I have a user form designed and now I would like to be able to program a Command Button to "SAVE" and when it is clicked, I would like to save the results of my fields to another worksheet. I have a defined range that I would like to have the inserted row into?
View 2 Replies View RelatedIs there a way for me to save the values inside a userform to public variables when a user closes the form?
View 9 Replies View Relatedi try to write a code to pop up input box with browse button to select the file path
the code without browse button is workin corectly
Private Sub CommandButton3_Click()
Dim N As Integer
Dim Fname As String
Fname = InputBox("Please enter the file name and path to save the output", "enter file name")
Sheets( Array(Sheet2.Name, Sheet3.Name)).Copy
ActiveWorkbook.SaveAs Filename:=Fname
ActiveWorkbook.Close
End Sub
Need to add browse button to make it easier than writting the file path
i have this code to run autofilter in protected sheet2, assigned to a check Box,
but it gives me this Msg when i run the code by checking the check Box: File in use.
The file is locked for editing by the (my name). However, when i excute the code by F5 in the VBE window, it works fine also when i excute the code from Worksheet_Activate , it works fine
Sub t()
With Sheet2
. Protect "0", , , , userinterfaceonly:=True, Allowautofilter:=True
.AutoFilterMode = False
.Range("A5:DT5").AutoFilter
End With
End Sub
We have an excel file which is made every thursday for comparison purposes etc. I've made an macro (excel 2003) which - on start - asks via an inputbox for the extension in date format for saving purposes. Later on, the macro will search for the same file but from last week to open it and copy/paste a 'comments' column into this new one. All went well, everybody happy. However it may be well possible that file is made and saved but with an extra extension: example:
dir to save = C:file
file when starts runs inputbox "Please enter a date (e.g. 2008-06-20)"
The macro records this string as mydate:
Sub Start()
Dim AnyString
Dim MyStr
Dim DirString
Dim mynum 'As String
Dim resp As Long
Dim get_mynum
Dim mydate
'Define extension for the file name to be saved and the correct path (dir) where this file will be stored.
get_mynum:
mynum = Application.InputBox("Enter the filename's extension to save in yyyy-mm-dd (e.g. 2008-06-30)", vbOKCancel)
If mynum = False Then
msgbox ("You do not want to continue? Ok, programm stopped")
Exit Sub
ElseIf mynum = "" Then..............
I want to combine the following two procedures, but I can’t figure out how . The purpose of the procedures is to warn the user to save data before importing and overwriting data that was already there.
Sub Is_sheet_empty()
If Range("your range") = vbNullString Then
Call Add_New_Data
Else: Call Sheet_Not_Empty
End If
End Sub
Sub Sheet_Not_Empty ()
Dim Response As VbMsgBoxResult
Response = MsgBox("Do you want to replace this data?", vbYesNo + vbQuestion)
If Response = vbYes Then
Call Add_New_Data
Else: Response = vbNo
‘Do Nothing
End If
End Sub
I have a update/Save button on a spreadsheet. So when clicked it updates another sheet and saves the workbook.
how can i have it run the macro when they don't click the button and do
File -> Save.
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] ........
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]....
Application.Dialogs(xlDialogSaveAs).Show
I am using above code and I want to find out whether the user pressed the save button or the cancel button in order to take the next action.
I've recorded this code and am looking to include it in a button. I would like for when a user clicks the button assinged macro that the active sheet is cloned and saved as a CSV file. The user should be prompted before saving on where (file location) they'd like to save the file.
Sub CloneWorksheet()
'
' CloneWorksheet Macro
'
'
Sheets("SDW&Customer Workshop scheduled").Select
Sheets("SDW&Customer Workshop scheduled").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings1167916My DocumentsTestBook1.csv", FileFormat:= _
xlCSV, CreateBackup:=False
End Sub
I have two workbooks open. I need to "Save As" and close workbook 2 with a file name from a cell in Workbook 1. The macro is running from workbook 1.
I'm guessing a change in the last line. I don't know what Dim means either.
Dim FName As String
Dim FPath As String
FPath = "G:"
FName = Sheets("sheet 1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
Is there any way that multiple user are using different excel based user form and update data using that. Is there a way that all the user save the data directly on the main database?
View 6 Replies View Relatedis it possible to work on a workbook lets call it 123
once macro button is pressed it opens workbook abc
then saves it as what ever is in cell a1 to a directory?
the following scenario.
I am using the following code to save a workbook as a macro enable workbook on the selected path as today's date. But it is being saved as a macro free workbook.
Below is the code in module:
Sub save_file()
Dim tDate As String
Dim FileSaveName As String
Dim fPath As String
tDate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-yyyy")
'FileSaveName = Application.GetSaveAsFilename(InitialFileName:=tDate, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
[Code]...
I have a spreadsheet that data gets exported to from a software program. There is information such as customer name, part number, inquiry number, etc that I'd like to use to develop a log of all my estimates I create for parts. I'd like excel to take the cells I select and insert them into certain cells in the other workbook and automatically save that workbook. This will create a log of all my estimates where I can then link the full estimate to the log. The information will have to go in the next available row. Is there any way I can accomplish this without having to open the other workbook and save the work book each time?
View 1 Replies View RelatedNeed VBA code to save column J of open workbook called LmbcAcctsPayable.xls, Sheet1, to same workbook & sheet called LmbcAcctsPayable.xls, Sheet1, when save is not selected.
Reason: The workbook is usually not saved because it is more of a templet with names in column A. Column J is updated with a date when a row is used so I need the column J to copy automatically to the blank templet when exiting without saving (using ThisWorkbook.Save = True in Auto_Close). That way, rows not being used for a long period of time can be periodically eleminated.
Following link explains how can format negative percentages.
[URL]....
Following code makes negative percentages red, positive percentages green.
[Code].....
I want to make; negative percentages red, positive percentages green, zero percentages blue, ---------->This is my question.
Attached File : Color Format.xlsm
I have read through numerous posts, but cannot get one thing to work. I am using Excel 2003, and am trying to get a calendar to pop up, to select a date, and then return that date to Cell B2.
I was successful at making the calendar pop up by the following methods:
1. Added a button to the toolbar, and assigned the Calendar Macro to it.
2. Using VB, added a sub-menu item to the right-click (context) menu, using the code below.
Private Sub Workbook_Open()
Application.OnKey "+^{C}", "Module1.Macro2"
End Sub
However, my goal is to make the calendar appear when I click on cell B2.
I tried using the following code, but when I click on B2 nothing happens.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$2" Then UserForm1.Show
End Sub
I also tried:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
With Worksheets(ActiveCell.Worksheet.Name)
If ActiveCell.Column = 2 Then
If ActiveCell.Row = 1 Then
UserForm1.Show
End If
End If
End With
End Sub
I'm helping out a community that puts out a role playing game by making a character sheet for their gaming system. It's normally a pen-and-paper type affair, but a few people have reached out to me and asked me to make it. Now, one thing that I really thought would be nifty would be to make it persistent. I know of the Excel basics, creating basic formulas and the such, but this is where I hit a brick wall.
For the character sheet, they normally have basic attributes on a 1-20 scale. These attributes are accomanied by a bonus. The only thing is those, while these bonuses go across a linear path (For attribute 1, it's -30, for 2 its -20, for 3 its -10, for 4 its -5 for 5 its 0....), I cant seem to figure out how to create some kind of If/Then formula for it....
Heres my example:
Brad makes a character that has a score of 9 for one of his attributes. The bonus given to someone with a 9 is a +10. What I'm looking to do is create a table that sees that 'If (c9)=9, Then (d9)=+10'...
And my second related issue...
Is there any possible way to do the above mentioned formula in relation to a drop down selection bar? It's the same thing here; To break it down simply, It would be excellent if there was a way for it to say If (a10)=Warrior, then (c10)= +10.
See attached document. In cell G4 i need a format which will make the cell green, amber or red depending on the values in Cells I7 to I9.
3 Greens = Cell G4 to be green
2 Greens, 1 Amber = Cell G4 to be amber
2 Greens, 1 Red = Cell G4 to be red
I want to set a conditional formatting rule that makes a cell red or green depending on if the value in the cell is larger or smaller than the value in another cell +2% or -3%. If neither of these conditions are met no formatting should be applied..
a) In the cell (R104) where the condition is set I have the following formula:
=if(BP104=0;"";BQ104/BP104) (the values in BP104 and BQ104 is retrieved from a database)
b) The conditions I've set in R104 are the following
1) =isblank($R$104) (condition: "nothing")
2) =$R$104<$R$106-0,02 (condition: "red")
3) =$R$104>$R$106+0,03 (condition: "green")
c) In cell R106 the value there is a value of 90%
The conditions seem to work fine when the value in R104 is higher or lower than the conditions set in 2 and 3 above but when the value is blank in R104 the cell goes green, which it shouldn't.