Delete Chart Series Across Sheets But Skip Protected Sheets

Mar 30, 2008

I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets

Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................

View 4 Replies


ADVERTISEMENT

Delete All Series In XY Scatter Chart

Aug 1, 2006

I am using the following code to delete all of the series in the chart. It is give type mismatch error.

Sub allchannelchart()
Dim i As Integer, s As SeriesCollection
ActiveSheet.ChartObjects("allchan").Activate
'ActiveChart.PlotArea.Select
For Each s In ActiveChart.SeriesCollection
s.Delete
Next s

View 2 Replies View Related

Delete Chart Series Via Macro Code

May 27, 2008

I have a spreadsheet (see attached) in which there are many series and i want to be able to delete most of the series except the first 4.

(The 0%, 5% 10% air voids and the 95% comp line)

iv got this code, which is attacted to a command button, which works fine to delete all the series but makes the graph a blank white box. id prefer it so it delete just the series not the graph "picture?" ...

View 7 Replies View Related

How To Password Protected All Sheets At Once

May 15, 2014

i want to protect my all sheets at once

View 3 Replies View Related

Macros On Protected Sheets

Nov 21, 2006

I have a spreadsheet with some macros in it. When I run the macros, I would like to unprotect the worksheet and then protect the spreadsheets when the macros are done. The excel tabs I would like to protect/unprotect are called Graph and Data.

View 3 Replies View Related

Add & Delete Multiple Chart Data Series Labels

Jan 16, 2008

My friend has set up an excel workbook with a sheet for every month (labelled Jan graphs, Feb graphs, Mar graphs etc) which has 7 pie charts per sheet. The problem is the pie charts are showing the 0% fields and the data labels are overlapping. I have found a macro that gets rid of the 0% labels but I need to repeat the macro for all the 7 charts on each sheet and all 12 worksheets for each month of the year. I am very new to VBA and coding of any type and have looked but can't find the solution.

The macro I am currently using for "Chart1" on "Jan graphs" is below. The charts are labelled Chart1, Chart2, Chart3 etc.

Sub ClearLabels()

Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _
Type:=xlShowLabelAndPercent

For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _
Chart.SeriesCollection(1).Points

View 8 Replies View Related

How To Unprotect A Workbook And Sheets That Already Protected

Mar 10, 2014

I have a workbook that is protected by password. I have to unprotect sheets.

How can I unprotect sheets and workbooks while I am updating or changing?

View 13 Replies View Related

Search Column A In All Protected Sheets

Nov 15, 2008

I need a simple Macro that will, sheet by sheet, search the entire workbook. However, it only needs to search Column A, and there need be no text box, because the user will not define the text. The search text will simply be "X"

The real issue that I'm having is that I need it to find the first item, then offer me the option of selecting "Next" "Previous" or "Cancel" "Cancel" of course would mean that the item found is the one being searched for.

View 14 Replies View Related

Custom View When Other Sheets Protected,

Nov 29, 2007

I have a workbook with 3 spreadsheets. When I put ptotection on 2 of the sheets, the custom view will not work on the other. Any way to make the views work?

View 5 Replies View Related

Comboboxes, Autofilter And Protected Sheets

Jun 12, 2006

Everything works fine except when I password protect the sheet. Then when the autofilter is used an error message is triggered and the error relates to the first range selected in the combobox code. However, the combobox itself works fine on the protected sheet as I included code to unprotect the sheet at the beginning of the code run by the combobox and reprotect it at the end.
Any advice suggestions gratefully received.

View 9 Replies View Related

Running Macros On Protected Sheets ....

Feb 19, 2007

I have the following code as workbook module but it is not working, I have tried fiddling with it but I cannot work out what is wrong, can anyone help please.

Private Sub Workbook_Open()

Dim ws As Worksheet ....

View 9 Replies View Related

Protected Zoom Level For All Sheets For All Users?

Dec 22, 2013

I've created a shared spreadsheet with multiple sheets. All the content was created to be visible without having to scroll at 87%. My question is can I protect or lock the zoom level so that when anyone else opens the spreadsheet, it will only be visible at 87%? I have my screen resolution at "Smallest" so not sure if that too will affect the viewing level.

View 7 Replies View Related

Getting Error When Userform Transfers To Protected Sheets

Jan 30, 2014

I have a userforms which transfers data to protected sheets.The userforms worked until i protected each sheet.Can there be any code which i add to the current userform code so it can still transfer the data to protected sheets

The other issue also is now that the sheets are protected the Outlining also does not work.Can this also be changed so the Outlining works on protected sheets

I have 5 userforms which all transfer data.

[Code].....

View 5 Replies View Related

Find & Replace On Protected Sheets Fails

Jan 10, 2007

I am attempting to run a macro that will do a find and replace on a protected sheet but this is not working. I am using the following code to Protect all Sheets, which I found on this site when workbook opens using the Workbook Open Event and uses UserInterFaceOnly :

Private Sub Workbook_Open()
Dim wSheet As Worksheet

For Each wSheet In Worksheets
wSheet.Protect Password:="111", _
UserInterFaceOnly:=True
Next wSheet
End Sub

And then a find and replace similar to this:

Sub FindAndReplace()
Selection.Replace What:="w", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

View 6 Replies View Related

Sort Data On Protected & Hidden Sheets

Sep 16, 2007

Is there a way I can sort multiple hidden protected worksheets with a password “Protect”
I have 12 worksheets “Jan”, “Feb”. Etc

“The Range on each worksheet is the same ("A11:CR200") ....

View 9 Replies View Related

Add Series Collection From Dynamic Sheets

Jul 29, 2014

I've created one file: Mappe1_results.xlsm

This file ask in another file "Mappe1_ground.xlsx" for "B" and "N". Now one new sheet is created with the name "month-2014" and shows me which Category (B) is how often referred in one month. After that one chart is created for a better representation. This can be done for every month (which month is selected by the user over the button "Auswertung" in Sheet "Tabelle1").

Now I have a few problems / requirements:

1. If one user is using (maybe) Jan as his selected month, in the sheet "Auswertung" should be one duplicated chart of the Jan Chart., with no other (previous) series. Because Jan has no previous conditions (prev. year).
1.1 If one user is using another month (maybe Feb), in the sheet "Auswertung" should be one chart with both series of Jan and Feb and so one (for the other months). So that in "Auswertung" the chart is one comparison over the months. Only Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct/Nov/Dec have one prev. month.

For example: If one user write "Apr" into the inputbox and "Mar" is in the sheet existing, so in the chart of "Auswertung" should be April and March shown.

Some functions are set in my macros, but the problem is that the results are not equal between the sheet "Auswertung" and maybe "Jan-2014" or "Feb-2014". Because in "Auswertung" we need called all Categories (they can be found in "Referenz" - A).

All what I want is in "Auswertung" one chart with all present categories of "Referenz" - A and the series of the created sheets by the user.

2. The next problem is, every created sheet has one legend "Anzahl im ..." - this legend of every series should be shown in "Auswertung", too. So that we know which color is for which month, u know.

View 2 Replies View Related

Retain Use Of Group/Outline On Protected Sheets/Worksheets

Oct 5, 2006

I have a sheet that I protect but i use the below code so that I can still use my gouping '+' or '-' symbols to hide/unhide rows.

ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True

This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons. Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up. For Reference Below is my full

Sub ProtectAll()
Dim ws As Worksheet
sSheet = Control.Name
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case sSheet1
Case Else
ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True
End Select
Next ws
End Sub

View 2 Replies View Related

Copy Row From Series Of Sheets To Sheet 1 In Workbook

Apr 20, 2012

Haven't done any VBA in a long time so I'm very rusty. I need to copy cells A2 - G2 from sheets 2 - 30 in a workbook to create rows Sheet 1, which will start out blank. This is the final part of longer process. Sheet 1 is used as a summary page.

View 2 Replies View Related

How To Copy Formula That References A Series Of Sheets

Feb 1, 2013

I have 4 sheets name A, B, C and D.

The D worksheet is a summary sheet that pulls in data from A, B and C.

e.g.

=A!A4
=A!C4

There are approx 50 of these references to Cell A. Is there an easy way to copy the same formulas but reference Sheet B and C without having to retype them all?

View 1 Replies View Related

Protected Sheets With Macros Giving Runtime Error 1004

Mar 3, 2009

I created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:

View 4 Replies View Related

Change VBA To Password Protect All Sheets To Allow User To Select Protected

Apr 8, 2009

I need to change VBA to Password Protect all sheets to allow user to select protected. I have:

View 2 Replies View Related

Fill Series That Combines Data From 2 Sheets In Blocks Of 10

Jun 5, 2006

I have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use as sort of a staging area to format info before bulk uploading to my product database. The products I'm working with at the moment have 10 subcategories below each of them. Sheet1 is the full product database listing. I place new subcategories in blocks of 10 that I plan to add to the database on Sheet2. Sheet2 also holds the default partial category names in cells A2-A11.

Part 1:

I am currently using the following formula on Sheet2, in column C to combine the category name value on Sheet1, with the default partial category name values on Sheet2 (A2-A11). I'll paste two blocks so you have a clear picture of what I mean by blocks of ten.

=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$2)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$3)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$4)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$5)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$6)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$7)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$8)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$9)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$10)..................

View 6 Replies View Related

Copy Macro Code Produced Cell Series To Other Sheets

Mar 10, 2008

I have got the following code to fill series and someone please help me that how to use this macro I can enter thses code in sheet1 on enter event and the result iwill be displayed in sheet2 and if any duplication in series then it is copied in sheet3....

View 9 Replies View Related

Paste Into Range But Skip Protected/Locked Cells

Feb 29, 2008

I've been trying to paste formula throughout a range of cells in the same column. Trouble is, I protect some sub- total cells and don't want the formula to paste over those sub-total formulas.

If I protect the sheet and paste the formula throughout the column including the protected cells, I would get an error message.

How can I paste the new formulas without getting into trouble of the protected cells?

View 8 Replies View Related

200 Series But Get (The Maximum Number Of Series Per Chart Is 255) Error Message

Aug 4, 2014

I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.

If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)

But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.

After the data is plotted it is the code below that gets the error:

[Code] .....

View 1 Replies View Related

Line Chart - Remove Series Code But What If No Series Exists?

Dec 22, 2011

I have the following code:

Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub

However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.

View 4 Replies View Related

Show Chart Data Series Labels On All Series. 2007

May 31, 2008

I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.

I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?

View 4 Replies View Related

Same Chart On Multiple Sheets

Oct 22, 2013

I have a sheet open that I would like to have a chart like...umm...floating in front of?

First off I want the same "chart" (or block of cells) to be on all sheets of my file so if I enter information into it on one of the sheets it'll be the same on all of them. That's the most important part.

The other thing I'm looking to do with it is I would love for it to remain stationary when I scroll up and down on the sheets. So basically the left half of my screen would scroll up and down but the chart on the right would stay put in my line of vision no matter how far down I scroll.

View 9 Replies View Related

For Each Sht In Sheets Bombs On Chart

Dec 27, 2006

I created a chart with F11. It becomes the first, well, "sheet" in the workbook, Chart1. It's active when I go

Function foo()
Dim wksht As Worksheet
For Each wksht In Sheets

Boom. Now what do I do? I can't just go activesheet.next.select in error trapping code - can I??!!

View 9 Replies View Related

Several Sheets With Data - Chart

Aug 8, 2007

I'm currently working on a workbook that has over 50 different sheets. On each sheet there is a column K that I want to graph if desired. From the Main sheet I would like to have checkboxes or something, so that when I press on it, the data from sheet x is added in the graph. Can this be easily done? It's not a problem to hardcode the which-button-calls to-which-sheet (just takes some time), the main thing I cant work out, is how to add a series to a chart, how do delete a series from a chart and so on..

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved