In this VBA code when I double click on Target.Column = 8 And Target.row > 3
It always processes Row 4 even when I click on another Row.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

ThisWorkbook.Worksheets("Key Allocation").Unprotect password:="edit"
ThisWorkbook.Worksheets("Key Return").Unprotect password:="edit"
ThisWorkbook.Worksheets("Key Audit").Unprotect password:="edit"

Application.EnableEvents = False

 If Target.Address = "$I$1" Then
  Cancel = True ' Prevent edit mode for I1
  ' Clear specific ranges
  Range("A2:H2").ClearContents
  Range("I3").ClearContents
  Range("I4").ClearContents
  Range("A4:H300").ClearContents
  MsgBox "Page has been reset"
  Cancel = True ' Prevent edit mode
 End If
  
If Target.Column <> 8 Then Exit Sub ' Check if double clicked in column H and exit if not
If Target.Column = 8 And Target.row > 3 Then ' Rest code for processing row in column H
' Set auditRow based on the double-clicked cell in column H

Dim returnSheet As Worksheet, allocSheet As Worksheet, auditSheet As Worksheet
Set returnSheet = Sheets("Key Return")
Set allocSheet = Sheets("Key Allocation")
Set auditSheet = Sheets("Key Audit")
Set auditRow = auditSheet.Rows(Target.row)
' Confirmation message
If MsgBox("Would you like to record details of a Lost or Returned Key?", vbYesNo) = vbNo Then
Application.EnableEvents = True
ThisWorkbook.Worksheets("Key Audit").Protect password:="edit"
Exit Sub
End If

 ' Password storage cell
 Dim passwordCell As Range
 Set passwordCell = Range("J1")
 ' Password Retrieval
 Dim storedPassword As String
 storedPassword = passwordCell.Value
 ' Password Prompt
 Dim enteredPassword As String
 enteredPassword = InputBox("Enter Password to access Key Return sheet:", "Password Required")
 ' Check entered password
 If enteredPassword <> storedPassword Then
  MsgBox "Incorrect Password. Access Denied.", vbExclamation
  Application.EnableEvents = True
  ThisWorkbook.Worksheets("Key Audit").Protect password:="edit"
  Exit Sub
 End If

' Find matching row in "Key Allocation" sheet
Set allocRow = allocSheet.Rows.Find(auditRow.Range("A1:H1"), LookIn:=xlValues, LookAt:=xlWhole)

' Check if matching row found
If allocRow Is Nothing Then
MsgBox "Matching row not found in 'Key Allocation' sheet.", vbExclamation
Application.EnableEvents = True
ThisWorkbook.Worksheets("Key Audit").Protect password:="edit"
Exit Sub
End If

allocSheet.Visible = True
' Copy specific range (A:H) from "Key Allocation" to "Key Return"
allocRow.EntireRow.Copy returnSheet.Rows(returnSheet.Rows.Count).End(xlUp).Offset(1).EntireRow
' Delete matching row from "Key Allocation" sheet
allocRow.EntireRow.Delete Shift:=xlUp
allocSheet.Visible = False
Application.Wait Now + TimeValue("00:00:01")
' Unhide and activate "Key Return" sheet (using Visible property - recommended for all versions)
returnSheet.Visible = True ' OR use Option 2 or 3 based on your Excel version (see previous comments)
returnSheet.Activate
    End If
     
  Dim auditSheetRow As Long
  Dim resetValue As Variant
  Dim cell As Range
  Dim i As Integer
   
  ' Clear range A4:H30 in auditSheet before copying
  auditSheet.Range("A4:H300").ClearContents

  resetValue = auditSheet.Range("A2").Value
  auditSheetRow = auditSheet.Cells(auditSheet.Rows.Count, 1).End(xlUp).row + 1

  For Each cell In allocSheet.Range("A2:A" & allocSheet.Cells(allocSheet.Rows.Count, 1).End(xlUp).row)
    If cell.Value = resetValue And allocSheet.Cells(cell.row, 8).Value <> Date Then
      With auditSheet.Rows(auditSheetRow)
        For i = 1 To 8 ' Copying only columns A to H
          If i <> 9 Then ' Excluding column I
            .Cells(1, i).Value = cell.EntireRow.Cells(1, i).Value
          End If
        Next i
      End With
      auditSheetRow = auditSheetRow + 1
    End If
  Next cell
   
  Cancel = True ' Prevent edit mode
  Application.EnableEvents = True
  ThisWorkbook.Worksheets("Key Audit").Unprotect password:="edit"
  ThisWorkbook.Worksheets("Key Return").Unprotect password:="edit"
  ThisWorkbook.Worksheets("Key Allocation").Unprotect password:="edit"
   
End Sub


Show drafts