none
Excel, we found a problem with some content ... RRS feed

  • Question

  • I made an excel file and made some macro's which are working as they should.

    When I then insert a "button" to activate the macro, I save the file, close it and re-open, everything is ok.

    But when I then "press" the "button" so that the macro is activated (and works as he should).  If I then save, close the file and open it, I get the message " We found a problem with some content in '....' Do you want us to try to recover as much as we can? If you trust the source of the workbook, click yes.

    Then I get:

    Repaired Part: /xl/worksheets/sheet2.xml part.

    Then everything seems quite normal, but my "button" is not there anymore.

    The text in my macro is as below.  I found in some other comments on the internet that it could have to do with the "String", but I am only starting in this matter and have no idea what I should do.  Somebody can help?

       Dim rSource As Range, rDV As Range, r  As Range, csString As String
        Dim c As Collection

        Set rSource = Sheets("Data").Range("B2:B2000")
        'Set rSource = Sheets("Data").Range("B2:B2000" & LR).SpecialCells(xlCellTypeVisible)
        Set rDV = Sheets("Select").Range("B2")
        Set c = New Collection
        csString = ""
        On Error Resume Next
        For Each r In rSource
            v = r.Value
            If v <> "" Then
                c.Add v, CStr(v)
                If Err.Number = 0 Then
                    If csString = "" Then
                        csString = v
                    Else
                        csString = csString & "," & v
                    End If
                Else
                    Err.Number = 0
                End If
            End If
        Next r
        On Error GoTo 0

        'MsgBox csString

        With rDV.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=csString
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = False
        End With

    Thursday, January 4, 2018 9:28 PM

All replies

  • Hi,

    I've checked your code, and found
    (1) a variable "v" is not defined
    (2) run-time error occurs here:
         
    Please specify where is your code (I guess it is in a [buton]) and provide code without run-time error.
    Or share your file via cloud storage such as Dropbox, OneDrive, etc. in order for us to identify your issue.

    Regards,

    Ashidacchi

    Friday, January 5, 2018 7:57 AM
  • Hello,

    Thank you for the reply.  I can send you the file with a cloud storage system, but can I get your e-mail address to send you the link?

    Kind regards,

    Friday, January 5, 2018 1:21 PM
  • Hi,

    Can't you share your file with all who hope to help you, including me?

    Sorry, I would not like to open my email adderss. If you provide yours, I will send an email (you can see mine).

    Regards,


    Ashidacchi

    Friday, January 5, 2018 1:38 PM
  • Hello,

    Indeed, I can work as you mention.  Find enclosed the link:

    https://abcdiesel-my.sharepoint.com/personal/lv_abcdiesel_be/_layouts/15/guestaccess.aspx?docid=0c541e7945f134a6a854fae2ff9becaad&authkey=AaQkrk1FHP_u8MYYKxppIz8&expiration=2018-01-08T23%3A00%3A00.000Z&e=0b100fdf4887482e9795bd3604ce7d89

    This file has the macro's and also "buttons".

    1) If I run the macro's directly without using the buttons to activate them, they work as intended.

    2) If I just push a button that activates a macro, they also work as intended, but if I then save, close and open again, all the button's are gone.

    3) If you just open the file, do not use the button's and close it again, next time everything is still ok.

    I am not at all an expert and cobined different VBA code's together.  I hope you can help.

    Kind regards,

    Friday, January 5, 2018 8:43 PM
  • Sorry, better use this link, with the other you can not open the file in the browser.

    https://abcdiesel-my.sharepoint.com/personal/lv_abcdiesel_be/_layouts/15/guestaccess.aspx?folderid=0f8d18cc934a64cd1bc1e2f9eab067911&authkey=AfH-lZutC_iVxHPwcIQB8UU&expiration=2018-01-08T23%3A00%3A00.000Z&e=bd17577740f040288f15b6d4f88e45ec

    Kind regards

    Friday, January 5, 2018 9:08 PM
  • Hi Probleem,

    Thank you for sharing. I could download and open it.
    I will examine it later (I have many to do). Please give me a time....

    Regards,

    Ashidacchi

    Friday, January 5, 2018 10:45 PM
  • Hi Prebleem,

    I'm struggling with your code...
    There is no data in the sheet "Data", so I cannot continue to modify your code, i.e. I've encountered run-time error.
      

    Please provide sample data for the sheet "Data". I hope over 10 rows of data.

    Regards,

    Ashidacchi

    Saturday, January 6, 2018 1:18 AM
  • Hello,

    I added some imaginary data to the file.  If you do a new download, you should have it.

    thanks already.

    Saturday, January 6, 2018 1:25 PM
  • Hi,

    I have modified your file and shared via OneDrive:
    2018 01 07 Ash-Parameter lijst PVR v5 met macro.zip
    https://1drv.ms/u/s!AhzOJeY5F3-fj6VuYquJmYXtykRx_g

    If it is helpful, please mark this post as an answer.

    Regards,

    P.S.
    Please don't get angry about the following comment.
    There are so many duplicated and unnecessary description in your code. It would be caused from recording macro without edit, and copying them to many places.
     
    When you will refine/improve this, please separate long code into small pieces and avoid duplicated code. 

    Ashidacchi

    • Proposed as answer by Terry Xu - MSFT Wednesday, January 10, 2018 2:01 AM
    Sunday, January 7, 2018 4:44 AM
  • Hello,

    Thank you for the work, it works for good now and does not block anymore, great!  I also certainly understand your comment.  The code was created as a combination of other code I had in other file's.

    But, with your code, I am still struggling with one thing.  The idea is to initiatie the file to start, that works.  But when I then choose for example laser FL40 it is the idea that the other choices from lens, thickness and so on are limited to the one's are then only available with the FL40 when one pushes the "Filter Laser button".  This filter happens on the "Data" page, but not on the drop down list on the "select" sheet.  That was also always my major problem to get that fixed.

    With the last button "Filter and copy" it is then just the idea to copy the data as filtered from the data page to the select page.

    Kind regards,


    • Edited by Probleem Sunday, January 7, 2018 9:16 PM
    Sunday, January 7, 2018 9:16 PM
  • Hi,

    I hope you will solve problems one by one. If what you mentioned above is different from your first problem, please create a new thread. Otherwise, this thread will have many posts, and contents will not indicate its title, ant that will mislead others who have the same issue as you.

    Putting that aside, I don't know all about what you want to do. I'm afraid you didn't specify features of each button. Please explain what each button should work, what value should be put in each cell... i.e. whole scenario of the file.

    Regards,

    Ashidacchi

    • Proposed as answer by Terry Xu - MSFT Wednesday, January 10, 2018 2:02 AM
    Monday, January 8, 2018 12:35 AM
  • Hi,

    I suspect something is done by sheets which I can not access.
    Please delete sheets which are hidden or protected, or remove password from ThisWorkbook.
    And share the file again. Otherwise, I can not control your file as I expect.

    Regards,

    Ashidacchi

    Monday, January 8, 2018 2:07 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, January 8, 2018 9:11 AM
  • Hello Probleem,

    It seems that your original issue has been resolved by Ashidacchi. I would suggest you mark helpful reply to close this thread. If you have any other issue, please feel free to post new threads to let us know.

    Thanks for understanding.

    Best Regards,T

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 10, 2018 2:04 AM