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 42

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 42

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.

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

LVL 42

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

829 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