Ribbon does not close

L

Legacy 287389

Guest
Hi good people!,

I have a very serious problem...Something happened while working on a spreadsheet which is causing me great headache. All I did was create a macro button with this code:
Code:
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 11")).Select
    Columns("AD:AF").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

At first I did not have the
HTML:
ActiveWorkbook.Close
event. So, the workbook saves, but when I click the "X" to close the workbook, I get the save prompt. So, I resorted to adding this code in the "BeforeClose" event in the "ThisWorkbook" module.:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
This did not prove to do anything, I still got my save prompt. Then I added the
HTML:
ActiveWorkbook.Close
event, and what I found was beyond my understanding: The sheet closes BUT the ribbon is still there with a grey sheet, and all the ribbon icons are greyed out.

I then noticed in the VBA project pane ANOTHER project which is titled: FUNCRES.XLAM. in the module folder there is a RibbonX_code, which says this:
Code:
'Entry point for RibbonX button click
Sub ShowATPDialog(control As IRibbonControl)
    Application.Run ("fDialog")
End Sub

and this:

Code:
'Callback for RibbonX button label
Sub GetATPLabel(control As IRibbonControl, ByRef label)
    label = ThisWorkbook.Sheets("RES").Range("A10").Value
End Sub

where on earth did this come from, how do I get rid of it?...I restarted my pc, but still the same thing?..

Then maybe a last point which might shed light. I have 6 of these similar workbooks. They all have the save code. (NOT the "close" part)..so if I select the button, the sheets save and scrolls to A1, after which I then click the Excel Close button. All the sheets close without a problem AS LONG as there are some of these sheets still open..As soon as I get to the last one, I get the save prompt. Please help me with this, I certainly hope it's not a virus or some bug that's starting havoc here...Please help...
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
That is a Microsoft add-in that you have installed. Nothing to worry about.

Closing a workbook is not the same as quitting the application, which is why you end up with a blank screen and Excel still running.
 
Upvote 0
You could replace the
Code:
    ActiveWorkbook.Save
    ActiveWorkbook.Close

with
Code:
    ActiveWorkbook.Close( SaveChanges = True)
 
Upvote 0
Hi Johnny C,

Ok, what I did was this:
Code:
 ActiveWorkbook.Save
  ActiveWorkbook.Close( SaveChanges = True)

and in the ThisWorkbook module I added in the "before close" event this code:
Code:
ActiveWorkbook.Close (savechanges = True)
 Application.Quit

Now the workbook closes properly IF I use the macro button. If I open the workbook and close it using the excel "X", the book closes but I have a grey screen, which I have to close in the task bar...I do not understand why this is happening...it's so frustrating I can scream...

Please...what is going on here????..How do I fix this?..please help...
 
Upvote 0
Johnny C,

Don't worry about this...I reversed everything...I will live with the way things are as described in post #1 . Thanx for your help...
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top