0

I am designing a tool in Excel 365 where I would like to include a simple way for a user to initiate via VBA the "Insert->Shapes->Freeform: Shape" function available via the menu bar. This seems like it should be possible but I haven't come across anything in my searches.

I've seen that Freeform shapes can be added using VBA but it requires the node coordinates so this doesn't help as the users will need to draw the shapes themselves.

I was also thinking of using Sendkeys to mimic clicking "Freeform: Shapes" but when the button is selected by pressing Enter or Space it results in a line being randomly added to the worksheet and the ability to draw is no longer available.

Any ideas on this are welcome!

Sean Marsell
  • 3
  • 3
  • When I macro recorded Insert->Shapes->Freeform: Shape, it used `.Addnode` to plot what I did. I would record drawing a shape and then modifying that code as a starting point. – Axuary Jan 13 '21 at 21:29
  • Thanks for the idea! I tried that initially as well, and the problem is that the initial starting location needs to be provided, along with the additional node locations to draw the shape, and that information is unknown until the user adds the Freeform shape. I should clarify that I don't want to draw the shape itself via VBA, just to trigger the action of drawing for the user without them having to click through the menu options. – Sean Marsell Jan 13 '21 at 21:37

1 Answers1

0

The following code will trigger Freeform Shape (Like clicking the ShapeFreeform button) (In Office365 64Bit on Win10): Application.CommandBars.FindControl(ID:=200).Execute

EDIT - The name in brackets identified far below (##) can also be used in quotes in the following code to trigger the button. CommandBars.ExecuteMso ("ShapeFreeform") (Curiously, "ShapeScribble" (ID:=409) seems to do exactly the same thing as "ShapeFreeform".)

Using an ActiveX Command Button will work, whether the Macro it triggers is in Sheet Code, or whether the sheet code calls a Macro in a Module.

Using a FORM button to trigger this will NOT work (whether the macro is in the Sheet Code or a Module). (Unknown whether this is a bug or intentional)

Assigning a shortcut key to trigger the macro appears to be unreliable unless a delay before the execute line is used. With zero delay, mostly a "ghostly" diagonal line is inserted instead, which disappears if you switch to "Page Layout View" and back. Using Application.Wait and 1 second, it mostly works, but as the actual wait can vary from (very near)0 to 1 second, when it is too short it will fail like above. Using Application.Wait and 2 seconds, it appears reliable (as the wait is from (near)1 to 2 sec).

The Sleep function is far more accurate but is a call to an external library. For both, See How to pause for specific amount of time? (Excel/VBA) I did not try Sleep, so you will have to experiment with what delay works for you.

Essentially, the code puts Excel into a "drawing mode" and other types are possible, like Freeform Curve (button named ShapeCurve), which ID is 1041. Identifying the ID of a button can be quite a chore unless someone knows an easier method than this?: on the ribbon hover over the desired button until the name pops up and remember it > rightclick on the ribbon and click "Customise the Ribbon" > in the first column click "All Commands" (NOT All Tabs) > find the remembered name in the alphabetical list and hover over it until info pops up. The actual "Code" name for the button will be the one in brackets (##). Again, remember this. > look this up online by searching for "office fluent ui command identifiers". A good source is "https://github.com/OfficeDev/office-fluent-ui-command-identifiers". Choose your office version and then the product used (in our case excel... use the excelcontrols.xls) and then search for the remembered name to find the "Policy ID" number.

(Note that only some of the button ID's can be "clicked" by the code first mentioned. Others need a different approach and/or parameters as well).

Stax
  • 469
  • 4
  • 15
  • Some extra information on the end solution: I had to use a shortcut key to trigger the sub (doing so via a form control button on the worksheet seemed to 'cancel' the drawing function), and I had to add a 1 second wait to the code execution prior to the line executing. Just having the single line of code above without the pause didn't reliably bring up the Freeform drawing crosshair on the worksheet. – Sean Marsell Jan 14 '21 at 00:00
  • Thanks Sean, I'll test this further when I get a chance (I only triggered it via debugging in the code window so didn't get that "cancel" problem), and improve the answer if possible... it may need something like DoEvents to allow the drawing to happen. – Stax Jan 14 '21 at 03:00
  • Only had a few minutes to test, but on my machine a **Form** button does not trigger the freeform shape drawing mode AT ALL (whether delayed or not). However an **ActiveX** button calling the code directly in the sheet works flawlessly, as does an ActiveX button calling the code in a Module. Weird. – Stax Jan 15 '21 at 01:03
  • I was just about to comment the same thing but you beat me to it! Thanks again for your help! – Sean Marsell Jan 15 '21 at 01:10