undo function in a macro

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
Hi i was wondering if there was a way to replicate the undo function. specifically i want the macro to check whether a sum of cells is greater than 100 and if it is then i want the macro to undo the last cell input. any ideas?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
On 2002-09-28 16:00, bolo wrote:
Hi i was wondering if there was a way to replicate the undo function. specifically i want the macro to check whether a sum of cells is greater than 100 and if it is then i want the macro to undo the last cell input. any ideas?

Hello bolo
One way to do this is via the Sheets Events
specifically the Worksheet_Change Event
Look it up in the Help Files.

Assuming the range to Total/monitor is
Range("D2:D7") then this should work.

<pre/>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oRg As Range
Dim Rg As Range
Dim RgSum

Set oRg = Range("D2:D7")

Set Rg = Application.Intersect(Target, oRg)
If Not Rg Is Nothing Then

With Application
RgSum = ((.WorksheetFunction.Sum(oRg)) > 100)
If RgSum Then
.EnableEvents = False
.Undo
.EnableEvents = True
End If
End With
End If

End Sub
</pre>

This is how you enter the code in your Sheet

<META content=Excel.Sheet name=ProgId><META content="Microsoft Excel 9" name=Generator><LINK href="./sht_files/filelist.xml" rel=File-List><STYLE id=AAA_Instructions_VBAEnter_18714_Styles></STYLE>  <DIV id=AAA_Instructions_VBAEnter_18714 align=center x:publishsource="Excel"><TABLE style="TABLE-LAYOUT: fixed; WIDTH: 435pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=580 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 320pt; mso-width-source: userset; mso-width-alt: 15616" width=427><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl2418714 style="WIDTH: 48pt; HEIGHT: 13.5pt" width=64 height=18> </TD><TD class=xl2418714 style="WIDTH: 320pt" width=427> </TD><TD class=xl2418714 style="WIDTH: 48pt" width=64> </TD><TD class=xl2418714 style="WIDTH: 19pt" width=25> </TD></TR><TR style="HEIGHT: 24.75pt" height=33><TD class=xl4218714 style="BORDER-RIGHT: white 2pt double; HEIGHT: 24.75pt" colSpan=3 height=33>WorkSheet_Change</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD class=xl3418714 style="HEIGHT: 9.75pt" height=13> </TD><TD class=xl3418714> </TD><TD class=xl3418714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 24.75pt" height=33><TD class=xl3718714 style="HEIGHT: 24.75pt" height=33>STEP</TD><TD class=xl3818714><SPAN style="mso-spacerun: yes"> </SPAN>'<FONT class=font818714> </FONT><FONT class=font718714>HOW<SPAN style="mso-spacerun: yes">  </SPAN></FONT><FONT class=font918714>J</FONT></TD><TD class=xl3318714>OK</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl3918714 style="HEIGHT: 15.75pt" height=21 x:num>1</TD><TD class=xl3618714 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Press<FONT class=font618714> Alt-F11</FONT><FONT class=font518714> to display the Visual Basic editor (VBE).</FONT></TD><TD class=xl4118714>þ</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2518714 style="HEIGHT: 15pt" height=20><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2518714><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl4018714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 51pt" height=68><TD class=xl3918714 style="HEIGHT: 51pt" height=68 x:num>2</TD><TD class=xl2918714 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>In the<FONT class=font618714> Project Explorer window</FONT><FONT class=font518714>, find an entry for the sheet you're working with. (For example, look for Sheet1 if the cells you're working on are on that sheet.) If you don't see the </FONT><FONT class=font618714>Project Explorer window</FONT><FONT class=font518714>, press </FONT><FONT class=font618714>Ctrl-R</FONT><FONT class=font518714>.</FONT></TD><TD class=xl4118714>þ</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2518714 style="HEIGHT: 15pt" height=20><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2518714><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl4018714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl3918714 style="HEIGHT: 15pt" height=20 x:num>3</TD><TD class=xl3018714 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Double-click<FONT class=font518714> the sheet entry in the </FONT><FONT class=font618714>Project Explorer window</FONT><FONT class=font518714>.</FONT></TD><TD class=xl4118714>þ</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2518714 style="HEIGHT: 15pt" height=20><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2518714><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl4018714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl3918714 style="HEIGHT: 25.5pt" height=34 x:num>4</TD><TD class=xl2918714 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Above the code window, you'll find<FONT class=font618714> two drop-down lists</FONT><FONT class=font518714>. In the list on the left, choose </FONT><FONT class=font618714>Worksheet</FONT><FONT class=font518714>. In the list on the right, choose </FONT><FONT class=font618714>Change</FONT><FONT class=font518714>.</FONT></TD><TD class=xl4118714>þ</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2518714 style="HEIGHT: 15pt" height=20><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2518714><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl4018714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl3918714 style="HEIGHT: 38.25pt" height=51 x:num>5</TD><TD class=xl2918714 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>A procedure template for <FONT class=font618714>Worksheet_Change</FONT><FONT class=font518714> now appears in the code window. Between the </FONT><FONT class=font618714>Private Sub</FONT><FONT class=font518714> line and the </FONT><FONT class=font618714>End Sub </FONT><FONT class=font518714>line, type in your procedure.</FONT></TD><TD class=xl4118714>þ</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2518714 style="HEIGHT: 15pt" height=20><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2518714><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl4018714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl3918714 style="HEIGHT: 15pt" height=20>eg.</TD><TD class=xl2818714 style="WIDTH: 320pt" width=427>See Above</TD><TD class=xl4118714 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2518714 style="HEIGHT: 15pt" height=20><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2518714><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl4018714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl3918714 style="HEIGHT: 15pt" height=20>Code:</TD><TD class=xl3118714 style="WIDTH: 320pt" width=427>See Above</TD><TD class=xl4118714 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2318714 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl3118714 style="WIDTH: 320pt" width=427> </TD><TD class=xl2218714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2718714 style="HEIGHT: 12.75pt" height=17><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2818714 style="WIDTH: 320pt" width=427> </TD><TD class=xl2618714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2318714 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl3118714 style="WIDTH: 320pt" width=427> </TD><TD class=xl2218714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2718714 style="HEIGHT: 12.75pt" height=17><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3218714 style="WIDTH: 320pt" width=427> </TD><TD class=xl2618714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl2418714 style="HEIGHT: 13.5pt" height=18> </TD><TD class=xl2418714> </TD><TD class=xl2418714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD class=xl2418714 style="HEIGHT: 30.75pt" height=41> </TD><TD class=xl3518714 style="WIDTH: 320pt" width=427><SUP>µ</SUP><FONT class=font1118714> HTH - Ivan F Moala </FONT><FONT class=font1018714><SUP>µ</SUP></FONT></TD><TD class=xl2418714> </TD><TD class=xl2418714> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl2418714 style="HEIGHT: 13.5pt" height=18> </TD><TD class=xl2418714> </TD><TD class=xl2418714> </TD><TD class=xl2418714> </TD></TR></TBODY></TABLE></DIV>
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
Thanks alot for thanks i just didn't know how to do the undo event. Tried recording a madro but got nothing. thx again. Until the next problem.....
 

Watch MrExcel Video

Forum statistics

Threads
1,123,478
Messages
5,601,900
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top