View original (French)

Run a macro for all sheets in a workbook

Solved
Jef35_0206 Posted messages 4 Status Member -  
Jef35_0206 Posted messages 4 Status Member -
Hello,

I am seeking your help.

Indeed, I have written a macro and it works, but only on one sheet.

Here it is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Union([B17:B41], [C17:C41])) Is Nothing Then
If IsEmpty(Target) Then
Target = "X"
Cancel = True
Else
If Target = "X" Then
Target = ""
Cancel = True
End If
End If
End If
End Sub

However, I would like it to run for all the sheets (200) in the workbook.

I have done some research and it results in this:

Sub Dosomething()

Dim Ws As Worksheet

Application.ScreenUpdating = False
For Each Ws In Worksheets
Ws.Select
Call Runcode
Next
Application.ScreenUpdating = True
End Sub
Sub Runcode()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Union([B17:B41], [C17:C41])) Is Nothing Then
If IsEmpty(Target) Then
Target = "X"
Cancel = True
Else
If Target = "X" Then
Target = ""
Cancel = True

Next Ws

End Sub

But it does not work (error message: Compilation error: End Sub expected).

NOTE: I inserted a module and wrote this code there.

I hope I have clearly stated my problem.

Just as I hope someone can help me.

Thank you in advance.

Jef

3 answers

  1.
    cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Hello,

    to put in ThisWorkbook: 

    Option Explicit Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 'your code End Sub


    --
    @+ The Woodpecker
    1
  2. Jef35_0206 Posted messages 4 Status Member
     
    Hello cs_Le Pivert,

    First of all, thank you for responding so quickly.

    I have written in ThisWorkbook:

    Option Explicit
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Union([B17:B41], [C17:C41])) Is Nothing Then
    If IsEmpty(Target) Then
    Target = "X"
    Cancel = True
    Else
    If Target = "X" Then
    Target = ""
    Cancel = True
    End If
    End If
    End If
    End Sub

    But when I double-click on a relevant cell, the "X" does not appear.

    I'm sorry, as a beginner, I cannot see everything that needs to be done.

    But thank you again.
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      It works!

      Between B17 and B41 as well as C17 and C41, when double-clicked, if empty we put an x, otherwise we remove the x

      Is this the expected result?
      0
  3. Jef35_0206 Posted messages 4 Status Member
     
    Absolutely!

    I must have done something wrong.

    So, I did the process again.

    In the workbook, I open VBA (right-click, view code).

    I double-click on ThisWorkbook. A field opens and I write the code in question.

    And then I save it in a format that supports macros.

    And yes, it works!

    Wonderful!

    Thank you a thousand times and good luck with everything.

    Jef
    0