Whenever I save and close the attached, after selecting the NO on line 23, I lose all the buttons associated with my macros. If I save with everything expanded, no rows hidden, my buttons stay put.
View 1 RepliesI'm trying to close a workbook without saving it. But everytime I get the message box popupped twice.
These are the codes that I use:
VB:
Private Sub Workbook_BeforeClose _(Cancel As Boolean)
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[Code].....
I am trying to write a macro/program that will save a csv activeworkbook in xls and close it.
The workbook will always be a different name.
The files do not have an extension upon download and formatting.
ie stm0107 (Then they need to be saved as xls. Which is done manually at this time.)
Below are some trial and error attemtps. Thank you for your suggestions.
Sub CloseWorkbook()
Dim Filename As String
'Filename = Workbook.Name
MsgBox (ActiveWorkbook.Name)
Filename = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename = ActiveWorkbook.Name(".xls")
'Filechange = Replace(Filename, "", "xls")
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close Saved = True
End Sub
I have my Excel workbook open & have made changes. Now I close it and Excel prompts me if I want to save the changes. Is there a way to just close the workbook and automatically accept any changes without have the "Do you want to save changes" message appear?
What about if it is opened in read-only mode? Typically the workbook will not save any changes unless you save it off as another file name. If there is a solution to my question above, can it be enabled only when the workbook is opened in edit mode, or will it not auto-save changes over the file because Excel knows it was opened in read-only mode?
Need the VBA code to close a workbook, and not save it? I need it to open a workbook, run a macro, and close without saving. The code I have thus far is:
View 3 Replies View RelatedI have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.
Other cells in the row contain formulas and are locked and the sheet is password protected.
On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?
I have created a workbook for a quality check and it has about 50+ worksheets in the workbook, which contains data, formula,graphic & vba function. The size of the file is about 4890kb. It's works as planned, except, it takes about 1 mins ++ to load, save & even exit. Is there any suggestion to eliminate this issue, apart from creating a few workbook to accomodate the number of sheets/item in the project?
View 2 Replies View RelatedIf for any reason I completely close excel during the workday, then when I restart the application none of my macros are active. Even if I manually open the PERSONAL workbook that contains my macros. Any fixes other than restarting the computer?
View 12 Replies View RelatedLooking forward VBA coding for:
If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save.
Could be that one or more of these row cells are left blank by mistake.
An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.
Using Excel 2010.
I have created a toolbar with buttons that run macros. When the macro is assigned to that button the macro will run fine. However, when the file name of the excel workbook has changed (file relocated or name changed) then the macro (button in the toolbar) does not work and has to re-assigned in the toolbar. Is there anyway of getting around the problem of having to reassign the macro to the toolbar button whenever the file name/directory changes.
View 5 Replies View RelatedI am trying to come up with some code to loop through all worksheets in the workbook and remve assigned macros from any buttons on each sheet
My code below. I type btn. and reviewed the items offered by intellisense, but nothing seemed fitting.
Sub RemoveAssignedMacro()
Dim wb As Workbook
Dim btn As Shape
Dim sht As Worksheet
Set wb = ThisWorkbook
For Each sht In wb.Worksheets
For Each btn In sht.Shapes
btn.
Next btn
Next sht
End Sub
I managed to save workbook as values only by the help of this forum
Now is it possible to disable the commond buttons, when the workbook is saved as values only or can we delete the macro's.
I'd like to be able to just open the spreadsheet up and have the macros already saved in there. Is there a way to save these macros?
View 2 Replies View RelatedI have written a piece of VBA code which I want to assign to a button in the front end of excel, however once I assign it and then press the button to run the macro (which works) I am not able to then press it again incase I need to re run it?
View 7 Replies View RelatedI have a setup at the moment where I have a spreadsheet using a bunch of macros that are coded in a module attached to another spreadsheet. In both spreadsheets the macros are tied to Excel buttons placed within worksheets. When I copy the spreadsheets each month to new directories - they're used for some monthly reporting - I see the following behavior:
1/ The spreadsheet that contains the module with the code for the macros correctly updates the location of the macros and works OK.
2/ If I open the other spreadsheet and save it the macros appear in the tools/macro dialog as having changed location OK. However, if I try to use the macros by pressing a button the macros invoked are in the old location. If I check the assignment of the macros in this spreadsheet by right-clicking on a button, indeed the macros invoked are in the old location. This means that I have to go through and manually update for each button the macro invoked.
I conducted a survey and want to tabulate the results in excel. There is a lot of paper, so I want to list each answer on the spreadsheet and have a button next to it to tabulate the responses instead of doing it by hand. When I look at each survey, I want to click the button for the corresponding answer. I want to have excel tally/increment each response each type I click the button.
So what macro do I need to assign to each button in order to do this? Or can I just click on a cell and have it increment? Or what can I do to achieve this?
My attempts have given my circular reference errors, or if I do the iteration thing (tools, options, iterations), excel increments EVERYTHING in the spreadsheet by one instead of just one specific question.
how to save my macros to a spread sheet? It seems every time I close my sheet(s) (after saving???) and come back to them later, my macros are gone??
So I'm trying my hand at creating VBA buttons and functions that do the following.
Button 1 :
to add a column with every click. The button will create one extra column. So by design there will be at least 1 column. Example if the button is clicked twice it will create 3 columns. I just need a buffer col. (contains no data)
Button 2 :
to delete empty columns leaving only one empty buffer column. That is this button will purge empty columns except the buffer column. OR if ALL the columns are filled, Button2 will create the buffer column.
Eg. I click Button 1 three times. It creates four columns. I only fill two columns. When Button 2 is clicked it will delete all the empty colums except the buffer column. OR I fill in all four columns, there is no buffer column, when I click Button2 it will insert the buffer col.
Button 3 :
to add a row with every click. The button will create one extra row. So by design there will be at least 1 row. Example if the button is clicked twice it will create 3 rows. I just need a buffer row. (contains no data)
Button 4 :
to delete empty row leaving only one empty buffer row. That is this button will purge empty row except the buffer row. OR if ALL the columns are filled, Button4 will create the buffer column.
Eg. I click Button 3 three times. It creates four rows. I only fill two rows. When Button 4 is clicked it will delete all the empty rows except the buffer rows. OR I fill in all four rows, there is no buffer rows, when I click Button4 it will insert the buffer row.
In the sample file there are six columns filled with data. The 7th col is empty(the buffer column). The column after that calculates the average of data in filled columns. There are eight rows with data and the ninth row is the empty buffer row.
The "add" buttons must be able copy the format/formula of the column/rows BEFORE the buffer colums/rows and insert it before the buffer column/row.
I'd like to insert some radio buttons in a worksheet that activate a hyperlink to a web page and then be able to go back to where I started (ie the radio button)
I can link the button to a macro which was a recording of clicking on a hyperlink. The trouble is when I hit the return arrow to return to the sheet from the web page I go back to the location of the hyperlink not where I started from.
Is there a way to do this with the buttons.
If I can rearrange the sheet to allow for more narrow columns I may be able to just use the hyperlink friendly name argument and do it that way but I'm not so sure I can insert more narrow columns without messing up the layout of the whole sheet. With a button I can float several of them over an area regardless of the width of the columns.
The form is completed by the project leader, then they hit a Command Button and it's sent to the Senior Manager. After the Senior Manager looks at it and approves it, he then sends it on to Admin (me!) so the project can get set up on all of our systems.
We recently upgraded to Office 2007 (yes, we are a bit late!) and now the Macro that sends the form to Admin no longer works. An example of the error is as follows:
Cannot run the Macro "FY06-10 PROJECTS 19-Jan-10 08-32'EmailtoAdmin.EmailtoAdmin'. The macro may not be available in this workbook or all Macros may be disabled.
Here is the code that I use to send it to the Senior Manager:
Sub MailtoSM()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else.........
I noticed that my custom "Close without Saving" button stopped working. I checked the VBA editor and my Personal workbook was missing and all of my personal macros were gone. I restarted excel, same issue. Rebooted my machine and they re-appeared. Not sure how what happened, but at least the Personal workbook was not lost. I'm working off my company's network, so often I get the "Personal Workbook is locked for editing" message. I usually select the open as read-only file and go about my merry way. Perhaps I clicked something different on accident without paying attention.
Anyway, I digress, the custom macro button that I had in my ribbon still didn't work, so I removed it and tried again in both the ribbon and the quick access toolbar with no success. The Macro executes if I execute it manually or via the keyboard shortcut, but the ribbon/quick access toolbar buttons won't work. Frustrating, because I use this quite often. I've even tried writing a quick "dummy" macro to see if it was some sort of code error related to the macro, but got the same result. My "Close without saving" code is below (super basic code).
Sub Close_Without_Saving()'' Close_Without_Saving Macro' Closes active workbook without saving changes.'' Keyboard Shortcut: Ctrl+q' ActiveWorkbook.Close (False)End Sub
I have a macros in my spreadsheet when I open it a number in the macros goes up 1 each time the worksheet is opened. I want to put this file on a server that can be accessed by various people. The problem I am getting is every time someone opens it from a different location with a different PC the number starts at 1 again. I want to keep the number going up 1 each time no matter who opens it.
View 4 Replies View RelatedHow/ where to save macros. If I save a macro "normally" ("in this workbook") then it only applies to that one file, right? And I can't use it in any new files.
So what do I do if I want a macro I can use in "all" my Excel files?
And can I email a file containing a macro to a colleague, so he can work on the file, using the macro too?
I ask, because I've had trouble with this in the past (tho in MS Word) where I couldn't get the macro to be emailed along with the file...
Also - how can I get an .xls file with a macro to work in newer versions of Excel? And can I use Excel 2010 to save a file with a macro in an .xls format, so it can be opened in older Excel versions too?
So i have a macro that i wrote that willclean up these datafeeds that i get. i want to be able to use them for each spreadsheet. So the first bit i tried was saving a personal.xls file in the xlstart folder in XP (MSE 2003) . That would automatically pull up but when i tried to run the macro on other worksheets i get the generic 400 error.
the next thing i tried was copying the macro to a module and adding function tags and taking out the sub tags. i then "saved" that in the addins folder then tried assiging my custom button to is through the macros prompt. When i gave the title box the absolute path to the addins folder with clean.xla cited it said it was invalid, so i tried saving it just as clean.xla expecting it to locate the file there anyway.
this far each time i try to run the macro globally it says it cant find the current sheetname!macroname So i must be missing a step somewhere... can anyone give advice, i have searched the forum and couldnt find a similar problem for solutions.
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 71 bytes) in /home/eforum/public_html/search.php on line 1155. I have following macro, what worked just fine in 2003. Now I'm working with 2007 and I can't set up the Microsoft Visual Basic for Applications Extensibility library. Here's the
View 2 Replies View RelatedI have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?
View 3 Replies View RelatedI am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is there a quicker way?
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
I have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?
View 9 Replies View RelatedI have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.
I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.
Where:
Market position is either 1 or -1
Entry price is the price where the position is reversed.
For example:
H3=1 and I3=100.56, then
H4=-1 and I4=100.88, then
H5=1 and I5=100.53
I need to calculate the profit as follows:
I4 - I3 (because market position of 1 expects the price to go up) and then,
I4 - I5 (because market position of -1 expects the price to go down)
I want to calculate this as a running total to the bottom of the column.
This is the formula I was working with, but it doesn't work:
=IF(OR($J4="",ISERROR(MATCH(-$J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH (-$J4,$J4:$J$55,0)-1,)-$K4))