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.

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.

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.


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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now