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
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
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
May 15, 2014
i want to protect my all sheets at once
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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