Excel Reminders

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
A process for example I data on a built-in data form and it copies that sends it to my invoice, well after every say 100 times it does this I would like it to notify me that it has done 250 process with a message box
 
Upvote 0
On 2002-02-28 12:32, Arash_The_A_Level_Student wrote:
A process for example I data on a built-in data form and it copies that sends it to my invoice, well after every say 100 times it does this I would like it to notify me that it has done 250 process with a message box
This process, is this done with a Macro?

If so add a variable, initialise it to zero and add 1 every time the macro is run. Then use:

If myVariable = 250 Then

MsgBox("Your Message")
myVariable = 0
End If


Would the messagebox only be required after 250 times no matter where the process was by the time that the workbook was closed or would you like to also show the message on closing the workbook?

Would the counting need to start from 1 when the workbook was opened or would it need to continue on from when the workbook was last saved?
 
Upvote 0
right at the beginning, before the fist sub write

dim processcount string

then at end of variable write

if processcount = 250 then
save bit goes in here

processcount =250
else
processcount - processcount + 1

hope this is what you want. :)
Henry
 
Upvote 0
Right click a sheet tab lable, then select "View Code" from the list on your left dubble click "ThisWorkBook" and paste a copy of this code on to the sheet on your right. Hope this helps. JSW

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim myTest As Integer
Static x As Integer
'By: Joe Was, 2/2002

x = x + 1
myTest = x
If myTest >= 250 Then
MsgBox "Time to Save"
x = 0
Else
GoTo myEnd
End If
myEnd:

End Sub

Thanks for the edit Gary, forgot the reset, was about to re-submit an updated copy and saw your post, thanks. JSW
This message was edited by Joe Was on 2002-03-01 13:37
This message was edited by Joe Was on 2002-03-01 13:39
 
Upvote 0
On 2002-03-01 11:05, Joe Was wrote:
Right click a sheet tab lable, then select "View Code" from the list on your left dubble click "ThisWorkBook" and paste a copy of this code on to the sheet on your right. Hope this helps. JSW

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim myTest As Integer
Static x As Integer
'By: Joe Was, 2/2002

x = x + 1
myTest = x
If myTest >= 250 Then
MsgBox "Time to Save"
Else
GoTo myEnd
End If
myEnd:

End Sub

You need to reset x to 0 after the message box has been shown otherwise x will always be >=250

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim myTest As Integer
Static x As Integer
'By: Joe Was, 2/2002

x = x + 1
myTest = x
If myTest >= 250 Then
MsgBox "Time to Save"
x = 0
Else
GoTo myEnd
End If
myEnd:

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top