How to get button clicked value from system generated error message in Excel vba 1

[sxschech]

Originally posted as thread68-1753252.

Maybe not using the right search terms, was wondering how to get what the user clicked on when a system error message happens or do I need to build my own message box and trap for the error. Since the system generated message gives the user the option of yes, no or cancel, I thought I could get the value of what they clicked on without having to write out a message box and error handling. Intellisense only seems to get the error number and message, didn't see an option for getting vbyes, vbno or vbcancel.

 

[SkipVought]

Hi,

What kind of control is being clicked?

Does this activate a click event?

Do you know for sure where it is the error occurs?

Can you select debug at that point?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sxschech]

This is related to save as. User clicks button, does a save as and message comes up if the file exists asking if want to replace it or not (error msg 1004). I was hoping could somehow use the built in message rather than create one, but in the meantime have created one.

 

[zelgar]

If you don't want to get the error message, you can add the following line of code:

Application.DisplayAlerts = False

This will result in it saving over the existing file.

 

[sxschech]

It's not that I don't want to get the error message, I want to be able to take action based on user choice.

 

[combo]

Build your own Save procedure. You can uswe WorkbookBefore Save event procedure for specific workbook only or build an add-in with a custom class for handling application events, you need WorkbookBeforeSave event. For both you can use ByVal SaveAsUI As Boolean and Cancel As Boolean parameters to send to excel if you want to cancel SaveAs dialog and cancel saving process.

combo

 

[sxschech]

combo, in my post of 07-AUG, I indicated that I created the necessary code. It was merely that it seemed odd to have to write code if everything was already built by MS and wondered if someone knew how to capture the yes/no from that popup form.

 

[combo]

The 1004 error is caused by your code executed after the user's action. Your code tries to do/access something that for any reason is not possible. If you have no control over the saving action and it may cause errors, capture the process and do some tests. But first find and post the line of code (maybe with context) that raises this error.

combo

 

[sxschech]

Not sure what the disconnect is in articulating my question...my code is working fine. Perhaps a picture may help.

Above is what was generated by excel. My question was whether I could determine if user clicked Yes or No instead of, as mentioned in post of 7 Aug, creating my own, which is replicates the style of the excel message and works properly as shown below. A lot of posts to learn if it was/wasn't possible to save a line or two of code. Sorry about that.

yesno = MsgBox(newname & " already exists." & vbCrLf & _
                      "Do you want to replace it?", vbExclamation + vbYesNo, "Confirm Save As")
                If yesno = vbYes Then
...
...

 

[Andrzejek]

If vbYes = MsgBox(newname & " already exists." & vbCrLf & _
    "Do you want to replace it?", vbExclamation + vbYesNo, _
    "Confirm Save As") Then[green]
   'Action when Yes is clicked[/green]

Else[green]
   'Action when No is clicked
[/green]
End If

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[combo]

So why your code generates the 1004 error - definitely it's not user's action!
Try to debug the code, you may need to change VBE settings: set 'Notify Before State Loss' and 'Break in Class Module' in general options. This will help to find the critical code. Is your code in the same workbook users tries to save?
The only thing you can do silently with excel messages is to allow excel to perform default action (as in zelgar's example), in this case No.

combo

 

[strongm]

The OP doesn't want to avoid the 'error', or respond to it silently. They want to be able to read what action the user took in response to the system generated dialog rather than writing their own dialog.

Sadly it isn't really possible (well, it is, but it isn't straightforward and involves a lot more lines of code than simply writing your own dialog)

 

[sxschech]

Strongman, thanks, you understood my question.

Andy, your suggestion is a bit more streamlined than mine, I'll try that in the future.