View original (French)

Run a macro for all sheets in a workbook

Solved
Jef35_0206 Posted messages 4 Status Membre -  
Jef35_0206 Posted messages 4 Status Membre -
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 réponses

Réponse 1 / 3
cs_Le Pivert Posted messages 8437 Status Contributeur 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
Réponse 2 / 3
Jef35_0206 Posted messages 4 Status Membre
 
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
cs_Le Pivert Posted messages 8437 Status Contributeur 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
Réponse 3 / 3
Jef35_0206 Posted messages 4 Status Membre
 
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