Solved: highlight entire row when click and during editing

[aloy78]

Hi all,

I'm not sure if this is even possible. I want an entire row to be highlighted when I click on the cell position. This is to be sure that I'm editing the correct row.

E.g. say I want to select and make changes on row 161. I want that entire row to be highlighted to yellow when I click on it or make changes to the cell content. After I'm done editing I want the entire row 161 to revert back to its original format.

But I want this to be like a switch to turn on/off this function
i.e. Cell A5 will be the switch. So when i click on it once, a prompt up message will pop requesting for a password and upon confirm say "Editing mode enable". Click on it again then a prompt up will say"Editing mode disable" and the whole sheet is lock.

Does this sound logical at all?

[Bob Phillips]

Option Explicit

Private prevrow As Long
Private EditOn As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const Pwd As String = "Xyz" '<<<< change to suit
Const WS_RANGE As String = "A5" '<<<< change to suit
Dim tmp As Variant

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

If EditOn Then

MsgBox "Editing mode disabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = False
Else

tmp = InputBox("Supply edit mode password", "Edit Mode", "")
If tmp = Pwd Then

MsgBox "Edit mode enabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = True
End If
End If
Else

If EditOn Then

If prevrow > 0 Then

Me.Rows(prevrow).Interior.ColorIndex = xlColorIndexNone
End If

Me.Rows(.Row).Interior.ColorIndex = 36
End If
End If

prevrow = .Row
End With

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[halimi1306]

Hi Bob,

Good job! Seriously I never come across to do this kind of thing. :thumb :thumb :thumb

[aloy78]

Option Explicit

Private prevrow As Long
Private EditOn As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const Pwd As String = "Xyz" '<<<< change to suit
Const WS_RANGE As String = "A5" '<<<< change to suit
Dim tmp As Variant

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

If EditOn Then

MsgBox "Editing mode disabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = False
Else

tmp = InputBox("Supply edit mode password", "Edit Mode", "")
If tmp = Pwd Then

MsgBox "Edit mode enabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = True
End If
End If
Else

If EditOn Then

If prevrow > 0 Then

Me.Rows(prevrow).Interior.ColorIndex = xlColorIndexNone
End If

Me.Rows(.Row).Interior.ColorIndex = 36
End If
End If

prevrow = .Row
End With

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Hi xld,
Very happy that you came up with this on such a short period. :bow: Code works fine. The only thing is that the password only disable the code and it didn't lock up the sheet. Any lead : pray2:

[Bob Phillips]

Option Explicit

Private prevrow As Long
Private EditOn As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const Pwd As String = "Xyz" '<<<< change to suit
Const WS_RANGE As String = "A5" '<<<< change to suit
Dim tmp As Variant

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

If EditOn Then

MsgBox "Editing mode disabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = False
Else

tmp = InputBox("Supply edit mode password", "Edit Mode", "")
If tmp = Pwd Then

MsgBox "Edit mode enabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = True
End If
End If
Else

If EditOn Then

Me.Unprotect Password:=Pwd

If prevrow > 0 Then

Me.Rows(prevrow).Interior.ColorIndex = xlColorIndexNone
End If

Me.Rows(.Row).Interior.ColorIndex = 36
End If
End If

prevrow = .Row
End With

ws_tidy:
If EditOn Then Me.Protect Password:=Pwd
Application.EnableEvents = True
Exit Sub

ws_exit:
Resume ws_tidy
End Sub

[aloy78]

Option Explicit

Private prevrow As Long
Private EditOn As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const Pwd As String = "Xyz" '<<<< change to suit
Const WS_RANGE As String = "A5" '<<<< change to suit
Dim tmp As Variant

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

If EditOn Then

MsgBox "Editing mode disabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = False
Else

tmp = InputBox("Supply edit mode password", "Edit Mode", "")
If tmp = Pwd Then

MsgBox "Edit mode enabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = True
End If
End If
Else

If EditOn Then

Me.Unprotect Password:=Pwd

If prevrow > 0 Then

Me.Rows(prevrow).Interior.ColorIndex = xlColorIndexNone
End If

Me.Rows(.Row).Interior.ColorIndex = 36
End If
End If

prevrow = .Row
End With

ws_tidy:
If EditOn Then Me.Protect Password:=Pwd
Application.EnableEvents = True
Exit Sub

ws_exit:
Resume ws_tidy
End Sub


xld,
I've tried the code and it ends up locking the whole sheet the entire time even when i've enter the pwd. Anyway to modify it to:

On clicking cell A5:
1. Prompt up a window
2. enter the password
After inputting the pwd and the pwd validates:
- it unlock the sheet and enable the highlighting of the row.

Then on clicking cell A5 again:
1. It automatically locks up the sheet and disable the highlighting.

Remark: On a side note. All my previous highlighting were removed when it overlaps. Anyway to retain my previous formatting.
I'll attached the file for your reference.

[aloy78]

Option Explicit

Private prevrow As Long
Private EditOn As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const Pwd As String = "Xyz" '<<<< change to suit
Const WS_RANGE As String = "A5" '<<<< change to suit
Dim tmp As Variant

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

If EditOn Then

MsgBox "Editing mode disabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = False
Else

tmp = InputBox("Supply edit mode password", "Edit Mode", "")
If tmp = Pwd Then

MsgBox "Edit mode enabled", vbOKOnly + vbInformation, "Edit Mode"
EditOn = True
End If
End If
Else

If EditOn Then

Me.Unprotect Password:=Pwd

If prevrow > 0 Then

Me.Rows(prevrow).Interior.ColorIndex = xlColorIndexNone
End If

Me.Rows(.Row).Interior.ColorIndex = 36
End If
End If

prevrow = .Row
End With

ws_tidy:
If EditOn Then Me.Protect Password:=Pwd
Application.EnableEvents = True
Exit Sub

ws_exit:
Resume ws_tidy
End Sub


Hi xld,
Do you think you could write another code for that just highlight the cells on click? I dun need the password lock anymore.

[Bob Phillips]

Can you start a new thread and restate your new question, this is an old thread, lot of water.

[aloy78]

thread closed as requested :)