Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
' This subroutine colors a cell red when double-clicked then clears it when double-clicked again.
' Some values for .ColorIndex are...
' Red = 3, Green = 4, Blue = 5, Yellow = 6, Orange = 45
' Google "VBA color palette" for more colors
If Sheets("HPSM").Range("H1").Value = "Off" Then
Exit Sub
Else
If Intersect(Target, Range("B3:B10")) Is Nothing Then Exit Sub
' If the cell is clear
If Target.Interior.ColorIndex = xlNone Then
' Then change the background color to yellow
Target.Interior.ColorIndex = 27
' Else if the cell background color is yellow
ElseIf Target.Interior.ColorIndex = 27 Then
' Then clear the background
Target.Interior.ColorIndex = xlNone
End If
' This is to prevent the cell from being edited when double-clicked
'Cancel = True
End If
End Sub
Here is my attempt.Private Sub CommandButton23_Click()
On Error GoTo ErrorRoutine
Application.ScreenUpdating = False
If CommandButton23.Caption = "On" Then
CommandButton23.Caption = "Off"
CommandButton23.BackColor = vbRed
Else
CommandButton23.Caption = "On"
CommandButton23.BackColor = vbGreen
Call Worksheet_BeforeDoubleClick(Selection, True)
End If
Application.ScreenUpdating = True
Exit Sub
ErrorRoutine:
End Sub
ASKER
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
TRUSTED BY
Worksheet_BeforeDoubleClic