none
Simple VB code stops working suddenly. RRS feed

  • Question

  • I have been working with a pretty basic spreadsheet for some time for an engineering project. I have used VB to populate the title of several charts. I recently added a new chart and did repeated the code, but all of a sudden the code stopped running with error message listed below.

    The code in charts that were working suddenly stopped working as well as the new chart. Any ideas, clues? 

    "Run-time error '2147467259 (80004005)

    Method 'Hastitle' of object '_Chart' failed

    The code was 

    With ActiveChart
     .HasTitle = True
     .ChartTitle.Text = "='Insitu culv'!TitleStruct"
     
    End With

    Sunday, December 3, 2017 1:41 AM

All replies

  • I may have stumbled on the reason, although I don't understand the cause and effect.

    I had accidentally protected the new graph sheet. 

    After I removed the protection of that sheet, the rest of the sheets started working properly as well.

    ????

    Sunday, December 3, 2017 1:52 AM
  • You can Protect the sheet with UserInterfacedOnly:=True so that code can make changes.  I usually do it in ThisWorkbook's Open event.
    Monday, December 4, 2017 1:23 AM
  • Hi currang2,

    if the sheet is locked, you cannot perform any operation, add, modify , delete data from that sheet.

    so you need to first unlock your sheet from code before you perform any operations.

    after completing your operation , you can again protect the sheet using code.

    below is an example to protect and unprotect sheet using VBA.

    Sub ProtectAll()
    Dim sh As Worksheet
    Dim myPassword As String
    myPassword = "password"
    
    For Each sh In ActiveWorkbook.Worksheets
    sh.Protect Password:=myPassword
    Next sh
    
    End Sub


    Sub UnrotectAll()
    Dim sh As Worksheet
    Dim myPassword As String
    myPassword = "password"
    
    For Each sh In ActiveWorkbook.Worksheets
    sh.Unprotect Password:=myPassword
    Next sh
    
    End Sub

    Reference:

    Worksheet.Protect Method (Excel)

    Worksheet.Unprotect Method (Excel)

    Regards

    Deepak


    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.

    Monday, December 4, 2017 2:02 AM
    Moderator
  • Hi currang2,

    did you got the answer for your question above from the suggestions provided by community members?

    if yes, I suggest you to mark that suggestion as an answer which helped you to solve your issue.

    if your issue is still exist , then let us know about that.

    we will try to provide further suggestions to solve the issue.

    this thread is open and will remain open, until you mark the answer.

    so I suggest you to mark the answer, if your issue is resolved.

    it will helpful to other community members in future, who will meet with same kind of issue.

    Thanks for your understanding.

    Regards

    Deepak


    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.

    Thursday, December 14, 2017 1:52 AM
    Moderator