0

I'm trying to access the "Find & Select" menu in Excel by using SendKeys through VBA.

When I am simply in Excel, I can access the "Find & Select" menu via the following keystrokes: (Alt + H) → (F D). Try it on your own Excel, it works! You can also see that when you only type (Alt + H) → (F), what it does is limit your alt shortcuts to those that begin with the letter "F", as follows.

However, when I try to do an identical maneuver in VBA using SendKeys, I am able to successfully send the (Alt + H) command and successfully send the letter "F". However, after doing so, the majority of alt shortcuts are greyed out and unavailable. Attempting to use SendKeys to send the letters for one of these shortcuts does not work.

You can see that the only available alt shortcut in the image is "FO". If I use VBA to send that combination of letters, it works! But none of the letter combos for the greyed out shortcuts do. Does anyone know why this is happening? Code used to obtain this result is seen below.

Dim newWB As Workbook
Set newWB = Workbooks.Add
With newWB
    .Activate
    Application.SendKeys ("%h")    ' send Alt+H
    DoEvents
    SendKeys ("f")
    DoEvents
End With

Note: I am aware that SendKeys is awful. In reality I'm not actually trying to access The Find & Select menu, I'm trying to access a different menu which I have not had any luck accessing in any other way, and SendKeys seems like the only option for that. This was just the clearest way to present the problem.

Steve Rindsberg
  • 14,442
  • 1
  • 29
  • 34
ScaredOfRidicule
  • 21
  • 2
  • I suppose you've tried identifying the actual Control you want to use on that "different menu"? If not, see the last large paragraph of https://stackoverflow.com/a/65711550/3654325 after "Identifying the ID of a button" – Stax Mar 16 '23 at 02:31
  • What command are you trying to access on the different menu? There's a good chance you don't need SendKeys. – Darren Bartrup-Cook Mar 16 '23 at 12:01
  • The other command that I am trying to access is the Sensitivity command, seen right next to the Find & Select command in the images that I posted. When checking the "All Commands" menu, I am able to find the 'code' name for the Sensitivity command, which is "ClassifyLabelProtect". When looking for the command identifiers associated with "ClassifyLabelProtect, this name is nowhere to be found. I think it's because this command is added on the enterprise level. – ScaredOfRidicule Mar 16 '23 at 14:57
  • @Stax When trying to only use the code name to access the command, via `Application.CommandBars.ExecuteMso ("ClassifyLabelProtect")`, I get an error message saying "Invalid procedure call or argument". This is using an ActiveX button, not a Form button. – ScaredOfRidicule Mar 16 '23 at 15:14
  • Looks like you can get to it with `Dim labelinfo As Office.labelInfo:Set labelinfo = ThisWorkbook.SensitivityLabel.CreateLabelInfo()` [SensitivityLabel object (Office)](https://learn.microsoft.com/en-us/office/vba/api/office.sensitivitylabel) – Darren Bartrup-Cook Mar 16 '23 at 15:28
  • This link to [MrExcel](https://www.mrexcel.com/board/threads/vba-to-set-sensitivity-label.1198970/#post-6022666) seems to give a way of changing the label – Darren Bartrup-Cook Mar 16 '23 at 15:33
  • 1
    @Darren WOW! I had found that info previously, but had been unable to make it work, which was why I was trying the horrible SendKeys approach. After you linked that MrExcel page, I took one more crack at it and managed to find the proper solution. Thanks! – ScaredOfRidicule Mar 16 '23 at 17:17

1 Answers1

2

So I never found the answer to the weird alt shortcut situation, but I was able to solve the actual problem that I was facing, which was attempting to programmatically change the sensitivity label of a new Excel file through VBA. This code is meant to be run by a button click from an Excel file. It creates a new .xlsx file, takes the sensitivity label of your current file, applies it to the new file, and saves the new file.

Dim currentLabelInfo As Office.LabelInfo 
Set currentLabelInfo = ActiveWorkbook.SensitivityLabel.GetLabel() 
Dim newLabelInfo As Office.LabelInfo

Set xlSQL = CreateObject("Excel.Application") 
Set xlSQS = xlSQL.Workbooks.Add

Set newLabelInfo = xlSQS.SensitivityLabel.CreateLabelInfo() 
With newLabelInfo
    .AssignmentMethod = 1
    .LabelId = currentLabelInfo.LabelId
    .LabelName = currentLabelInfo.LabelName
    .SiteId = currentLabelInfo.SiteId 
End With 

xlSQS.SensitivityLabel.SetLabel newLabelInfo, newLabelInfo

xlSQS.SaveAs Filename:="C:\Temp\myFile.xlsx", FileFormat:=51 
xlSQS.Close
ScaredOfRidicule
  • 21
  • 2