Results 1 to 3 of 3

Thread: Excel/VBA - Set Action on Cancel Press

  1. #1
    Network|Geek kidzer's Avatar
    Join Date
    Jul 2005
    Location
    Aberdeenshire
    Posts
    1,732
    Thanks
    91
    Thanked
    47 times in 42 posts
    • kidzer's system
      • Motherboard:
      • $motherboard
      • CPU:
      • Intel Q6600
      • Memory:
      • 4GB
      • Storage:
      • 1TiB Samsung
      • Graphics card(s):
      • BFG 8800GTS OC
      • PSU:
      • Antec Truepower
      • Case:
      • Antec P160
      • Operating System:
      • Windows 7
      • Monitor(s):
      • 20" Viewsonic
      • Internet:
      • ~3Mbps ADSL (TalkTalk Business)

    Excel/VBA - Set Action on Cancel Press

    Right, I don't know if my title makes sense - I couldn't think of a better wording though!

    Anyways, I'm working on a big fancy spreadsheet at the moment, and I've come across a useful bit of VBA for running a Goal Seek on mutiple cells, which after some testing and modification works well enough but for one issue, when the cancel button is clicked on any of the dialog boxes I get the following error;

    "Runtime Error '424':
    Object Required"

    Now, I'm no VBA/VB guru by any means, so I've not really got a clue about this - what I'm looking for is a way to make it close gracefully - clicking cancel at any point means no data has been modified up to that point, so I need it to just end.

    Is there some kind of function like -
    Code:
    OnError goto 'Exit'
    
    ...main code for Goal Seek...
    
    Sub Exit
    End
    End Sub
    That will do what I need, or am I being too simplistic?

    Cheers,
    David
    "If you're not on the edge, you're taking up too much room!"
    - me, 2005

  2. #2
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Excel/VBA - Set Action on Cancel Press

    erm without nowing any context of it, you might find that the on error method is dodgy as hell, however its not as if we never do a few hacks

    now i thought with VBA you could only use a line handler for a goto?

    ie
    Code:
    sub foo
    on error goto err_handler
    ... dodgy code
    exit sub 'stop the good code hitting the error handler
    err_handler:
    msgbox "error!"
    end sub
    throw new ArgumentException (String, String, Exception)

  3. #3
    Network|Geek kidzer's Avatar
    Join Date
    Jul 2005
    Location
    Aberdeenshire
    Posts
    1,732
    Thanks
    91
    Thanked
    47 times in 42 posts
    • kidzer's system
      • Motherboard:
      • $motherboard
      • CPU:
      • Intel Q6600
      • Memory:
      • 4GB
      • Storage:
      • 1TiB Samsung
      • Graphics card(s):
      • BFG 8800GTS OC
      • PSU:
      • Antec Truepower
      • Case:
      • Antec P160
      • Operating System:
      • Windows 7
      • Monitor(s):
      • 20" Viewsonic
      • Internet:
      • ~3Mbps ADSL (TalkTalk Business)

    Re: Excel/VBA - Set Action on Cancel Press

    A-thanking you, that has got the desired result!

    Now it looks better than an actuall error box, cheers!

    "If you're not on the edge, you're taking up too much room!"
    - me, 2005

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Netscreen 5-GT configuring resilience
    By jez_convergence in forum Networking and Broadband
    Replies: 4
    Last Post: 23-08-2007, 02:31 PM
  2. Replies: 10
    Last Post: 28-03-2007, 02:59 PM
  3. Stupid Dabs website won't let me cancel order?!
    By Sumanji in forum SHOPPING AND CLASSIFIEDS
    Replies: 7
    Last Post: 09-04-2006, 09:08 PM
  4. Audio Codec PLEASE HELP!!!
    By 3Dfx in forum PC Hardware and Components
    Replies: 5
    Last Post: 24-09-2003, 03:38 PM

Posting Permissions

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