undo function in a macro

[bolo]

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?

 

[Ivan F Moala]

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 xublishsource="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]

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