VBA And Macros

Joined
Feb 16, 2002
Messages
38
Hello, I have a problem, I have created a buttons that I would like when clicked to will automatically close and save the application however I would also like it to every 259 times this is done to pop up a message box saying a customised message can anyone please help me?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Odd one :).

Try naming a cell Counter (on the same sheet as your button) and hiding the column it's in, then paste the following code to the start of your button's code

Range("Counter") = Range("Counter") + 1
If Range("Counter") = 259 Then
MsgBox "This buttonhas been pressed 259 times"
Range("Counter") = 0
End If

' Rest of your code
 
Upvote 0
Sure, but I'll need you to elaborate for me first: -

1. How many worksheets are in your workbook and what are they called?
2. What is the column letter of the last column with data in on your first worksheet?
3. Where is your command button- on the worksheet, or is it on a userform?
4. What is your command button called (it'll be something like commandbutton1 or cmdSomething)?
5. What message do you want displaying after 259 clicks?
 
Upvote 0
Here is the whole thing, it uses A5000 to hold the counter and saves and closes Excel, if you wanted to just close the workbook then change "Quit" to "Close" in the code.

Sub Bye()
Range("A5000").Value = Range("A5000").Value + 1
If Range("A5000").Value = 259 Then
Range("A5000").Value = 0
str1 = "Your message goes here....."
i = MsgBox(str1, vbInformation + vbOKOnly, "Exiting Now")
End If
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.Quit
Application.DisplayAlerts = True
End Sub

Add the above then add the Button and attach the macro to the button.

Good Luck, Rick E.
 
Upvote 0
Hi, thanks dude that works great only one little problem It doesn't save! but thanks atleast I'm getting somewhere, I don't want it to save the changes of the template I want it to save as a seperate workbook, sorry for not being precise enough
 
Upvote 0
Arash,

I was an A-level student a few years ago and I thought the point was to LEARN new things and then pass the exam/coursework. The reason you're getting negative responses is because you seem to want people to do you work for you without you attempting to do something for yourself i.e. even attempting a little bit of code and then asking for help when it doesn't work. There are many people here who will happily provide code and you'll earn yourself maybe a C grade - attempt something, ask questions, understand an you'll be an A grade. If you want a decent uni place/job/both then believe me, you need to try stuff yourself.

Regards,
Dan,

Ex A-level student.
 
Upvote 0
By the way, the previous post was written by me. I neglected to enter my username/password. I am also ashamed that it's 8:45pm on a Friday night and I'm answering questions on an Excel help site! However, I'm waiting for a phone call from a 23 year old north Londoner who seems unattainable but she'll be mine - if only through my Excel knowledge! By the way, I'm sat here with my 6th (and counting) Stella of the evening,

Laters people,
Dan.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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