View original (French)

The macro that runs correctly once, then nothing afterwards!!

Solved
bassmart Posted messages 281 Registration date   Status Member Last intervention   -  
bassmart Posted messages 281 Registration date   Status Member Last intervention   -
Bonjour à tous!

I have a problem with my file that contains several macros. I have a macro that activates when double-clicking (Worksheet_BeforeDoubleClick) in column C of the "Coordonnées" sheet to modify the value of that cell. I have another macro that converts all entries on this sheet to uppercase (UCase) as well.

It works perfectly the first time, but when I try to modify another value by double-clicking in the column, nothing happens anymore, and even other macros on this sheet stop working as well.

What is happening?

What can I do to correct the situation?

Thank you in advance for your help!

Here are the macros that are directly on my sheet "Coordonnées" 

Private dlig As Long Private PL As Range Public var As Variant Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim nValue As String Dim NewVal As String Dim f As Worksheet Dim sheetName As String Application.ScreenUpdating = False For Each f In ActiveWorkbook.Worksheets f.Unprotect Next nValue = ActiveCell.Value If Not Intersect(Target, Range("c5:c" & [a1048576].End(xlUp).Row + 1)) Is Nothing Then If MsgBox("Do you want to modify the survey number?", _ vbYesNo + vbQuestion, "MODIFY") = vbYes Then NewVal = UCase$(Application.InputBox("New survey number?", "MODIFICATION OF NUMBER", Type:=2)) ActiveCell = NewVal If InStr(1, nValue, "C") = 1 Or InStr(1, nValue, "M") = 1 Then sheetName = "CPTU" ElseIf InStr(1, nValue, "F") = 1 Then sheetName = "FORAGE" ElseIf InStr(1, nValue, "Z") = 1 Or InStr(1, nValue, "FZ") = 1 Then sheetName = "Piezometers" ElseIf InStr(1, nValue, "I") = 1 Then sheetName = "Inclinometers" Else sheetName = "" MsgBox "The value was not found in the other sheets! Update the sheets on the home page!", vbCritical End If If Len(sheetName) > 0 Then Sheets(sheetName).Columns(1).Replace nValue, NewVal, LookAt:=xlWhole, SearchOrder:=xlByColumns End If If var <> Target Then var = Target.Value MsgBox "Don't forget to change the number in the ABBREVIATION column!", vbExclamation, "IMPORTANT" End If Else ActiveCell.Select End If End If For Each f In ActiveWorkbook.Worksheets f.Protect Next Application.ScreenUpdating = True ActiveCell.Offset(-1, 0).Select 'Range("B5").Select End Sub Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Target.Column >= 3 And Target.Column <= 5 Then 'disable Excel events: avoid recursive calls after uppercase conversion Application.EnableEvents = False Target = UCase(Target) End If 'enable Excel events Application.EnableEvents = True If Target.Cells.Count > 1 Then Exit Sub If Target.Row < 5 Or Target.Column <> 5 Then Exit Sub If Target.Value = "" Then Cells(Target.Row, 1).ClearContents If Range("A5") <> "" Then dlig = Range("E5").End(xlDown).Row Set PL = Range("A5:A" & dlig) PL.Value = Range("a5").Value End If Dim T As Range, i& Set T = [TableauCoord] Application.EnableEvents = False On Error Resume Next 'safety If T.Rows.Count < 4 Then Application.Undo 'undo Else '---remove empty rows--- For i = T.Rows.Count - 1 To 4 Step -1 If T(i, 1) = "" Then T(i, 1).EntireRow.Delete Next '---add row--- If T(T.Rows.Count, 1) <> "" Then Application.ScreenUpdating = False T(T.Rows.Count, 1).EntireRow.Insert T.Rows(T.Rows.Count - 1).FormulaR1C1 = T.Rows(T.Rows.Count).FormulaR1C1 T.Rows(T.Rows.Count) = "" Application.ScreenUpdating = True End If End If Application.EnableEvents = True ActiveSheet.Protect End Sub Private Sub worksheet_activate() Dim resultat As String Const Dossier As String = "6.02.06.MT.02." ActiveSheet.Unprotect If Range("a5") = 0 Then resultat = UCase(InputBox("Enter the Watershed Number!", "Watershed")) If resultat <> "" Then dlig = Range("E5").End(xlDown).Row Set PL = Range("A5:A" & dlig) PL.Value = Dossier & resultat End If End If ActiveSheet.Protect Range("b5").Select End Sub

3 answers

  2.
    PlacageGranby Posted messages 402 Status Member 26
     
    Hello,

    Unfortunately, I don't have the time to dive into your code.
    But, the best way to find the problem is with a step-by-step trace.

    We go to the VBA editor, and we click in the left margin to set a breakpoint.

    Then, double click, and see what the code does. You'll see.
    1 - if the macro executes or not with the double click.
    If it doesn't execute, it's a problem related to the event. The code is not in the right event.

    2 - if the macro executes, then you press F8 to follow its execution step by step to find out why it doesn't act as expected.

    Good luck.
    0
    1. bassmart Posted messages 281 Registration date   Status Member Last intervention   1
       
      Thank you very much PlacageGranby!

      What I noticed is that when I modify the value of a cell by double-clicking on it, it goes to the line 
      Activecell = NewVal
      , in the macro that is right after Worksheet_Change.

      And then it returns to the line 
      If Target.Row < 5 Or Target.Column <> 5 Then Exit [/contents/446-fichier-sub Sub]
      in my BeforeDoubleClic macro, at the line 
      If [/contents/1169-vbscript-les-fonctions-de-chaines-de-caracteres InStr](1, nValue, "C") = 1 Or InStr(1, nValue, "M") = 1 Then
      .

      So, what I understand is that it is a value change. So, it goes into the corresponding macro Worksheet_Change.

      At this point, I have a macro that ensures that all data entries are capitalized and another macro that adds rows to the end of my table when it is full.

      When I double-click again on a new cell, nothing happens anymore, I remain on line 1 of the BeforeDoubleClic macro!

      Is that my problem? I'm not too sure?
      0
    2. PlacageGranby Posted messages 402 Status Member 26
       
      You start with an assignment
      nValue = ActiveCell.Value

      Then, you input the new info
      NewVal = UCase$(Application.InputBox("New survey number?", "NUMBER MODIFICATION", Type:=2))

      And you change the active cell
      ActiveCell = NewVal

      But you use
      If InStr(1, nValue, "C") = 1 Or InStr(1, nValue, "M") = 1 Then

      nValue is a variable that has been assigned only once, so it still equals its old value. You assign NewVal to ActiveCell, but never to nValue.

      Is this intentional?
      0
      1. bassmart Posted messages 281 Registration date   Status Member Last intervention   1 > PlacageGranby Posted messages 402 Status Member
         
        Hello PlacageGranby!

        Yes, that's normal! For now it's working well!

        I use nValue to search for where the selected value is located in my other sheets. Then, with my input box, I assign it a new value that becomes my NewVal and that I use to replace the old value with the new one.

        Maybe my code isn't very well written, but it works! I'm a beginner in VBA!

        Thank you!
        0
  3. ThauTheme Posted messages 1564 Status Member 160
     
    I don't like Application.EnableEvents = False. If in your code there is an exit point where you forget to set it back to True, or if it crashes before resetting to True, event macros won't work anymore!
    I use a boolean like in this example: 

    Private TEST As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If TEST = True Then Exit Sub TEST = True 'the code... TEST = False 'to be set at all exit points If/Redo/Exit For/Exit sub/etc. End Sub

    In your Double Click code, you modify the Coordinates tab but also other tabs that each contain the event Change with Application.EnableEvents = False. Therefore, you need to check your code in all the tabs. I think your problem comes from there...

    The advantage of the boolean is that other event macros continue to work even if Test has not been set back to False...

    Another problem, you declare a public variable in the Feuil2(Coordinates) component. 

    Public var As Variant

    However, public variables must be declared at the very top of a standard module (like Module1 for example).

    To avoid unprotecting and then reprotecting your tabs all the time, I recommend this little snippet of code in the ThisWorkbook component: 

    Private Sub Workbook_Open() Dim O As Object 'declares the variable O (Sheet) For Each O In Sheets 'loops through all the sheets in the workbook O.Protect UserInterfaceOnly:=True 'protects the sheet while allowing macros to act Next O Sheets("Home").Select End Sub


    The UserInterfaceOnly argument allows modification by macro but not manually...

    --
    See you later,
    ThauTheme
    0
    1. bassmart Posted messages 281 Registration date   Status Member Last intervention   1
       
      Thank you ThauTheme for the response!

      Firstly, I understand what you mean regarding 
      Application.EnableEvents=False
      , but how do I modify my code with your suggestion?

      Right now, I’m a bit lost! Do I just add the lines of code as they are?

      For 
      Public var As Variant
      , it is located at the top of the Coordinates sheet. I found this on a forum; I wanted that when I modify a cell, it would return a message like "Don't forget to change the number in column X" on the screen. But it doesn’t work, even if I enter the same value again, it sends me the message.

      What problem could I have with it not being declared in a module?

      And for your suggestion regarding protection, it works very well! But only for the first modification I make in my Coordinates sheet. After that, I get an error message because my sheet is locked! It stops at the line ActiveCell in the following code: 
      If var <> Target Then var = Target.Value MsgBox "Don't forget to change the number in the ABBREVIATION column!", vbExclamation, "IMPORTANT" ActiveCell.Offset(-1, 0).Select End If


      Thank you very much!
      0
      1. bassmart Posted messages 281 Registration date   Status Member Last intervention   1 > bassmart Posted messages 281 Registration date   Status Member Last intervention  
         
        Hello everyone!

        Problem solved!

        Indeed, the problem was caused by 
        Application.EnableEvents=False
        . I added an expression 
        Application.EnableEvents=true
        at the end of my macro and that fixed my problem.

        And for the code proposed by thautheme for sheet protection, it works very well! It was again my problem with 
        Application.EnableEvents=False
        that was causing everything to crash!

        Here is my final code!: 
        Private dlig As Long Private PL As Range Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim nvalue As String Dim NewVal As Variant Dim f As Worksheet Dim valueRange As Range Dim Cpt As Integer Application.ScreenUpdating = False nvalue = ActiveCell.Value If Not Intersect(Target, Range("c5:c" & [a1048576].End(xlUp).Row + 1)) Is Nothing Then If MsgBox("Do you want to modify the survey number?", _ vbYesNo + vbQuestion, "MODIFY") = vbYes Then NewVal = UCase(InputBox("New survey number?", "NUMBER MODIFICATION")) If NewVal = "" Then ActiveCell.Offset(-1, 0).Select Exit Sub Else ActiveCell = NewVal End If If InStr(1, nvalue, "C") = 1 Or InStr(1, nvalue, "M") = 1 Then Set valueRange = Sheets("CPTU").Columns(1) ElseIf InStr(1, nvalue, "F") = 1 Then Set valueRange = Sheets("FORAGE").Columns(1) ElseIf InStr(1, nvalue, "Z") = 1 Or InStr(1, nvalue, "FZ") = 1 Then Set valueRange = Sheets("Piezometers").Columns(2) ElseIf InStr(1, nvalue, "I") = 1 Then Set valueRange = Sheets("Inclinometers").Columns(2) Else Set valueRange = Nothing MsgBox "The value was not found in the other sheets! Update the sheets on the homepage!", vbCritical End If If Not valueRange Is Nothing Then valueRange.Replace nvalue, NewVal, lookat:=xlWhole, searchorder:=xlByColumns End If If NewVal <> nvalue Then MsgBox "Don't forget to change the number in the ABBREVIATION column!", vbExclamation, "IMPORTANT" ActiveCell.Offset(-1, 0).Select End If Else ActiveCell.Offset(-1, 0).Select End If End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub
        0