asked on

How to create a VBA code to delete a private sub on sheet1?

The below code works great, but I want the user to have a choice whether or not to keep it. I’m trying to create a code that I can assign to a button that will delete the below code if the user does not want the option.

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 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 Sub

Open in new window

Martin Liss

Instead of deleting the code, add as global variable like bSkip and then in a sub ask the user if he wants to skip the code. If he says yes then set bSkip to True. You would need to modify your code above to look like this:

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 bSkip Then Exit Sub
  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

...Etc

Open in new window

kbay808

ASKER

I'm not sure how to create a code to set the global variable. I created a dropdown box in cell "H1" with a True and False option. Here was my attempt to create the code to set the variable. I placed it in the Sheet code. Needless to say, it does not work.

 Private Sub Workboot_bSkip()
 Public bSkip As Integer
 bSkip = Range("H1")
 End Sub

Open in new window

Martin Liss

Option Explicit 
Private bSkip As Boolean

Private Sub Workboot_bSkip()
 bSkip = Range("H1")
End Sub

Open in new window

Martin Liss

Note that the above assumes that you have "Option Explicit" at the top of your sheet's code. Having it there is not absolutely required but it's a good idea that we can discuss if you don't know what it does. In any case line 2 should either be under it or at the top of the sheet if Option Explicit isn't there.

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kbay808

ASKER

You put me on the right track. The below code works as a trigger.

If Sheets("HPSM").Range("G2").Value = "Off" Then
Exit Sub
Else