CODEWITHSUNDEEP
vbaexcel
Nick
Nick

Reputation: 3845

Excel Sendkeys not working

i have the following code which should minimize the Excel 2010 ribbon. I have placed in the workbook_open event code :

Dim iheight As Integer
iheight = Application.CommandBars.Item("Ribbon").Height
If iheight > 100 Then
   Application.SendKeys ("^{F1}")
End If

However when i run it, it opens excel help! i have tried removing the brackets but getting the same result.

Any ideas?

cheers

Upvotes: 0

Views: 3510

Answers (2)

Ben
Ben

Reputation: 3518

you can try following, to hide the ribbon bar:

Sub hideRibbonIfVisible() 
   If RibbonVisibility = 0 Then
      CommandBars.ExecuteMso "MinimizeRibbon" 
   End If
End Sub 

Function RibbonVisibility() As Integer 
   RibbonVisibility = (CommandBars("Ribbon").Controls(1).Height < 100) 
End Function

Upvotes: 1

LBPLC
LBPLC

Reputation: 1571

I'm taking a wild guess here that you are trying to test this code in the VBA editor? Either by clicking the play button or pressing F5?

This won't work if you test in the VBA editor because Ctrl + F1 does bring up the help menu.

What you need to do is call the routine from inside excel, so excel is the active window. Simply create a shape and assign it the routine to test, then invoke it in whichever way you were going to.

Remember what sendkeys does, it just mimics the keyboard

You can lose the brackets, although they wont make any difference

My test code, worked OK:

Sub test6()
Dim iheight As Integer
iheight = Application.CommandBars.Item("Ribbon").Height
If iheight > 100 Then
   Application.SendKeys ("^{F1}")
End If
End Sub

Sub MinRibbon()
Call test6 'assigned to a shape on an excel sheet
End Sub

You could just try saving your workbook and then closing it and opening it (as you have this set to run on workbook_open), I see no reason why this wouldn't work

Upvotes: 1

Related Questions