Snap to grid in VBA (actually a SendKeys question)

[Nick Hebb]

Searching through this forum's archives, it seems that there is no way
to set the snap to grid function via VBA. I have noticed that when you
select a shape, the arrow keys will normally just nudge its position
but when Snap to Grid is enabled it will snap the shape to the next
cell edge.

I thought that I could mimic this in code by creating a shape,
selecting it, use SendKeys to move it, then check to see whether it was
moved to the next cell edge. This would tell me whether Snap to Grid
was enabled and I could toggle the command bar button accordingly.

This all sounds good in theory, but the SendKeys step in the code below
does nothing. Can someone review the code and tell me if they spot a
problem? Maybe SendKeys just doesn't work for all Excel objects.

Thanks,

Nick Hebb
BreezeTree Software, LLC
http://www.breezetree.com

============================================================

Public Sub TestSnapToGrid()
SnapToGrid True
SnapToGrid False
SnapToGrid True
SnapToGrid False
End Sub

Public Sub SnapToGrid(ByVal SnapOn As Boolean)
Dim cbb As CommandBarButton
Dim left1 As Single
Dim left2 As Single
Dim shp As Excel.Shape
Dim aligned As Boolean

left1 = ActiveSheet.Range("IS2").Left
left2 = ActiveSheet.Range("IT2").Left

Set shp = ActiveSheet.Shapes.AddShape(msoShapeFlowchartProcess,
left2, 12.75, 48#, 12.75)
shp.Select

Application.SendKeys "{LEFT}"
aligned = (shp.Left = left1)
Debug.Print "aligned = ", aligned

If (Not aligned And SnapOn) Or _
(aligned And Not SnapOn) Then
Set cbb =
Application.CommandBars("Drawing").FindControl(ID:=549,
Recursive:=True)
cbb.Execute
Set cbb = Nothing
Else
' Do nothing: current state = requested state
End If

shp.Delete
Set shp = Nothing
End Sub

 

[Peter T]

Hi Nick,

To determine "whether Snap to Grid was enabled" check
cbb.State
then cbb.Execute if .State is not as required.

I would have thought fairly simple to mimic the snap to grid feature for
'moving' shapes by comparing coordinates with .topleftcell or
..bottomrightcell.

Regards,
Peter T

 

[Nick Hebb]

To determine "whether Snap to Grid was enabled" check
cbb.State then cbb.Execute if .State is not as required.

Ah, that makes it too easy. I didn't know about the State property.

Thanks for bailing me out once again, Peter.

- Nick