i have which is i fond in net
please let me know how can i modify this code
this undo code should work
After Running Other Macro Codes
Private Sub Worksheet_Change(ByVal Target As Range) 'set your criteria here If Target.Column < 16 Then 'must disable events if you change the sheet as it will 'continually trigger the change event Application.EnableEvents = False Application.Undo Application.EnableEvents = True MsgBox "You cannot do that!" End If End Sub
Hi baig123,
Unfortunately, what you want probably can not be done easily.
Take the following example Macro:
For example if you put 1,2,3,4 in 4 consecutive cells. Manually you can undo all 4 actions.Sub SimpleUndo() Application.EnableEvents = False Application.Undo Application.EnableEvents = True End Sub
Put 1,2,3,4 in 4 consecutive cells again. Run Macro SimpleUndo() several times. All that happens is the 4 toggles between 4 and whatever was in the cell previously.
You can write a macro to log changes you make (i.e. saving the previous value). You can then write another macro to undo what was done by using the values of the changes (i.e. previous values) you saved. See the attached file that contains the following code:
Sheet1 module:
Ordinary Code module (e.g. Module ModUnDo):Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Call SavePreviousValue(Target) End Sub
Option Explicit Public Type myType sAddress As String vValue As Variant End Type Public myGblVarrayCount As Long Public myGblVarray() As myType Sub SimpleUndo() 'This will undo the last value changed Application.EnableEvents = False Application.Undo Application.EnableEvents = True End Sub Sub ClearCustomUndoArray() myGblVarrayCount = 0 ReDim myGblVarray(1 To 1) End Sub Sub MyCustomUndo() 'This will undo all stored changes Dim vValue As Variant Dim i As Long Dim sAddress As String Application.EnableEvents = False If myGblVarrayCount > 0 Then For i = myGblVarrayCount To 1 Step -1 sAddress = myGblVarray(i).sAddress vValue = myGblVarray(i).vValue Range(sAddress) = vValue Next i Call ClearCustomUndoArray End If Application.EnableEvents = True End Sub Sub SavePreviousValue(ByVal Target As Range) 'This will not work if more than one value is changed at a time ' 'This is the Event Handler called by the Worksheet_Change() event Dim myValue As Variant If Target.Count > 1 Then Exit Sub End If 'Get the current value myValue = Target.Value 'Save the previous value for later use Application.EnableEvents = False Application.Undo myGblVarrayCount = myGblVarrayCount + 1 If myGblVarrayCount = 1 Then ReDim myGblVarray(1 To myGblVarrayCount) Else ReDim Preserve myGblVarray(1 To myGblVarrayCount) End If myGblVarray(myGblVarrayCount).sAddress = Target.Address(False, False) '(false,false) means NO '$' signs myGblVarray(myGblVarrayCount).vValue = Target.Value 'Restore the new value Target.Value = myValue 'Set the focus one cell to the right of the value just changed Range(Target.Address).Offset(0, 1).Select Application.EnableEvents = True End Sub
Lewis
Last edited by LJMetzger; 11-27-2014 at 02:12 PM. Reason: Corrected errors in MyCustomUndo() and replaced attached file.
thanks for an suggest
but i have more macro codes in the same excel which is to run i have attached to my sheet
i have access code which is used to access data from the database
what i was trying to do is after accessing data from the database
if any client try to edit first 6 columns it should undo immediatly
it should display an output that youcant change the row
this undo code should works after running accessing data from the database in a sheet
it is possibale to do it Please let me know
Private Sub Worksheet_Change(ByVal Target As Range) 'set your criteria here If Target.Column < 16 Then 'must disable events if you change the sheet as it will 'continually trigger the change event Application.EnableEvents = False Application.Undo Application.EnableEvents = True MsgBox "You cannot do that!" End If End Sub
I think you are trying to use the wrong tool to do what you want. I think you want to lock the cells, and protect the sheet.
To protect the sheet:
a. Select all the cells in the workbook > Format Cells > Protection > Uncheck Locked (i.e. allow access to all cells)
b. Select the first 6 columns > Format Cells > Protection > Check Locked (i.e. do not all allow access to columns 1-6)
c. Tools > Protection > Protect Sheet (to activate what you just selected).
When you need to unlock those cells when you are downloading data from your data base you could use something like:
Sub YourDownloadMacro() Sheet1.Unprotect 'Your Download code here Sheet1.protect DrawingObjects:=False, Contents:=True, Scenarios:=False End Sub
can i fixed a range columns to be protected in this code
Sub YourDownloadMacro() Sheet1.Unprotect 'Your Download code here Sheet1.protect DrawingObjects:=False, Contents:=True, Scenarios:=False End Sub
Yes,
Use the following instructions from my previous post.
The following tutorial may help you:To protect the sheet:
a. Select all the cells in the workbook > Format Cells > Protection > Uncheck Locked (i.e. allow access to all cells)
b. Select the first 6 columns > Format Cells > Protection > Check Locked (i.e. do not all allow access to columns 1-6)
c. Tools > Protection > Protect Sheet (to activate what you just selected).
http://www.functionx.com/excel/Lesson19.htm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks