SendKeys won't fire on Workbook_Deactivate or Workbook_BeforeClose

**xzlaatc** · 11-26-2012, 12:37 PM

I have a script using 'Workbook_Activate' that performs a few actions when the workbook opens, among them is to minimize the Ribbon using SendKeys. (I only want the Ribbon minimized, not hidden, that's why SendKeys.) I have another script using 'Workbook_Deactivate' that reverses these actions, but the SendKeys line won't run. It does'nt lock up, it just doesn't restore the Ribbon. Any ideas would be appreciated. (BTW: all the other commands work fine both opening and closing and yes, I've tried 'Workbook_BeforeClose' with the same results.) Heres my script:

Option Explicit
______________________________________________

Private Sub Workbook_Activate()
Application.ScreenUpdating = False
Worksheets("Class Scores").Activate
ActiveWindow.WindowState = xlMaximized
Application.WindowState = xlMaximized
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Range("Y5").Select
Application.DisplayFormulaBar = False
If Application.CommandBars.Item("Ribbon").Height > 60 Then
Application.SendKeys ("^{F1}")
Else: GoTo Finish:
End If
Finish:
Application.ScreenUpdating = True
Call ToggleCutCopyAndPaste(False)
End Sub
_______________________________________________________

Private Sub Workbook_Deactivate()
Application.SendKeys ("^{F1}")
Application.DisplayFormulaBar = True
Call ToggleCutCopyAndPaste(True)
End Sub

**Jakobshavn** · 11-26-2012, 01:04 PM

Try inserting:

Please Login or Register  to view this content.

immediately after the SendKeys

**xzlaatc** · 11-26-2012, 01:21 PM

Thanks Jakobshavn...that works fine when closing the workbook! However, when switching between workbooks, the other actions work fine, but the ribbon won't restore. You did so good with your first suggestion, how about taking a shot at this one

NEW INFO: Interesting...if I switch between this workbook and another xlsx file, it works fine. But, if I open a new workbook while this one is open, it doesn't toggle the ribbon when switching between those two books.

**Jakobshavn** · 11-26-2012, 01:41 PM

Because CNTRL-F1 is a toggle, if it is issued twice, it looks like its not working.

I suggest you place MsgBoxes before and after each SendKeys to make sure we understand exactly what is happening.