View original (French)

Enable a macro on another sheet

Steeve999 Posted messages 16 Status Member -  
 paul -
Hello,

I coded a macro on a "sheet1" but it bugged so I copied it to another "sheet2" but I want the programming to apply to "sheet1" how can I do that ?
Thanks in advance.

1 answer

  1. paul
     
    Hello Steeve999,

    A macro can go in Module1 or Module2 (for example), and the placement in one module or another doesn’t matter; then the sub of the module does its job on one sheet or another: it’s up to you to write it correctly; so if it bugs, review your VBA code.

    If you still can’t manage, you can post your code in your next post on this forum (if not too long); otherwise, attach your Excel file.

    In both cases, clearly specify what the task is.

    Best regards
    0
    1. Steeve999 Posted messages 16 Status Member
       
      Hello Paul, I put the code in a module but it doesn’t work. Here’s the code: Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Set KeyCells = Sheets("Fiche_opérateur").Range("d6") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then If Range("d6") = "Sonomètre" Then Sheets("Détail résultats Sonomètre").Visible = True Sheets("Détail résultats Centrale LANXI").Visible = False Sheets("Fiche_opérateur").Range("a66:c66").EntireRow.Hidden = False Sheets("Fiche_opérateur").Range("a57:a58").EntireRow.Hidden = True Sheets("Fiche_opérateur").Range("a59").EntireRow.Hidden = False End If End If End Sub
      0
      1. paul > Steeve999 Posted messages 16 Status Member
         
        For the code location: This code is an event-driven subroutine => it should not be placed in a module (such as Module1 or Module2), but in the code of the relevant worksheet; so if it’s for Sheet1: press Alt+F11 to open Microsoft Visual Basic; on the left, in the top list: double-click Sheet1 => on the right, the code page for Sheet1; that is where you should copy the code (in Module1, don’t forget to delete that same code). --------------------------------------------------- I propose this VBA code: 
         ' KeyCells : on sheet "Fiche_opérateur", D6 Private Sub Worksheet_Change(ByVal Target As Range) With Worksheets("Fiche_opérateur") If Application.Intersect(.[D6], Range(Target.Address)) Is Nothing Then Exit Sub If .[D6] <> "Sonomètre" Then Exit Sub Worksheets("Détail résultats Sonomètre").Visible = True Worksheets("Détail résultats Centrale LANXI").Visible = False .[A66:C66].EntireRow.Hidden = False .[A57:A58].EntireRow.Hidden = True .[A59].EntireRow.Hidden = False End With End Sub
        Thank you let me know if that works for you.
        0