Toggle Seriescollection In A Chart
Apr 7, 2007
Series are added to a chart on a chartsheet in WB Retirement.xls with the following macro:
Sub AddMyWife()
Application. ScreenUpdating = False
Sheets("Chart").Select
With ActiveChart.SeriesCollection.NewSeries
. Name = "MyWife"
.Values = "='Retirement.xls'!MyWife"
End With
ActiveChart.SeriesCollection("MyWife").Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerStyle = xlDiamond
.MarkerSize = 3
.MarkerForegroundColorIndex = 3
.MarkerBackgroundColorIndex = 3
End With
Application.ScreenUpdating = True
ActiveChart.Deselect
End Sub
The series is deleted with:
Sub DeleteMyWife()
Sheets("Chart").Select
On Error Resume Next
ActiveChart.SeriesCollection("MyWife").Delete
ActiveChart.Deselect
End Sub...
View 4 Replies
ADVERTISEMENT
Jun 22, 2006
Is there anyone out there who've used SeriesCollection.Values before? As I know, there's no way to do double indexing for the values of the point referencing to. So something like the following would not be possible.
CCArray(i) = ActiveChart.SeriesCollection(i).Values
CCArray(i, j) = ActiveChart.SeriesCollection(i).Values(j)
CCArray(I, j) = ActiveChart.SeriesCollection(i).Values
But I need to get the values of the points by some means. Is there anyone who could help me find out the best way for me to put the values into a doubly for...next loop. The following is the code that has the idea of what I'm trying to achieve but , of course, the syntax does not comply to what VB allows.
Redim CC_Array(1 To SeriesCount, 1 To 13) As Integer
For i = 1 To SeriesCount
For j = 1 To 13
CC_Array(i, j) = ActiveChart.SeriesCollection(i).Values(j)
Next j
Next i
View 3 Replies
View Related
Aug 16, 2007
I am trying to generate a series of charts. Each row (and 3 columns) is the sole series of each chart, but am having trouble setting the values for the chart seriescollections. I'm using the following
Sub DrawCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long
Dim PointRow As Long
Set Ws = ThisWorkbook.Worksheets("Global Summary")
LastRow = 6
For CurrRow = 5 To LastRow
Set NewWs = ThisWorkbook.Worksheets.Add
NewWs. Name = Ws.Range("A" & CurrRow).Value
Set cht = ThisWorkbook.Charts.Add
With cht
.ChartType = xlBarClustered..................
View 4 Replies
View Related
Apr 20, 2014
How to see if a SeriesCollection that has been named exists so when the corresponding toggle button it clicked it turns on or off the correct corresponding dataseries.
When I create a series I use something like:
[Code] ............
Where the range "tblJim[Quizes]" contains the quiz scores which are the data series pointes and "=Data!$C$6:$C$35" has the respective dates.
When that toggle button is clicked I need to test if the SeriesCollection "Jim" exists so if it does I can delete it and if not, create it.
Also, as much as I hate to multi-subject but usually do anyway, I am wondering if there is good example of doing the same ends by a different strategy - a chart with everyone on it and by applying data filters to the dataset I would turn on and off people's data.
View 2 Replies
View Related
Jun 1, 2006
I am trying to create a chart that will Add or Remove data entries as toggle buttons are clicked. I've fallen at the first hurdle. When I remove one series from the source data, all the SeriesCollection numbers will change down. e.g. ->
ActiveChart.SeriesCollection(2).Delete
where (2) will change to (1) when I delete a series. This then screws up subsequent Removal macros. Is there any way I can either lock the SeriesCollection number?
View 2 Replies
View Related
Nov 7, 2006
I try to plot an XY graph with a VB macro but I don't manage to select the right name of the active sheet.
Sub plot()
WksName = ActiveSheet.Name
Worksheets(WksName).Activate
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets(WksName).Range("A11:F12"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection(1).XValues = "=WksName!R5C8:R643C8"
ActiveChart.SeriesCollection(1).Values = "=WksName!R5C9:R643C9"
ActiveChart.SeriesCollection(1).Name = "=""Specular"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=WksName
End Sub
View 6 Replies
View Related
Sep 4, 2006
I'm trying to make a macro that generates a bubble graph from a defined range. I however can't seem to get the SeriesCollection.NewSeries to work.
Dim nr As Integer
Dim myChart As ChartObject
Set myChart = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
myChart.Chart.SetSourceData Source:= Sheets("Sheet1").range("A2:D2"), PlotBy:= _
xlRows
myChart.Activate
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Cells(nr + 1, A)
.XValues = ActiveSheet.Cells(nr + 1, B)
.Values = ActiveSheet.Cells(nr + 1, C)
.BubbleSizes = ActiveSheet.Cells(nr + 1, D)
With .Interior
If Cells(nr + 1, G) >= 50 Then
.ColorIndex = 4
Else
.ColorIndex = 10
End If
End With
End With
myChart.Chart.ChartType = xlBubble
View 6 Replies
View Related
Oct 3, 2007
Is there any way you can use VBA to assign a key to toggle between two different cell formats? I want to toggle a cell from white border with black text to black border with white text and visa versa, with the same key or button. I've searched the posts but can't find this mentioned.
View 6 Replies
View Related
May 26, 2014
How can I toggle between percentage and actual value to display on a pie chart?
View 1 Replies
View Related
Jun 18, 2008
I am working on a sheet in Excel 2007 and am having trouble with Excel toggling with a checkbox. What I want is for a cell to have "A" in it if the box is checked and a "B" if it isn't. The code I have is:
Sub CheckBox6_Click()
If CheckBox6 = True Then
Range("B20") = "A"
Else
Range("B20") = "B"
End If
End Sub
View 9 Replies
View Related
Jan 3, 2007
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Address "$B$9" Then Exit Sub
Sh.Name = Right(Sh.Range("B9"), 4)
End Sub
Problem is i still have to toggle it in the formula bar before it changes. I have this formula ='Class List'!B10 in B10 which is feeding from another worksheet called Class List.
View 9 Replies
View Related
Nov 12, 2008
Is there a way to keep the drop-box on a combobox from showing at all? I am trying to build one that has several different values that will just cycle through when clicked.
Here a bit of code I am using so far, just as an example:
Private Sub ComboBox1_DropButt*******()
If Range("F7").Value = "Yes" Then
Me.ComboBox1.Value = "No"
Else
Me.ComboBox1.Value = "Yes"
End If
End Sub
View 9 Replies
View Related
Jul 2, 2006
Attempting to toggle autofilter (if on - then off, if off - then on). Found this here at Ozgrid, apologies lost the thread and author
Sub CheckForAutoFilters2()
With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
.AutoFilterMode = False And .FilterMode = False
Else
.AutoFilterMode = True And .FilterMode = True
End If
End With
End Sub
I added the Else... piece. The code does turn autofilter off - if on. But not on - if off. (Hard to read) ObjectiveAdd drop down arrows to header row if autofilter off. That's it all data to reamin visible until user (me) takes some action.Show all data, remove drop down arrows if autofilter onThanks
View 5 Replies
View Related
Feb 23, 2008
I'm looking for a way to have mutually exclusive toggle buttons (i.e. only one can be selected at a time) embedded in a worksheet. It is possible with radio buttons but they won't serve my needs.
View 5 Replies
View Related
Apr 7, 2014
Is there a way to get a sound toggle button on excel?
What I need is a button to turn the sound alerts on and off on my spreadsheet. Ideally I'd like a button that either changed an image to the speaker image when on and the speaker image with line through it when off. Is that possible?
If not, is it possible to have a button that just says: "Sound:ON" or "Sound:OFF"?
I guess it could be done with tick boxes, or drop down options.
View 4 Replies
View Related
Mar 26, 2014
I have the code below for creating a stopwatch in excel. I was trying to see if there is a way to change this code to assign it to a toggle button so if you hit one part the stopwatch would start and then the other it would stop.
[Code] ....
View 4 Replies
View Related
Feb 11, 2009
I wanted to know how do I keep a toggle button in the on(ture) position after I close the userform? How do I keep the toggle button off(false) positoin after I close the userform? In order words, once I run my macro again, I want it to remain in memory that my toggle button was in the on(true) or off(false)?
For example,I have an application that I am developing which involves some what-if analysis for some projects. There is a command button located on my worksheet. After the command button is clicked, I have include some toggle buttons on a userform for all of the various projects. If I assume that sine projects will not be used, I click the associated toggle buttons. This puts them in the on(true) position. After I close the userform and reclick the command button, my toggle buttons are all back in the off(false) position.
View 5 Replies
View Related
Jul 27, 2009
it's possible to use this macro code in a toggle to perform this action when it's true and when the toggle is click again it can undo it.
This macro does lookups and finds the max on sets of value when it is run and i was wondering if by clicking the button again it can undo what it placed in the cells.
View 4 Replies
View Related
Apr 8, 2013
I have a macro that cycles every 5 minutes. I have tried everything I can to get this program to run while I modify a sheet in the workbook to no avail. I have however figured out that if I open up a separate excel application that my macro can continuously run without preventing me from modifying the other application. Now I need to figure out a way toggle between the two excel application windows if a condition is met. The basis of this is to allow production associates to create a schedule and modify it while a macro looks into the defect database and if a defect becomes too problematic, the macro interrupts the schedule to display the issue.
View 2 Replies
View Related
Aug 3, 2013
This code toggles between a blank cell and an "a" each time the user selects V81.
Code:
If Target.Address = "$V$81" Then
If Target.Value = "" Then
ActiveCell.Value = "a"
Else
ActiveCell.Value = ""
End If
End If
I'd like to expand it so this toggle works not only on V81, but also on V89, V100:105, and V120:124.
View 8 Replies
View Related
Jan 8, 2007
Could someone provide me with the VBA to 'toggle' between HIDING and UNHIDING columns within one macro
e.g. toggle between this hiding and unhiding the following
Sub HideColumns()
Range("G:G,I:I,AB:AV").Activate
Selection.EntireColumn.Hidden = True
End Sub
View 9 Replies
View Related
Dec 9, 2008
I am having a small issue with my toggle buttons.
I have approximately 20 sheets that all have 4 toggle buttons on them. Coded as below (with different ranges for each button). So when I wrote them I took the easy (for me anyway) way and copied all the code for all the buttons in each sheet. (Buttons are named Zoom1, Zoom2 etc.)
Private Sub Zoom1_Click()
If Zoom1.Value = True Then
ActiveWindow.Zoom = 143
Range("A1").Select
ActiveWindow.LargeScroll ToRight:=-10, Down:=-10
Else
ActiveWindow.Zoom = 75
ActiveWindow.LargeScroll ToRight:=-10, Down:=-10
End If
End Sub.........
View 9 Replies
View Related
Mar 9, 2009
This is to inform you that I frequently use toggle grid tool to make my worksheet white/plain,doing this with the help of mouse is eating lot of my time which i really feel anonying/pain.
I would like to have a short cut key for toggle grid which lies in Forms toolbar.
View 9 Replies
View Related
Apr 23, 2009
I am trying to modify the code below to toggle the snap to grid on/off or off/on and is giving me an error.
Sub ToggleSnapToGrid(Optional Dummy As Long)
With Application.CommandBars.FindControl(ID:=549)
If .State = 0 Then
.Execute
Else
.State = 1
End If
End With
End Sub
View 9 Replies
View Related
May 29, 2009
I m sure this should be simple but the excell help in 2007 is infuriating
I have a macro invoked by ctrl t to toggle the worksheet that I am useing. I want it to work out which sheet is open and open the other one if the macro is invoked
here is the code I have got but it goes to the first sheet and then wont come back
Sub Atmos()
If Sheets("Work").Select Then
Sheets("Atmos").Select
End If
GoTo Last
If Sheets("Atmos").Select Then
Sheets("Work").Select
End If
GoTo Last
Last:
End Sub
View 9 Replies
View Related
Jul 2, 2005
I'm having a problem making my font bold when I click on my checkbox and when I uncheck it I want the font to become unbold. I can make it bold when its checked but am not able to make it return to normal when i uncheck it..
View 9 Replies
View Related
Apr 26, 2006
I have several toggle buttons on a spreadsheet for the user to choose which month they want data for. Can anyone tell me how I can make the toggle button that is in the down position change to the up position and not execute the command again when one of the other toggle buttons is pushed?
View 6 Replies
View Related
Mar 16, 2007
I'm trying to create some VBA code that will allow the user with the click of a button to toggle every other line shading on or off.
I have tried the following which does not work
Sub Macro4()
If Range("A3:Q23").Interior.ColorIndex = 15 Then
Range("A3:Q23").Select
Selection.FormatConditions.Delete
Range("A3").Select
Else
Range("A3:Q23").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(1).Interior.ColorIndex = 15
Range("A3").Select
End If
End Sub
View 3 Replies
View Related
May 31, 2007
I am trying to write a macro so that when you click a circle shape it will fill it black and then when you click again it will go back to no fill. Like the option button but is not linked to any other one. So it should just work like a check box but I need it to be a circle.
View 4 Replies
View Related
Jul 2, 2007
This is sheet1
I have assigned the following dates with a name so that conditional formatting can take place..
After i click the "RUN", sheet2 will have the following conditional formatting set
what are the codes for the "RUN" button to let sheet2 do the conditional formatting itself after i have click on the "RUN" button
View 3 Replies
View Related