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?
I have created a spreadsheet in the field of marine surveying. In that sheet, I have used a lot of macros (21 modules) and a lot of VBA, most of it provided by kind people on this forum. But what has happened now, is that when I use that particular worksheet, the 'Undo' menu item in the 'Edit' menu has become permanently grayed out and it says 'Can't Undo'.
In other words, I cannot undo, even if I have made the wrong entry in an unprotected cell. If I use any other sheet, or make a new sheet, everything returns to normal. So, is there any way I can re-gain the 'Undo' facility, while using that particular sheet. I am using Excel 2003 on a Windows XP Home (SP3) platform.
I have a user who wanted a spreadsheet to highlight cells containing formulas whenever the formula was changed. (They want the ability to make changes, but want those changes highlighted. They do not want to protect cells with formulas.) I tried to accomplish this with Conditional Formatting but could not figure out a way to do it, so I turned to VBA. I wrote the following routine that is called on the Worksheet_change event:
Sub CheckFormulas(Target As Range, FormulaRange As String) Dim CurCell As Range For Each CurCell In Range(FormulaRange) If Target.Address = CurCell.Address Then Target.Interior.ColorIndex = 3 End If Next CurCell End Sub...................
A Worksheet_Change macro in Sheet1 changes the font colour if the target is cell(1,1). So, if I change the value of cell(1,1), it will become red. But then I cannot change it back to the original value because the undo button is not active anymore. And what if I changed cell(1,1) by mistake instead of cell(2,1), and I want to put it back to the original value?
I'd like to know if there is a change event that only occurs when a target cell is changed by the user, but does not occur when i'ts changed by a macro. nfortunately, the Worksheet_Change event occurs in both cases.
I have looked at a series of Change Event topics and code but can't see what I need. I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes. I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...
I just spent 8 hours of VBA coding completed and after exiting excel and re-opening the file, I have seem to lost all the 8 hours worth of VBA coding I just completed. For example, I created an extra module (Module 3) and that disappeared; I updated several SUB commands and even those did not update. My code was about accessing databases using the OpenDatabase method and I noticed that towards the end before I shut it, my connection to the database kept failing (hence the reason why I was closing it and reopening it).
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
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]
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
I have a summary sheet using a macro to summarise multiple other worksheets. However, when I run the macro, the summary sheet loses all formatting.
For example, in one column of the "target" worksheets, the data is entered on multiple lines within a single cell, using alt+shift. When I run the macro the formatting is lost and the data in this specific range is displayed in one long row in a single cell. Also, font formatting is also lost.
In the attached example, the top row shows the original formatting on the "target" worksheet, but below that is the formatting after the macro has run and this is what shows on the summary.
I need it to be formatted as on the original if possible.
When I use customize and assign a macro (to a button) I set the "Marco In" option to "This Workbook" then select a macro from the available list. If you close the Assign Macro dialog and open it again, notice how it appends the name of the file to the macro name. Later, in an automated process the xls filename is appended with a value and I think this is causing the problem because the appended data is static and thus no longer is in sync with the new (latest) filename. How do I assign macro's to either custom toolbar's or autoshapes so that the assignment is tolerant of file name changes? I tried to delete the appended data but it is appended automatically.
I am also curious why some of the macro's listed in the dialog include a filename and macro name delimited with an exclaimation mark and some are macro name only (with no delimiter).
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 macro with a vbYesNo in it currently, I tried adding cancel as an option (vbYesNoCancel) but ran into some issues. aMaking my code run with the Cancel option? All I need is to have the cancel option there and have the sub end if cancel is selected.
Code: Sub Sendit() Dim lr As Long ' Conformation Box for Daily Sales Report Date[code]......
If any work is done by a macro it canot be restored by undo. Can a macro be provided so that I can attach the same to my codes and thus undo action can be performed?
The below code uses data in column "A" (Const cl& = 1) to create a sheet for every unique value in column "A".
I would like for the code to request what column to use as source data. As an example when the code first runs a pop-up box would ask for a column letter to use as the source, after entering the letter (or corresponding column number) the code will execute.
Code: Sub Add_sheets_from_A() Range("XA1") = ActiveSheet.Name ActiveSheet.Name = ("Add_Sheets")
I want to write a VBA code that will place ActiveX Frame directly onto the worksheet and then add 3 Option Buttons from Toolbox into that frame.
I could not find an answer searching through the web... Also when trying to record macro and see the code it did not record placing the buttons from the toolbox or any formatting (color, caption, etc.) via properties...
When formatting the buttons it would be useful if the selected radio button would have different color, bold font, etc.
I have set up a combolist box, and wish to run different VBA subroutines depending on what the user has selected from the combolist. How do I do this? Apologies in advance if this sounds like a silly question.
is it possible to choose an option button which will then perform a calculation on a cell reference? I’m trying to deduct a percentage from a total when an option button is highlighted.
I need to to use VBA to copy a worksheet (which i've managed to do!), but I need the new worksheet to have a Worksheet_Change event. Now when I copy the worksheet, the event doesn't copy over (obviously as its a cut and paste jobby). Any ideas on what code I need to add in the Worksheet_Change event just after the new worksheet is automatically created?
I have seen many examples posted here that are close to what I need, but I am not experienced at writing code, so I am not sure how to make the changes to this code that apply to what I need. So I will try to explain what I am looking for & hopefully not be too long winded:
I have an excel workbook that has several worksheets within it - 10 of the worksheets are identical as far as the formulas that are in each of the cells, however, they are all VLOOKUP cells that refer to another excel workbook (used as a "database")which lists all of our projects - there is a cell in each of the 10 sheets that can be changed that will allow that particular worksheet to access the information in the "database worksheet" for the particular project name that is entered in this cell.
There is currently an event worksheet code for each of these worksheets which allows for a picture to be displayed "floating" above cell (L13) based on the project name that is showing in this cell- however it is based on all of the project pictures "living" in each worksheet (the picture that is called up by the project name is displayed while the rest are hidden - as per the formula)- the code is shown below: Option Explicit
Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("L13") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub
However - this option works fine when there are 5 or 10 pictures/projects - but we are looking to grow our project database. So, I was hoping to be able to store the pictures in another location (such as another worksheet or in a file on the server - I would also appreciate input if anyone has an opinion on which would work better?) and have some type of worksheet event code that can be written in to each worksheet that would access the picture in this "central" location and have them appear in cell (L13) of each worksheet based on the project name displayed.
I have 2 worksheets ('pathways', 'pathway events').
'pathways' has unique rows with a unique ID i.e. [Pathway ID] whilst 'pathway events' has the same initial column [Pathway ID] but with multiple values of the same [Pathway ID] value.
If an [Pathway ID] value is selected in 'pathways', I want it to trigger some code which will open a new worksheet and copy the multiple rows in 'pathway events' with the same [Pathway ID] value and paste them into the new worksheet.
Is this possible to do in Excel. I normally use Access and you can have triggered events.
