View original (French)

VBA workbook that interferes with others

Solved
Sansandrine Posted messages 94 Status Member -  
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   -
Hello,
I have a file that contains macros which interfere with other files and I don't know how to make the macro apply only to this file.
When I open another Excel file, I get:
Runtime error 9: the index does not belong to the selection
And when the file is the only one open, there are no problems. I tried to define the workbook at the beginning of the macro: woorkook(« ... »).sheets(« men_ext »).

Also, I had to delete all the other sheets from this workbook because they were interfering too, but that's not the most serious issue, I mainly want to be able to work on other workbooks at the same time as this one without getting the error message.

I hope someone can help me.
Thanks in advance.

Here is my file: http://www.cijoint.fr/cjlink.php?file=cj201006/cij3nDGLTY.xls

Configuration: Windows XP / Firefox 3.6.2/EXCEL 2003

5 answers

  1.
    Sansandrine Posted messages 94 Status Member
     
    Still no one?
    0
  2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    Your message is not precise enough for us to provide an adequate response.

    It's probably your macro that is not correctly coded, but without seeing it, it's impossible to guess what isn't working: the possibilities for error are endless!

    If you could share an example of your workbook, without identifiable and private data, but with the structure of your data and the macros on http://www.cijoint.fr/ and then post the generated link here, that would make things easier.
    --

    Always zen
    0
    1. Sansandrine Posted messages 94 Status Member
       
      But the link is in my message:
      http://www.cijoint.fr/cjlink.php?file=cj201006/cij3nDGLTY.xls
      0
  3. Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
     
    Hello,
    I just saw the post and it’s a procedure that I wrote: 
    Private Sub Worksheet_Calculate() Sheets("MEN_EXT").Activate If ValVerif = "" Then Verification End If End Sub
    Indeed, if the workbook is open and another one is opened, the procedure triggers and causes an error.
    It’s strange, I will look into it.
    For your information, the relevant workbook can be found at: http://www.cijoint.fr/cjlink.php?file=cj201006/cij3nDGLTY.xls
    --
    Best regards.
    Le Pingou
    0
  4. Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
     
    Hello Sansandrine,
    Replace the existing procedure with this one: 
    Private Sub Worksheet_Calculate() If Not ActiveWorkbook.Name = "MEN EXT avec calcul de surfaces.xls" Then Exit Sub Sheets("MEN_EXT").Activate If ValVerif = "" Then Verification End If End Sub

    Note: make sure the workbook name is correct:
    "MEN EXT avec calcul de surfaces.xls"
    Best regards.
    Le Pingou
    0
    1. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello Le Pingou,

      Indeed, I didn't have the workbook with me when I sent my message and I hadn't thought about "calculate".

      This function can be useful but it has the major drawback of being triggered constantly, with every modification to the relevant workbook or not. It is also active, of course, with any change to a sheet by macro, which means that it really needs to be very lightweight, perfectly contained, and used sparingly.
      0
    2. Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
       
      Hello gbinforme,
      Thank you very much for this valuable information.
      I have added a check on the activated workbook's name in my code and it works.
      Have a great weekend.
      Regards.
      The Penguin
      0
  6. Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
     
    Hello Sansandrine,
    Is it good now with this correction: https://forums.commentcamarche.net/forum/affich-17916795-vba-classeur-qui-interfere-avec-les-autres#8
    --
    Best regards.
    Le Pingou
    0
    1. Sansandrine Posted messages 94 Status Member
       
      Yes, that works well, THANK YOU! This way, I can work with several spreadsheet files at the same time.
      However, is there anything I need to add so that it no longer interferes with the other sheets in the spreadsheet if I want to add more?
      0
    2. Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
       
      Hello,
      Please replace the existing procedure with the following:

      Private Sub Worksheet_Calculate()
      If Not ActiveWorkbook.Name = "MEN EXT avec calcul de surfaces.xls" Then Exit Sub
      If Not ActiveSheet.Name = "MEN_EXT" Then Exit Sub
      Sheets("MEN_EXT").Activate
      If ValVerif = "" Then
      Verification
      End If
      End Sub

      I apologize for the inconvenience caused.

      Regards.
      The Penguin
      0
    3. Sansandrine Posted messages 94 Status Member
       
      You don't have to apologize! You really helped me a lot!!
      And it allows me to improve my VBA skills!
      Thanks again for taking the time to help me.

      Sansandrine
      0
    4. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello Le Pingou,

      By avoiding negations and putting things positively, it can also be written as: 

      Private Sub Worksheet_Calculate() If ActiveWorkbook.Name = "MEN EXT avec calcul de surfaces.xls" _ And ActiveSheet.Name = "MEN_EXT" _ And ValVerif = "" Then Verification End Sub

      I like to simplify as much as possible to facilitate later understanding...

      Perfection is attained, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry

      Warm regards and have a nice day.
      0
    5. Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
       
      Hello gbinforme,
      It’s always a great pleasure for me to note your advice and put it into practice.
      Best regards.
      The Penguin.
      0