Excel:  VBA to unlock a chart from a protected sheet

Posted on 2011-03-10
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 42

Accepted Solution

dlmille earned 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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