Worksheet_change Event Disables Undo

Jul 4, 2007

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...................

View 2 Replies


ADVERTISEMENT

Worksheet_Change Event

Dec 31, 2006

I have two workbooks that have the following
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myColor As Integer
With Target
If Intersect(.Cells, Range("v:v")) Is Nothing Then Exit Sub
If IsEmpty(.Cells) Then r.Offset(, 1).Interior.ColorIndex = xlNone: Exit Sub
If Not IsDate(.Cells) Then r.Offset(, 1).Interior.ColorIndex = xlNone: Exit Sub
Select Case Month(.Value)
Case 1: myColor = 3
Case 2: myColor = 17
Case 3: myColor = 19
Case 4: myColor = 22
Case 5: myColor = 26
Case 6: myColor = 33
Case 7: myColor = 36
Case 8: myColor = 38
Case 9: myColor = 40
Case 10: myColor = 42
Case 11: myColor = 44
Case 12: myColor = 7

I have this code in 1 sheet in one of the books (and all other sheets work fine), and the same code in all sheets in the other book. Both books work the way it's supposed to.

My question,
Is it necessary to have the Worksheet_Change event in all the sheets (all sheets act on the code the same way) or is it okay for just one sheet?

Could I encounter a problem if in only one sheet?

I just don't see why I would have to add more size with the code in all sheets if it is not necessary.

View 9 Replies View Related

Worksheet_Change Event Not Firing

Nov 2, 2008

Bit of an odd one, but I'm sure it's probably happened before to others. I've just been sent a worksheet to redesign (an audit template) one which I'll be making use of data validation lists (Yes, No, N/A) a fair bit.

What I want to happen is that when the user selects from a list, I'll fire a Worksheet_Change event, which will then run some background calculations to set up the next questions, etc. Problem is, the event isn't firing at all. I'm using the following to test the event...

View 2 Replies View Related

Worksheet_Change Event Not Firing ...

Jan 16, 2008

I wrote a simple script to show/hide certain rows based on the value of a certain cell on my worksheet (cell value chosen by drop down). When I left work last night, everything worked fine. When I returned this morning, the change event no longer appears to be firing. I'm quite certain no one else accessed the file to change the coding, so my only guesses are 1)perhaps some sort of system update was applied in the middle of the night and it messed with something or 2) aliens have blocked our technology in advance of their invasion.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Target.Address = "F1" Then

Application.EnableEvents = False

Select Case Target
Case "150"
Rows("13").EntireRow.Hidden = False
Rows("14:19").EntireRow.Hidden = True
Case "330"
Rows("14").EntireRow.Hidden = False
Rows("13").EntireRow.Hidden = True
Rows("15:19").EntireRow.Hidden = True
Case "340"
Rows("15:19").EntireRow.Hidden = False
Rows("13:14").EntireRow.Hidden = True
Case Else
Rows("13:19").EntireRow.Hidden = True
End Select

Application.EnableEvents = True

End If
End Sub

View 9 Replies View Related

Prevent Worksheet_Change Event

Mar 12, 2008

I'm trying to find a way to bypass a worksheet_change event. Right now, my code validates if a change has been done to a specific cell ("D13"). It works perfectly, too perfectly actually.

Here is the code I got :

First line of Module 1 :

Public bDeviation As Boolean

On Sheet1 :

Public Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim VRange As Range
Dim strName As String

Application. ScreenUpdating = False

View 4 Replies View Related

Worksheet_Change Event With Named Range

Aug 17, 2006

Is it possible to use a named range as part of the Target.Address in a Worksheet_Change event? For example, if I've named cell A1 to be XYZ, can I use something like the following VBA script:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("XYZ") Then
.....
End If
End Sub

The above script obviously doesn't work. The only thing I can get to work is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
.....
End If
End Sub

I would like to be able to use a named range so that I don't have to remember to update my VBA when I insert rows or columns in my worksheet.

View 6 Replies View Related

Worksheet_change Event And Delete Rows

Feb 16, 2007

I'm using the following code. Basically it formats some cells in row based on the value in the first column of the row.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
Dim RowNr As Integer

If Not Intersect(Target, Range("A1:A65536")) Is Nothing Then
For Each cell In Target
If Target.Text = "" Then
icolor = 0
ElseIf Target.Text = "Row Loaded" Then
icolor = 4
ElseIf Left(Target.Text, 14) = "Row not loaded" Then

This works fine, except when I delete a row by clicking on the row number to highlight the row, and then right click to select delete.

The event fires for each cell in the rows, and takes ages to run.

Does anyone have any ideas on how to prevent this from happening? I tried substituting


For Each row In Target

View 9 Replies View Related

Get Cell Value Before Worksheet_change Event Triggers

Jul 28, 2007

In the worksheet_change event I am calling a method for performing some calculations. The method is called only if certain cells are affected. I want to know the cell values before the event gets triggered.

For eg: Lets say Cell A1: 10 Cell A2: 20 I select both A1 & A2 and click on delete. Then I want to obtain the values 10 & 20 in the method called. Is this possible? Currently If I do Cell.value it returns blank.

View 5 Replies View Related

Undo Option Lost After Event Macro Code

May 23, 2008

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?

View 9 Replies View Related

Worksheet_Change Event Wont Fire When Target Changed By ListBox Selection

Dec 5, 2008

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("R1C1")) Is Nothing Then
'do something
End If
End Sub

Now, that Private Sub works fine if you change the value of R1C1 manually or from another macro.

But if R1C1 is the cell linked to a list box, nothing will happen if you change its value by selecting different items in the list box.

View 4 Replies View Related

VBA That Disables Save Function

Nov 10, 2011

I have a template workbook which I need to prevent anyone making changes and overwriting the original.

I used the following code;

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
MsgBox "You cannot save this workbook. Use Save As"
End If
End Sub

This works fine apart from I am then unable to save these changes myself so when the workbook is next opened the code has not been saved.

View 2 Replies View Related

Ribbon Disables Sendkeys

Sep 28, 2009

Sub ExcelOptionDialogSendkeys()
Application.SendKeys "{ESC 5}%TO"
End Sub
But if you happen to want to assign it to a Ribbon button click...

Sub rxExcelOptionsDialog(control As IRibbonControl)
Application.SendKeys "{ESC 5}%TO"
End Sub

the sendkeys method just doesn't seem to work under any circumstance.

View 9 Replies View Related

Protecting Cells Disables Group Column Function?

Jul 15, 2014

I have certain cells in my spreadsheet that contain formulas linking them with other sheets within my workbook. I have grouped sets of rows in my first 2 sheets (to show just headings and to expand to subheadings & details). When I try protect the cells with formulas on the sheet then it blocks me from expanding or contracting the row groups.

Is there an easier way to stop someone editing or deleting the data in my cells? There are about 2 dozen cells with formulas in my first sheet that need protecting - unfortunately not in any particular order...

View 8 Replies View Related

Hiding Chart Sheets If Users Disables Macros

May 10, 2008

I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0

Splash.Visible = True

View 4 Replies View Related

Excel 2007 :: Protect Worksheet Disables Formatting And Insert Symbols?

Mar 20, 2013

I've got a workbook where I need to protect certain cells by locking them.

The trouble is when I do this I lose the ability to apply formatting and to insert symbols in other cells?

I've checked the obvious options for when you're locking cells but nothing seems to be stopping this there.

Is there a reason it disables these options, a work around or am I just being dumb?

I'm using Excel 2007 by the way.

View 2 Replies View Related

Code, That Disables Save, Save As And Close Commands

Nov 2, 2009

Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.

The code I currently use for that purpose is as follows.

View 2 Replies View Related

Undo Column Name

Apr 1, 2009

How do undo a column name, say you named column "b" boy how do you get it back to b.

View 2 Replies View Related

VBA Code For Undo

Nov 26, 2009

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?

View 5 Replies View Related

Undo Action In VBA

Jul 18, 2006

I have a database in excel which I want only selected users to have edit rights & others should have readonly rights. I have written the following code where a pop up would appear in selected cells warning user for editing the said cell. What I want is when a user clicks yes he should be able to edit it & when he clicks no the cell should get protected.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rTriggerCell As Range
If Not Intersect(Target, Range("D1:D100")) Is Nothing Then
Set rTriggerCell = Target
Application.EnableEvents = True
If MsgBox("Edit Cell?", vbYesNo)
End If
On Error Goto 0
Exit Sub
End If

View 6 Replies View Related

Worksheet Change Event :: (ByVal Target As Range) Event In Module After Creating A Sheet

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

Undo Text To Columns?

Jul 20, 2009

How do I undo text to columns?

I had a list of email addresses that I put into two columns with the @-now I have finished manipulating and I need to have the email addresses whole again.

The email is now in column A and the domain is in column B. I cannot click undo.

View 4 Replies View Related

Undo Last Commad Button

Jan 28, 2009

I need to create an Undo Last command button.

I have created a textbox using the control toolbox and I have created command buttons that populate text when I click them into the text box.

Do you guys know the code for a command button to undo the last entry in the text box.

View 7 Replies View Related

Undo Data Validation

Mar 19, 2012

How can I do the above Title?

View 1 Replies View Related

Unable To Undo / Redo

Apr 16, 2007

I am unable to undo/redo in MsExcel. Have tried to uninstall/reinstall office, all updated are current.

View 9 Replies View Related

Undo Last Action - Userform

Aug 14, 2008

What i have is a userform which contains textboxes a user can enter or change data in.

What i'd like to do is to have a button called something like "undo last" whereby a user can 'swap' back the last change they made.. does anyone have any idea how i might go about it?

View 9 Replies View Related

Edit Undo When Using VBA Code

Jul 21, 2006

Is it possible to enable the undo command when using VBA code in a spreadsheet? Right now if someone makes a mistake it can’t be undone.

View 5 Replies View Related

Increase Undo Limit From 16

Apr 20, 2007

In Dave and Raina Hawley's excellent Excel Hacks book they showed how to increase the undo limit of 16 up to 100. THis only works for EXcel 2002. Any Bright spark know how to do this for Excel 2003?

View 2 Replies View Related

UNDO Feature To Work

Sep 13, 2007

I cannot get the UNDO feature to work.

View 9 Replies View Related

Undo All Users Actions

Sep 20, 2007

Is there a structure inside of Excel which keeps track of all the actions taken by a user (something that would presumably be used to allow for an undo sequence)? If so, is there a way to get access to it?

View 2 Replies View Related

Undo Causes Copy & Paste

Mar 27, 2008

I'm working on a massive Excel sheet which admittedly uses tons of macros, comboboxes and pivot tables. My issue at the moment is that I have no idea why Excel is pasteing values from specific sheets into the current sheet, when I click on 'Undo'. I've only been able to replicate it when i copy, paste and then undo. I've removed code relating to those sheets and it still does it. I also have no macro's using Ctrl+z. The sheet, whose values it's pasting, does use comboboxes. I have changed the linked cell property in Excel to the "SheetName"!"Cell". It still does this. The odd thing is that it doesn't do it to new workbooks or new sheets.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved