Avatar of kbay808
kbay808Flag for United States of America

asked on 

How to trigger a Worksheet_BeforeDoubleClick macro with a command button?

The code below works great, but it’s triggered by the value in cell H1.  I want to be able to trigger it by a command button.
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

Open in new window

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

Open in new window

VB ScriptMicrosoft ExcelVBA

Avatar of undefined
Last Comment
Martin Liss
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Here's an example

Worksheet_BeforeDoubleClick Range("A1"), False
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of kbay808
kbay808
Flag of United States of America image

ASKER

That worked!!!  Thanks
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo