Snap to grid in VBA (actually a SendKeys question)

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> 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