Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: VBA And Macros

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Um could you elaborate on this please?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    Guest

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    arash shut up lol

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sami kiss ***

  9. #9
    Guest

    Default

    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.

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •