Issue with Save and Close macro

[ChaosPup]

Hi all,

I have a couple issues that I don't understand, hoping someone can help.

I have a spreadsheet that everyone uses that opens as full screen with no formula bar, no ribbon etc. (see code for opening below - I use a macro button to save and close, also below). The code itself works fine.

1st issue - I originally used application.close but it closed every other iteration of Excel that someone using the spreadsheet had open (obviously). ThisWorkbook.Close fixes that but I'm left with an empty Excel window, is there any way to close this also?

2nd issue - After using the workbook, every subsequent spreadsheet that is opened is missing its formula bar. It's easy enough to check the View Formula Bar every time, but is there any way to prevent this?

Thanks everyone!

Sub Workbook_Open()

 ActiveWindow.DisplayGridlines = False
 ActiveWindow.DisplayHeadings = False
 Application.DisplayFormulaBar = False
 Application.DisplayFullScreen = True
 
 End Sub

 Sub Save_and_Close()
   
    ActiveWindow.DisplayGridlines = True
    ActiveWindow.DisplayHeadings = True
    Application.DisplayFormulaBar = True
    Application.DisplayFullScreen = False
    Application.DisplayAlerts = False
    
    ThisWorkbook.Save
    
    ThisWorkbook.Close

End Sub

 

[RobP]

Hi,

to solve your issue (1) you might be able to use :

Application.Workbooks.Count

in order to see if you have >1 workbook open - then you can choose to close the workbook instance, or the application instance (which I guess is better for those having only your sheet open) ?

for item(2) I think its true Excel remembers its last state as such - but perhaps one option again is to turn them back on just before you save and close this workbook in your code above ?

cheers

Rob