Prevent code from moving a picture in cell outside of specified range

[vanhunk]

Prevent code from moving a picture in cell outside of specified range, even though the code asked for the picture to be moved outside of the specified range

Greetings

This is an extension of the previous post "Move picture in cell to a specified number of cells away, without losing focus", i.e., MOVE IN CELL PICTURE A NUMBER OF TIMES, SPECIFIED IN A DIFFERENT CELL, IN A DIRECTION, WITHOUT LOOSING FOCUS WHEN SPECIFIED NUMBER IS CHANGED.

I have an in-cell picture that I move around a maze.
I have 4 direction buttons (different macros), one for each direction, i.e., left, right, up, or down.
I have a dedicated cell with a number typed in, indicating the number of cells the picture must move in one of the directions above, determined by the button clicked.

I can change the number in the dedicated cell, and is able to click on another direction button, without having to click on the picture again.
The in-cell picture does not lose focus between changing the number in the dedicated cell, and clicking on another direction button.

The above is achieved with the current code below.

What I want the code to be able to do is, whenever the number of cells selected would take the bee outside of the maze, to reset the maze, i.e., run reset code that take the bee back to its starting position.


See current code below (built on code supplied by @Jan Karel Pieterse):


Sub Left()
' Left Macro


Set StepsStart = Range("Bee")

StepsStart.Copy
StepsStart.Offset(0, -Range("Steps").Value).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Name = "Bee"
StepsStart.ClearContents

' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
End Sub
Sub Right()
' Right Macro

Set StepsStart = Range("Bee")

StepsStart.Copy
StepsStart.Offset(0, Range("Steps").Value).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Name = "Bee"
StepsStart.ClearContents

End Sub
Sub Up()
' Up Macro
Set StepsStart = Range("Bee")

StepsStart.Copy
StepsStart.Offset(-Range("Steps").Value).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Name = "Bee"
StepsStart.ClearContents
End Sub
Sub down()
' Down Macro

Set StepsStart = Range("Bee")

StepsStart.Copy
StepsStart.Offset(Range("Steps").Value).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Name = "Bee"
StepsStart.ClearContents

End Sub
Sub ResetToStart()
'Reset Bee to start position

If Not Intersect(Range("D5"), Range("Bee")) Is Nothing Then Exit Sub

Set StepsStart = Range("Bee")

Range("Bee").Copy
Range("D5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

ActiveCell.Name = "Bee"

StepsStart.ClearContents


End Sub

Best Regards
Vanhunk

[vanhunk]

I am sure it is possible to do, and would find it very useful. I hope someone can give it a go.

[p45cal]

Have look at the attached.
Different method to move things around, no selecting.
Look at the named ranges:
TheBee, a cell in a hidden column containing the picture of the bee. Stays put.
AllowedRange, the cells the bee is allowed to go in.
ImHere, the current location of the bee, also contains the formula =TheBee.
The movement of the bee is also restricted by the drawn cell borders.
Currently, instead of resetting to the start, a noise is made.
If the bee lands on the rose, the rose is gone, so I haven't included the rose in the AllowedRange.

[vanhunk]

@p45cal

Much appreciated. I absolutely love it, only problem is that the bee can go through the maize without having to change the number of movements, i.e., you can select a big number and just select the correct arrow block. Although you get the sound, you can still "do the wrong" thing.

Can it somehow be prevented, i.e., either not allow the move, or take the bee back to the beginning?

Regards
Vanhunk

[vanhunk]

@p45cal
Looking at your code, could you perhaps, after the beep, move the bee in the opposite direction, same number of steps, or into the border, to force you to try a different number?

Regards
Vanhunk

[p45cal]

Try changing:
If intendedpath.Cells(i).Borders(direction).LineStyle <> xlNone Or Intersect(intendedpath.Cells(i + myStep), Range("AllowedRange")) Is Nothing Then
Set reachedcell = intendedpath.Cells(i)
Beep ' sound.
Exit For
Else
to:

If intendedpath.Cells(i).Borders(direction).LineStyle <> xlNone Or Intersect(intendedpath.Cells(i + myStep), Range("AllowedRange")) Is Nothing Then
Beep ' sound.
resetStartPosn 'optional
Exit Sub 'For
Elsewhere you can comment out resetStartPosn to prevent the bee moving at all.