Limit location of Highlighted/Active Cell

[EssKayKay]

I’m not sure this is possible but is there a way to limit the range where an active cell is allowed? I’m not talking about “locking” a cell, I mean where you can only highlight/activate cells in a specific range (M33:M1233), either using the mouse or tab/enter button.

 

[duggie33]

You could do this with VBA in the Worksheet, Selection Change event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim lngTargetRow As Long

    If Intersect(Target, Range("M33:M1233")) Is Nothing Then

        Application.EnableEvents = False

        lngTargetRow = Target.Row

        If lngTargetRow < 33 Then
            lngTargetRow = 33
        ElseIf lngTargetRow > 1233 Then
            lngTargetRow = 1233
        End If

        Range("M" & lngTargetRow).Select

        Application.EnableEvents = True

    End If

End Sub

If the user hits the TAB key, it will likely stay in the same cell, but arrows, ENTER, or mouse should all work. There is likely a way to handle the TAB key, but I cannot think of it at the moment.

Hope that helps,

Doug

 

[EssKayKay]

OOPS - Apparently I made a duplicate entry.

You could do this with VBA in the Worksheet, Selection Change event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim lngTargetRow As Long

    If Intersect(Target, Range("M33:M1233")) Is Nothing Then

        Application.EnableEvents = False

        lngTargetRow = Target.Row

        If lngTargetRow < 33 Then
            lngTargetRow = 33
        ElseIf lngTargetRow > 1233 Then
            lngTargetRow = 1233
        End If

        Range("M" & lngTargetRow).Select

        Application.EnableEvents = True

    End If

End Sub

If the user hits the TAB key, it will likely stay in the same cell, but arrows, ENTER, or mouse should all work. There is likely a way to handle the TAB key, but I cannot think of it at the moment.

Hope that helps,

Doug

Thank you Doug. I am still having a problem with this. BUT, I thought of something else. I can hide column H with a command button etc. The run my program and upon exit unhide the column. This is working quite well. The problem is I have numerous other buttons that are now in the way. So I added a hide for the buttons which again is working. But now I have shapes behind the buttons that I do not know how to either hide or reshape.

Is there a way to either hide or rather resize a shape? This way after hiding column H, I could resize the shape to fit the screen properly. After that I would unhide the buttons and return the shape to it's original size (or unhide the shape).

Again thanks,
Steve,