Excel 2013 :: Enabling Undo After Running A Macro?
Mar 18, 2014
A coworker just ran something by me that i'm quite frankly baffled by. After running a macro, the undo function becomes disabled up to the point of when the macro was ran. I've done a quick search through the forums and can't seem to find this question so i thought i'd ask.
Is there an option to toggle somewhere or 3rd party addon that keeps Undo enabled after running a macro? On a similar topic for my own curiosity, what's the reason that it disables?
I managed to implement a macro that adds a time/date stamp in Column A when any of the subsequent cells in that row are changed. With this now working, I really need the ability to restore the undo history in case a change to the data needs to be reverted.
Sub procedure:
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Application.EnableEvents = False For Each c In Target If c.Column > 1 And c.Column < 21 Then Cells(c.Row, 1) = Now End If Next c Application.EnableEvents = True End Sub
Some of my Excel files work perfectly in terms of undo/redo, while others simply cannot use that feature. I took a stroll through the internet looking for the answer, but the issues that I found seemed to point to the application losing its ability, which was resolved with a registry edit/update/change. I don't think that's the case here because I CAN undo is SOME documents.
After some investigation with my problem documents I have found that THIS Macro is the one thing all my undo-free documents have in common: [URL]
I've got a spreadsheet that pulls data from a SQL server.
I'm looking to have a button on the first sheet that extracts the data from SQL and inserts it into sheet 2 of spreadsheet. The data connection from SqL is working fine but when I click on the button sheet 2 is blank.
I am using excel 2013. I have the table look like the above, in the diagram 1, i want to have the macro such that it can be formatted to the 2nd diagram. Can this be done using macro ? [URL]
I am completely new to Macro's and VB and the macro below has been generated using the "Record Macro" function in Excel 2013 with a couple of very minor modifications based on some research I have done (hyperlink & input box). My ultimate goal is to make a copy of my "TEMPLATE", which is hidden and the copy could have a variety of names, then create an entry in my "SUMMARY" table that references cells on the newly created sheet. The new entry on the SUMMARY page should be entered in the next available row ... at the moment I need to make sure I have my cursor in the right place before I run the macro. I also want the first cell in the new "SUMMARY" row to create a hyperlink to the newly created worksheet.
The macro does what I need it to do, as long as I name the new sheet "Test", what I would like is for the Macro to recognise the name of the new worksheet and create links to that name. The rows and columns in each new sheet will remain the same, hence the R##C## part will always work.
The "SUMMARY" and "TEMPLATE" worksheet names will not (ever) change.
I am trying to define a font colour within a macro When I record the macro, I choose a colour from the font colour-picker in the Home section of the ribbon. The colour I choose is one of the grey shades Everything seems to work ok, and the macro saves with the colour defined as a long number, e.g.
(Note this is the colour number returned by Excel when I chose a grey font) But when I run the macro, instead of a grey font I get a sort of pale apricot
Do I need to re-index something?
Is there a list anywhere of these long numbers and the colours that they represent?
I have a shared workbook with four buttons executing different macros. Problem is that some of the users should only be able to use two of the buttons. To avoid that they accidently press the "restricted buttons" i would like a password (or keyboard shortcut) to enable these.
I currently have this beast of a code running in Excel 2013:
HTML Code: Sub OBTAIN_RAW_DATA_MACRO() Dim NextCol As Long
'Copy data Workbooks.Open Filename:= _
[Code] ........
However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.
Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?
Had 2003 now 2013. Trying to get the macro to select different printers for different doccuments. 2013 macro recorder does not record any printer info or path, all printers show the same wording just application print...
How can I get the recorder to acurrately record and diferentiate printers?
So there's a macro "RoundedRectangle4_click" that's assigned to a box on a worksheet. It's a simple macro, I assume. it just takes the user to the 'Main Sheet' tab. I know the name b/c I rightclicked on the box to get the Macro Assigned.
I click on Macros (F8), it's not listed there, but"RoundedRectangle5_click" is, so I click on it, edit it to match the one I'm searching. Nope, nothing. CREATE button is highlighted.
I've gone to VBA (AltF11) to try to find it among the 25+ worksheets, forms & modules - haven't found it yet.
I have an Excel file which has a macro that sucks in data from a number of separate files (two Excel, a varying number of csv). The macro grabs all the individual files and loads them into one of three worksheets with some formatting, sorting, and structuring on the way. All good so far.
I then need to take two of the worksheets and copy them to a new Workbook which I can send out to some users. So I use the Workbooks.Add method, and use the Selection.Copy on just the data (UsedRange.Rows/Columns.Count to ensure it is only the data) and Selection.PasteSpecial with the xlPasteColumnWidths, xlPasteValues, and xlPasteFormats options to copy the data over. None of the data is filtered. Just straight data.
I would expect the newly added workbook to be smaller than the one with the macros because the one with the macros has two extra sheets that I don't transfer, and the sheets I do transfer are identical.
And that's where it gets weird. The file with the macros and more data is 18.7Mb, but the new one with only two sheets is 24.8Mb. One is .xlsm and the other is .xlsx (because it has no macros).
I've tried opening the new workbook and saving as .xlsm (no material difference to the size) and as .xls (it got even bigger). I've opened the new workbook, gone to the end of the data and deleted all the blank rows and columns, but no effect (because the rows are already empty).
Why would the file with less in it be so much bigger (or bigger at all)?
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have developed a model for a client which keeps crashing after being used a few times. The crash is major! Excel shuts down and asks to send an error report.
This can happen after being run anywhere from 3 to 20 times. (Clicking a POST button which transfers items to a "database" type sheet).
I developed the model on a PC using Excel 2010. When this was loaded on a Mac, the user tells me that it happened periodically, and I have seen is occasionally on my PC as well.
However, the workbook has now been loaded on a server, with users across the state of Victoria accessing via Sharepoint - most using Excel 2007. Now it is happening very frequently.
I don't think the macro is at fault, it doesn't go into debug. Excel just seems to GIVE UP.
Have you seen this kind of thing before? Is there a way of "flushing buffers" or something so that Excel can keep working?
I am reluctant to post the model because it is full of personnel details, and is 2.5mb.
I am attempting to run a MS Ecel macro that is stored on a MS Excel shared file in a MS Ecel target file (locally stored on my C drive) from a VBA module in Ms Access. That is, from a code i want to open the .xls file that houses the macro, and then open the .xls file that i want to run the file in, and then run the macro.
Here is my situation: the file that houses the macro has the workbook hidden and causes in error.
if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set.
if i hide the workbook the vba coding can't find the macro...run time error 1004
once again, i currently have an excel file that houses macro whose workbook is hidden.
I have several similar documents open. They each have a macro with the shortcut CTRL + SHFT + T. The problem is each macro is different for each book. When I am working on book X, I don't want macro from book Z to run, but it does.
Undo / Redo - they do NOT work properly after a save. Before a save, its great - I can undo / redo many many changes.
After a save, I can only undo 2 changes, but the worst thing is it ONLY REDOES ONE of them.
I work by saving very regularly, then I go and look at something on the sheet, and then use Undo / Redo to get back to where I was last updating the sheet and see what I had just been working on, especially if its a few minutes in between.
However, when I do that now, it undoes my last change, but the redo has been lost. So I do:
change, save, undo, redo
and it just doesn't do the redo. The redo button is greyed out.
If I do:
change, change, save, undo, undo, redo, redo
the first redo works, and the second fails as the redo has been lost.
It seems pretty basic that undo / redo should be symmetrical but they aren't, at least now.
I would rather just not have undo after save at all than this broken version.
Why are they putting so much stuff in Excel but they can't even get one of the most basic parts of spreadsheets correct - such as undo / redo, and the ability to open independent spreadsheets without having to change things in the registry (which I have had to do so I can work on two spreadsheets in a semi-sane fashion, undo being shared across your sheets otherwise). MS needs a back to basics...
I have a macro with "ActiveWorkbook.Protect Password:="password"" . It was working fine in excel 2003. In excel 2007, because of this I am not able to run any macro's in the workbook. I am getting a message "The macro may not be available in this workbook or all macros may be disabled. I can see a security warning in message bar saying macros have been disabled, but I dont have have a option to enable the macros.
I have a macro which performs some conditional formatting on a worksheet. Based on the value in cells in one column, if the value meets the criteria some simple formatting is performed (row is colored, font changes for that row etc).
The macro then calls a second macro which performs the same evaluation on the same cells from the same column. If the value meets the criteria (same as previously) it is supposed to change the value of the cell by 1/2.
Both macros work, however the 2nd macro also seems to remove all of the formatting, font changes, coloring etc that occurred from the first macro. I have had the first macro save the active workbook and close and have the 2nd macro re-open the file but it sill overwrites the formatting. If I do not have the 2nd macro run the format changes are saved. I have tried saving the file after the first macro runs with .xls and .xlsx extension and no luck.
I have a workbook with numerous macros in it and they are assigned to buttons in the different worksheets. I am trying to record a new macro and when I start recording and click on one of the other macros nothing happens. This wasn't a problem in excel 2003.
I have a VBS script that runs a macro in a workbook that is located in a SharePoint doc lib. The code looks like this:
[Code]....
I use code exactly like this to run other queries and it works great, but for some reason when I run this macro the instance of Excel doesn't end after the script is done running. I can see EXCEL.EXE sitting in the Task Manager and I have to end it manually.
Here is the code for the macro and related macros:
This macro just calls my other macros. Each terrslicersXX macro is a different sales territory for which there are slicers to filter out data.
[Code]....
An example of one of the sales territory slicers. Basically it just sets the slicer for the required sales territory so the SaveAsWebpage macro can save the information off as a webpage.
[Code]....
Saves the current data as a webpage.
[Code] ....
I have a feeling it has something to do with the last macro and how I publish the sheet as a webpage but I still can't get it to work.
I have an Excel VBA macro which calls a stored procedure. The stored procedure calls a SQL agent, which in turn calls an SSIS package. All I need from the Excel VBA code is to ensure that the SSIS package ran successfully. How do you develop a VBA code to let the user know that the job either failed or succeeded after the user executes the Excel macro? Also, is there a need for a recordset in the VBA code?
I have a sales spreadsheet, that people on this forum have very kindly helped me with by giving me two macros; one to remind users that they need to update the month cell when an order comes in, and the other to automatically put the date in a cell when any cell in that row is changed.
The final thread is here: Message To Remind That Cell Is Mandatory
and the Macro used is:
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Application.EnableEvents = False For Each c In Target If c.Column = 11 Then If c.Value = "100 - Purchase Order In" Then MsgBox "Is the Month In correct?" End If End If If c.Column > 1 And c.Column < 18 Then Cells(c.Row, 1) = Now End If Next c Application.EnableEvents = True End Sub
However...
We've now found that we cannot undo anything in these spreadsheets. If, for example, a cell is incorrectly copied or deleted, the only way of undoing the change is to shut down the spreadsheet without saving!
Is this just a by-product of using the time macro (a search on other threads suggests that it might be), and, if so, is there any way of changing it?
