Excel: VBA to unlock a chart from a protected sheet


I have a password protected sheet that contains a chart that needs to be updated.  When I run the macro it basically states that it can get to my chart.  What can I add to my code?

Excel 2007
Sub UpdateGenericChartArray(ByVal lbItemCount As Integer, dataRange As String, datasheet As String, chartSheet As String, chartID As String, obUsed As Boolean)
Dim myRng As Variant
Dim Target As String
Dim i As Integer
Dim mySeries As Series
Dim myRange As Range
Dim foundData As Boolean
Dim chrSer As Series

    foundData = False
    With ActiveChart
        i = .SeriesCollection.Count - 1
        For Each mySeries In .SeriesCollection
            If i > 0 Then
                i = i - 1
            End If
        Next mySeries
    End With
    For i = 0 To lbItemCount - 1 'for each item in the listbox that was selected
        Target = lbArray(i) 'keep the code as similar as possible to original combobox routine
        On Error Resume Next
        If Not obUsed Then obMainChart = 4 'go with last 8 weeks as default, to keep all charts working and to chart things that don't have the option button for date range, they will go with last 8 months
        myRng = Application.WorksheetFunction.VLookup(Target, Range(dataRange), Range(dataRange).Columns.Count - obMainChart, False)
        On Error GoTo 0

        If Err.Number = 0 And Not IsEmpty(myRng) Then
            With ActiveChart
                If i = 0 Then 'set x-axis labels only once
                    Set myRange = Range(myRng)
                    .SeriesCollection(1).XValues = "'" & datasheet & "'!" & Range(Cells(5, myRange.Cells(1, 1).Column), Cells(5, myRange.Cells(1, myRange.Columns.Count).Column)).Address
                End If
                If i > 0 Then .SeriesCollection.NewSeries ' don't create the new series till past the first one
                .SeriesCollection(i + 1).Values = "'" & datasheet & "'!" & Range(myRng).Address
                .SeriesCollection(i + 1).Name = Left(Target, InStr(Target & " ", " ") - 1) 'add a space after, just in case it doesn't have a first one -> name = initial word before a blank space of the combobox selection
            End With
        foundData = True
            'do nothing
            foundData = False
        End If
    Next i
    'last step here is to now format the resulting chart
    Application.ScreenUpdating = False
    If foundData Then
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Text = Target
            Selection.TickLabels.NumberFormat = Range("'" & datasheet & "'!" & myRange.Cells(1, 1).Address).NumberFormat
                For Each chser In .SeriesCollection
                 chser.Trendlines.Add xlLogarithmic
        End With
        SendKeys "{ESC}"


        With ActiveChart
            .HasTitle = True
            .ChartTitle.Text = "                  Please select an ID #" & Target
        End With
    End If
End Sub

Open in new window

Maliki HassaniAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

dlmilleConnect With a Mentor Commented:
So my comments remain:


  call unlockSettingsOnly(ActiveSheet) '

and at the end of the chart routine

  call LockSettingsOnly(Activesheet, true)

which just locks the objects, cells, etc., and protects them with your embedded password, as you may recall...

Just like we originally did it.

Wouldn't you add back the routine calls to unlock settings?  If you still have those routines, after your statement:

     foundData = false


     Call UnlockSettingsOnly(ActiveSheet)
     Call UnlockSettingsOnly(sheets(datasheet))


Maliki HassaniAuthor Commented:
Hi Dave

I was needing to make a modification to spreadsheet, concerning how slow it was running.  The protection level is not as important to my management team.  However, only protecting the sheet was necessary.  Which we had but it required it to unlock and lock the sheets.  Sometimes more than twice depending on the listbox.  So what I decided to do was go to the sheets that will be visible  and click on "review" and "protect", and add a password. So my question now is that I just need a line of code that when firing the chart, it will ONLY unlock the chart. and then lock it afterwards.  the goal is to get this thing running faster.  Without any sheets protected it runs really nice!  By the way, I was using your call UnlockSettingsOnly and that was still slow like we knew it would.
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

All UnlocksettingsOnly does is unprotect the active sheet and pass the password to that routine:

Sub UnlockSettingsOnly(mysheet as worksheet)

  mysheet.unprotect password:=shtpasswd

end sub

its not anymore complicated than that.  Its Excel that runs slowly on an unlock, not the code.

So - the only want to update the chart is to unlock the sheet where it resides.  If its locked, you're not allowed to change it.

Maliki HassaniAuthor Commented:
Oh yeah!  I am pop locking and dropping charts like it's hot!  Great advice Dave...
Maliki HassaniAuthor Commented:
Dave:  We still have an issue..  When I removed the Calls to unlock and lock the sheets, it is not allowing me to update the chart.  I will keep looking for answers but I think that was the reason why it was working for you..  We had it still calling to unlock and lock, and thinking it was actually working.
Maliki HassaniAuthor Commented:
Quick fix to this whole problem.  I removed all the charting routines that has unlock and lock.  I added the following to the individual sheets.

Private Sub ListBox1_ChartUpdate() 'when something else on the spreadsheet is selected
    ActiveSheet.Unprotect Password:=shtPassWd    
    lastLB = ListBox1.Name
    Call UpdateChart_ArrayOnLBLostFocus(ListBox1, "Surveillance_Range", "Surveillance Data", "Surveillance", "Chart 238", True)
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=shtPassWdEnd Sub
Maliki HassaniAuthor Commented:
The spreadsheet is faster.  Take a look and tell me what you think?
All Courses

From novice to tech pro — start learning today.