How to manually modify a cell that is being edited in a worksheet_change event?

Bnstates

I'm having a bit of trouble and could use some help. I need to create code that changes the value of a named range based on the value of a cell. There are 4 options: A, B, C, and None. If A, B, or C are selected, I need a preset value input into the named range, change the color of the range to grey, and lock the cells. If None is selected, the named range will change to yellow, and the user will enter their own value into the named range. Below is the code I was trying:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets("Sheet1")

ws.Unprotect Password:="MyPass"

If ws.Range("L14") = "A" Then
    ws.Range("NamedRange").Rows(1) = "Option 1"
    
    ws.Range("NamedRange").Interior.ColorIndex = 15
    ws.Range("NamedRange").Locked = True
    
ElseIf ws.Range("L14") = "B" Then
    ws.Range("NamedRange").Rows(1) = "Option 2"
    
    ws.Range("NamedRange").Interior.ColorIndex = 15
    ws.Range("NamedRange").Locked = True

ElseIf ws.Range("L14") = "C" Then
    ws.Range("NamedRange").Rows(1) = "Option 3"

    ws.Range("NamedRange").Interior.ColorIndex = 15
    ws.Range("NamedRange").Locked = True
    
Else 'user input own entries
    ws.Range("NamedRange").ClearContents
    ws.Range("NamedRange").Interior.ColorIndex = 6
    ws.Range("NamedRange").Locked = False
End If

ws.Protect Password:="MyPass"

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

The issue I'm having is when None is selected. The cells are changed to yellow and unlocked, but because I'm using the worksheet change event, any value I try to input gets automatically cleared. Is there any way to allow the user to change the named range when "None" is selected with the worksheet change event?

FaneDuru

But you do not use in your code the Target cell.... Do you want the event to be triggered by all cells in the sheet change? It would be good to start the event with the cote line : If Target.Address <> "$L$14" Then Exit Sub, being a sheet event, it is not need to use ws and qualify it. It refers to the active cell and it is enough to simple use Range("L14"). Then, the last Else does not refer to a change in "L14" as you probably want...

Test the last way, please:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Address <> "$L$14" Then Exit Sub
 Application.EnableEvents = False

 ActiveSheet.Unprotect Password:="MyPass"
 Select Case Target.Value
    Case "A"
        Range("NamedRange").rows(1) = "Option 1"
        
        Range("NamedRange").Interior.ColorIndex = 15
        Range("NamedRange").Locked = True
    Case "B"
        Range("NamedRange").rows(1) = "Option 2"
    
        Range("NamedRange").Interior.ColorIndex = 15
        Range("NamedRange").Locked = True
    Case "C"
        Range("NamedRange").rows(1) = "Option 3"

        Range("NamedRange").Interior.ColorIndex = 15
        Range("NamedRange").Locked = True
    Case Else
        Range("NamedRange").ClearContents
        Range("NamedRange").Interior.ColorIndex = 6
        Range("NamedRange").Locked = False
 End Select

 ActiveSheet.Protect Password:="MyPass"
 Application.EnableEvents = True
End Sub

The code is not tested, but it should work, I think. Please, test it and send some feedback.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

how to detect enter pressed while jtable cell is being edited?

From Dev

How to check if any item is being edited in the QTreeView or not?

From Dev

Worksheet_change event handler changes a cell; how to stop it triggering itself?

From Dev

Stopping a particular ngGrid cell (not column) from being edited during ngGridEventStartCellEdit

From Dev

How to check if a particular cell in JTable is being edited?

From Dev

How can I trigger a change event manually?

From Dev

How to change cell value using Worksheet_change event without triggering a second call

From Dev

How to check if Being edited on CMSDESK

From Dev

how to manually trigger an event

From Dev

Excel hyperlink follow macro after worksheet_change event

From Dev

Worksheet_change not working when cell content changes via VBA but does manually

From Dev

How to manually trigger a `change` event on a Backbone model?

From Dev

How to execute Macro code inside a module from a worksheet_change event

From Dev

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

From Dev

Manually change the cell that phone is connected to

From Dev

How to know whether QTableWidget's cell is (not) being edited?

From Dev

How to prevent data from edited manually in DB?

From Dev

QTableView: dataChanged event clears cell being edited

From Dev

Worksheet_change event handler changes a cell; how to stop it triggering itself?

From Dev

How to prevent a directory from being edited

From Dev

Exclude Cell Range from Worksheet_Change function

From Dev

How to mark Kendo Grid's cell as edited?

From Dev

Worksheet_change not working when cell content changes via VBA but does manually

From Dev

How to check if an Excel document cell is being edited

From Dev

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

From Dev

Change of the edited cell in a datagridview cell

From Dev

Validate user entry on Worksheet_Change event

From Dev

Combining Select Case as Cell and Range in Worksheet_Change

From Dev

Multiple worksheet_change with same macro with different reference cell

Related Related

  1. 1

    how to detect enter pressed while jtable cell is being edited?

  2. 2

    How to check if any item is being edited in the QTreeView or not?

  3. 3

    Worksheet_change event handler changes a cell; how to stop it triggering itself?

  4. 4

    Stopping a particular ngGrid cell (not column) from being edited during ngGridEventStartCellEdit

  5. 5

    How to check if a particular cell in JTable is being edited?

  6. 6

    How can I trigger a change event manually?

  7. 7

    How to change cell value using Worksheet_change event without triggering a second call

  8. 8

    How to check if Being edited on CMSDESK

  9. 9

    how to manually trigger an event

  10. 10

    Excel hyperlink follow macro after worksheet_change event

  11. 11

    Worksheet_change not working when cell content changes via VBA but does manually

  12. 12

    How to manually trigger a `change` event on a Backbone model?

  13. 13

    How to execute Macro code inside a module from a worksheet_change event

  14. 14

    Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

  15. 15

    Manually change the cell that phone is connected to

  16. 16

    How to know whether QTableWidget's cell is (not) being edited?

  17. 17

    How to prevent data from edited manually in DB?

  18. 18

    QTableView: dataChanged event clears cell being edited

  19. 19

    Worksheet_change event handler changes a cell; how to stop it triggering itself?

  20. 20

    How to prevent a directory from being edited

  21. 21

    Exclude Cell Range from Worksheet_Change function

  22. 22

    How to mark Kendo Grid's cell as edited?

  23. 23

    Worksheet_change not working when cell content changes via VBA but does manually

  24. 24

    How to check if an Excel document cell is being edited

  25. 25

    Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

  26. 26

    Change of the edited cell in a datagridview cell

  27. 27

    Validate user entry on Worksheet_Change event

  28. 28

    Combining Select Case as Cell and Range in Worksheet_Change

  29. 29

    Multiple worksheet_change with same macro with different reference cell

HotTag

Archive