Changing Message Box Button Default Focus

[Bass71]

Hi there:

I have a macro in excel that formats a sheet and then saves it, replacing an existing file with same name. THe message box that pops up asks if I want to replace this file with YES-NO-CANCEL buttons. The NO button is always defaulted.

Can I put a line of code previous to the SaveAs command that will change the message box button default to YES?

Here is the code previous to saving...

ActiveWorkbook.SaveAs FileName:="C:\notes\Export\TASHost.txt", FileFormat:= _
xlTextPrinter, CreateBackup:=False


Thanks..............

 

[RobBroekhuis]

I'm afraid you're going to have to check in your own code to see if the file already exists, and ask the question yourself. That way you have full control over defaults etc. I don't think there is a way to direct Excel to do so.


Rob

 

[xlbo]

Would this not be a case of just using
application.displayalerts = false
to supress the messagebox and thereby negating the need for this.....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[RobBroekhuis]

Only if that is truly the intent - if the user still needs to have the opportunity to decline, then the extra work is required.


Rob

 

[xlbo]

But this is after the save button has been hit and it is merely the question of whether the file should be overwritten - being as Bass71 wants it to default to Yes, I would imagine that's what he / she wants to do...

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[RobBroekhuis]

Sometimes I want to be able to do the default action quickly (i.e., enter to confirm), but still wish to have the option of overriding. I approached this question as Bass having the same intention, but I may be wrong (he's been rather quiet about it).


Rob

 

[xlbo]

I see your point Rob - guess we'll have to wait and find out

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bass71]

Hey guys thanks for the dialogue; XLBO you are correct I can simply disable the alerts as I choose the same answer every time.


Thanks again...Bass