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
  Else

where you can comment out resetStartPosn to prevent the bee moving at all.