Excel:  VBA to unlock a chart from a protected sheet

Posted on 2011-03-10
Last Modified: 2012-05-11

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

Question by:Maliki Hassani
  • 5
  • 3
LVL 41

Expert Comment

ID: 35099794
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))



Author Comment

by:Maliki Hassani
ID: 35100359
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.
LVL 41

Expert Comment

ID: 35100488
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.

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

LVL 41

Accepted Solution

dlmille earned 500 total points
ID: 35100559
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.


Author Comment

by:Maliki Hassani
ID: 35101887
Oh yeah!  I am pop locking and dropping charts like it's hot!  Great advice Dave...

Author Comment

by:Maliki Hassani
ID: 35103657
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.

Author Comment

by:Maliki Hassani
ID: 35104804
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

Author Comment

by:Maliki Hassani
ID: 35104835
The spreadsheet is faster.  Take a look and tell me what you think?

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question