Worksheet_Change to prevent area change

[JackDanIce]

Hi,

I have a dynamic area under B14:H14, which I only want to add rows to via macro input and not limit via cell protection.

Is there a way to use Worksheet_Change to detect if a user enters a value in a defined area and prevent cell value edit?

Tried to search for answer, but must typing wrong. Results include recurrsive change event loops and surpressing change events, but yet to find solution for using worksheet_change to to detect and prevent cell edit.

Any suggestions? TIA,
Jack

 

[Trebor76]

Hi Jack,

Something like this?:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("B14:H14")) Is Nothing Then
        MsgBox "Changes are not permitted to that cell.", vbExclamation
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Undo
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
    
End Sub

HTH

Robert

 

[Michael M]

would this help

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 14 Then
        If Target.Column > 2 AND Target.Column < 15 Then
            Cells(Target.Row, Target.Column).Offset(1, 0).Select
        End If
    End If
End Sub

 

[JackDanIce]

@Trebor76 and @Michael M

Great suggestions, really like them both and can adapt, both replies from down-under, thank you!

I was in Sydney in January for friend's wedding and stayed with another friend in Manly, fantastic city and weather - if only not so far away

 

[JackDanIce]

May have replied too quickly, ask has changed slightly.

If range is e.g. B14:H20, user can only use a macro to create B21:H21, however they can edit any cell within B14:H20

Next time, range is now B14:H21 and user can only use a macro to create row B22 but no restrictions to edit range B14:H21 etc

I.e, allow change only to a specific area and prevent user adding new rows after last row (unless via macro) and without using cell/sheet protection.

(Again, unable to find an answer online and if this change is too different to thread header, will open a new thread.)

 

[Trebor76]

Hi Jack,

Manly certainty is a beautiful part of Sydney. Though it is the the Rugby League team I support (Manly Warringah Sea Eagles) I actually live in the other part of town in the Sutherland Shire.

How is the range that the user can edit being determined? How is it then decided that a macro will unlock the next row i.e. B21:H21? The address range may have to be stored on another sheet so we can reference it when needed.

Robert

 

[JackDanIce]

Hi Robert,
Only there 2 weeks so was kinda going with the flow and wedding stuff - reception was restaurant Catalonia - incredible views by a water body. Back to Excel!

Header row (14)

Above header, input section (6 variables with data controls for restricted input). Macro reads these values and creates a new row at bottom of table with the data and then it is sorted.

User can only edit cells under row 14 if non-blank, i.e. they are not allowed to put their own "new" data in, unless they have used the input section above row 14

It's to control data types and ranges of values when User inputs.

 

[Trebor76]

Hi Jack,

To illustrate what I mean follow these steps:

• Put the current range that can be currently edited into a cell (I have used cell A1 of Sheet2) like so $B$14:$H$20

• Put the following worksheet event macro on the sheet you want limit the range the user can edit (obviously changing the strMyRange reference to where the editable range resides):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strMyRange As String
    
    strMyRange = Sheets("Sheet2").Range("A1")
    
    If Intersect(Target, Range(strMyRange)) Is Nothing Then
        MsgBox "Only changes are allowed in the cell range " & strMyRange & ".", vbExclamation
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Undo
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
    
End Sub

• Then have this code in a standard module when you want to increment the range by one (again changing the location where you've put the address to be edited):

Option Explicit
Sub Macro1()

    Dim lngPos As Long
    Dim strMyRow As String
    
    Application.ScreenUpdating = False
    
    For lngPos = Len(Sheets("Sheet2").Range("A1")) To 1 Step -1
        If IsNumeric(Mid(Sheets("Sheet2").Range("A1"), lngPos, 1)) = True Then
            strMyRow = Mid(Sheets("Sheet2").Range("A1"), lngPos, 1) & strMyRow
        Else
            Exit For
        End If
    Next lngPos
    
    Sheets("Sheet2").Range("A1").Value = Left(Sheets("Sheet2").Range("A1"), lngPos) & CLng(strMyRow) + 1
    
    Application.ScreenUpdating = True
    
End Sub

HTH

Robert

 

[JackDanIce]

I was thinking of having a cell record the last used row and then worksheet events test whether target.row exceeds this cell value. Mask cell input with format ";;;"

This gives me some things to try, thanks Robert

 

[JackDanIce]

Named range to record LastRow value, using following code for worksheet_activate and then use something similar to your suggestion for test if input is on row number bigger than value in named range:

Private Sub Worksheet_Activate()
    
    Application.ScreenUpdating = False
    With ActiveSheet
        On Error Resume Next: .Unprotect myPWD: On Error GoTo 0
        .Range("LR_driver").Value = LastRow(ActiveSheet, 2)
        .Protect myPWD
        Application.Goto .Cells(1, .Range("LR_driver").Column)
    End With
    Application.ScreenUpdating = True
    
End Sub

myPWD - a string function to return the password (so it's just one point in the code to change if I need to)
LastRow - function to return last row with optional column argument

Worksheet_Change event will test if Target.Row > Range("LR_Driver").Value and action accordingly or along those lines!