Posts by CoolBlue

Re: VBA - Charts() .SeriesCollection.NewSeries

Quote from apo;716539

CoolBlue.. how did you get that funky 'tear pattern" around your screenshot?

Yeh, it's nice ha?

I use use a screen capture program called Snagit by TechSmith. It throws the image into it's editor and that's one if the available effects. It's way cool.

When I insert the link in the editor here it actually renders the pic but then, here in the forum it's just a link.
Is there a setting somewhere to render the image links?

Re: VBA - Charts() .SeriesCollection.NewSeries

Here is some more code to show a few different ways to do it in case your interested...

Sub NewSeriesNames3()
'Assumes that:
'1. The data is organised into columns.
'   Each column is three cells high with the following structure:
'       Cell1   Series Name
'       Cell2   XValue
'       Cell3   YValue (Value)
'2. The activecell is the name of the first series


Dim ch As Chart
Dim s As Series
Dim sh As Worksheet
Dim r As Range, lastColumn As Long, sRange As Range
     
     
    Set sh = Worksheets("Sheet2")
     
    Set ch = ActiveSheet.ChartObjects("Chart 1").Chart
       
'   Clear chart
    For Each s In ch.SeriesCollection
        s.Delete
    Next s


'   Some methodes for selecting your table...
'   Method 1
'   Find the column number and then resize based on the active cell
    lastColumn = sh.Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
    Set r = ActiveCell.Resize(3, lastColumn - ActiveCell.Column + 1)
    
'Test Code////////////////////////////////////////////////////////////////////////////////////////////////////////////
    r.Select
    Application.InputBox(Title:="That was method 1", prompt:="select the same cell again and click OK", Type:=8).Select
    Application.Wait (Now() + TimeSerial(0, 0, 1))
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
'   Method 2
'   Use the Range property of the sheet and the resize method to adjust the height
    Set r = sh.Range(ActiveCell, sh.Cells(ActiveCell.Row, Columns.Count).End(xlToLeft)).Resize(3)


'Test Code////////////////////////////////////////////////////////////////////////////////////////////////////////////
    r.Select
    Application.InputBox(Title:="That was method 2", prompt:="select any cell in the table and click OK", Type:=8).Select
    Application.Wait (Now() + TimeSerial(0, 0, 1))
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
'   Method 3
'   If there are not too many gaps in your data, then you can use the CurrentRegion property of the ActiveCell
'   and then use Resize to adjust to exclude the header column and increase to 3 rows
    Set r = ActiveCell.CurrentRegion
    Set r = r.Offset(0, 1).Resize(3, r.Columns.Count - 1)


'Test Code////////////////////////////////////////////////////////////////////////////////////////////////////////////
    r.Select
    MsgBox "That was method 3"
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
     
    For Each sRange In r.Columns
        Set s = ch.SeriesCollection.NewSeries
        With sRange
            s.Name = .Cells(1)
            s.XValues = .Cells(2)
            s.Values = .Cells(3)
        End With
    Next sRange
     
     'ch.Legend.Select
    ch.SetElement (msoElementLegendNone)
     
End Sub

Re: VBA - Charts() .SeriesCollection.NewSeries

OK, good job... I knew you could figure it out.
Based on your code, I assume you're sheet looks something like this: [ATTACH=CONFIG]60988[/ATTACH]

You could also do it like this...

Sub NewSeriesNames2()
'Assumes that:
'1. The data is organised into columns.
'   Each column is three cells high with the following structure:
'       Cell1   Series Name
'       Cell2   XValue
'       Cell3   YValue (Value)
'2. The activecell is the name of the first series


Dim ch As Chart
Dim s As Series
Dim sh As Worksheet
Dim r As Range, lastColumn As Long, sRange As Range
     
' Set up references      
    Set sh = Worksheets("Sheet2")     
    Set ch = ActiveSheet.ChartObjects("Chart 1").Chart
       
'   Clear chart
    For Each s In ch.SeriesCollection
        s.Delete
    Next s


'   set up a local variable to contain the data
    lastColumn = sh.Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
    Set r = ActiveCell.Resize(3, lastColumn - ActiveCell.Column + 1)


'   No need to do this, but the following line is just to show you that the data is selected...
    r.Select
     
'   Use the Columns Object for the Range object and access the Cells in each
    For Each sRange In r.Columns
        Set s = ch.SeriesCollection.NewSeries
        With sRange
            s.Name = .Cells(1)
            s.XValues = .Cells(2)
            s.Values = .Cells(3)
        End With
    Next sRange
     
    ch.SetElement (msoElementLegendNone)
     
End Sub

You just need to select the first series name, as shown in the pic, and then run the macro.

Its nicer and easier to understand if you use "For Each" because VBA can figure out the indexing behind the scenes.

It takes advantage of the Columns Collection of the Range Object and the Cells property of the Column Object.

This way the natural structure of the language takes care of all of the details.

Its also nice to direct the code to the data using the active cell rather than hard code it in.

By the way, after you set a variable to reference Sheet2,

Set sh = Worksheets("Sheet2")

you should just use that variable. So, in your code its better to replace this line...

LastColumn = Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column

with this line...

LastColumn = sh.Cells(1, Columns.Count).End(xlToLeft).Column

I have no clue what that means though...

Quote from Susy;716453

I still wish I could just leave the series name....

Re: VBA - Charts() .SeriesCollection.NewSeries

Not sure why to need a seperate series for each set of points but anyway, the principles you need to solve it are all illustrated in the example code. You should be able to figure out how to adapt it from there.

Re: VBA - Charts() .SeriesCollection.NewSeries

Hi, what does your data look like? How many series are there and where are the series names and the x and y values for each series? what cells are they in?
If it looks like this...
[ATTACH=CONFIG]60963[/ATTACH]
Then style of code will work...

Sub NewSeriesNames1()
Dim ch As Chart
Dim s As Series
Dim sh As Worksheet
     
    
    Set sh = Worksheets("Sheet2")
    
    Set ch = ActiveSheet.ChartObjects("Chart 8").Chart
    
    Set s = ch.SeriesCollection.NewSeries
     
    s.Name = sh.Range("B1")
    s.XValues = sh.Range("A2:A12")
    s.Values = sh.Range("B2:B12")
    
    Set s = ch.SeriesCollection.NewSeries
    
    s.Name = sh.Range("C1")
    s.XValues = sh.Range("A2:A12")
    s.Values = sh.Range("C2:C12")
    ch.Legend.Select
    ch.SetElement (msoElementLegendNone)
        
End Sub

Re: Run a sub in another userform

Check out the explanation here

Quote from royUK

[INDENT]All code in a UserForm is Private.[/INDENT]

That is only true if you are talking about events and the standard userform properties.
You can add you own, public properties and methods to Form Class definitions. If you then instantiate the form using the preferred method discussed in the above link, then it's not a problem to reference them outside the form.
You just need to add a Public wrapper for the Private event or sub-object that you need to refer to.

Re: Error when using spellcheck with Active x text boxes

You might have some other ole objects that are not text boxes and don't have a text property. Use the TypeName function to discriminate.

Re: Start Timer after workbook is open for a certain amount of time.

Hi, there are cleaner ways to do a timer, but you need to be a bit careful. Check out the Application.OnTime method. Also, if you want some code to execute when the workbook opens, use the events in the ThisWorkbook module.

Re: Delete define names in selected cells

Which names do you want to delete, the ones in the ActiveSheet, the ones in the selection, or all names in the workbook? You need to qualify the Names collection like this...

Selection.Names
ActiveSheet.Names
ThisWorkbook.Names

Or, if you want to delete names used in the selected range

Dim c as Range
For each c in Selection
    C.Name.Delete
Next c

Re: Run time error 1004 when locked and protected using a macro on a graph

Yes, protection also affects macros...forum.ozgrid.com/index.php?attachment/58080/

Re: VBA List Worksheet Names as String

Something like this...

Sub WorksheetNames()


    Dim wSht As Worksheet
    Dim r As Range


    Set r = ThisWorkbook.Sheets("Sheet1")
    r.Text = "All Sites: "


    For Each wSht In ThisWorkbook.Sheets
        If wSht.Name Like "Quote - *" Then
             r.Text =r.Text & Mid(wSht.Name, 9, 20) & ", "
        End If
    Next wSht
End Sub

Re: Chart Deactivate Event of Class Firing but not sure why

My solution also works with SpecialCells Badger... you just need to bracket the code with

Application.EnableEvents = False 


Application.EnableEvents = True

Re: Conditional Formatting all numbers two colors

Actually, only the CF in the visible scroll range are evaluated so it doesn't necessarily bog things down if a large area of CF is used.

Re: Simply count the colored cells per row

It needs to be in a Worksheet Module. In VBE you need to click on the actual sheet where want the behaviour and put the code there. It will run when the Worksheet Selection changes. It is an event handler routine... Google it.

Re: Chart Deactivate Event of Class Firing but not sure why

Since this is an interesting problem I decided to investigate further. My investigations confirm my previous post.
I found that, not only is the Chart Deactivated by SpecialCells, but also by Find and by reading a cell's formula.

Like I said, assuming that an object's "Activated" disposition is not affected by other code is a big risk.

Below you can see print-out of the sequence of events.
I had an event handler in the worksheet selection change event and sometimes that fires also when the Chart deactivates, but not always.
It seems that if the result of the operation is Nothing then the Chart Deactivate Event fires but the Worksheet Selection Change does not.

11:09:08: Class1: myChartClass_Activate: R6C1

11:09:08: Module1: TestSpecialCells: Test SpecialCells xlVisible

Set r = ActiveSheet.Range(Cells(1, 1), Cells(22, 26)).SpecialCells(xlVisible)[/COLOR]

RESULT
11:09:08: Class1: myChartClass_Deactivate: R6C1
11:09:08: Sheet1: Worksheet_SelectionChange: R1C1:R5C26,R13C1:R22C26
11:09:08: Class1: myChartClass_Activate: R6C1
11:09:08: Module1: TestSpecialCells: SpecialCells xlVisible Tested

11:09:08: Module1: TestSpecialCells: Test xlCellTypeFormulas

Set r = ActiveSheet.Range(Cells(1, 1), Cells(22, 26)).SpecialCells(xlCellTypeFormulas)

RESULT
11:09:08: Class1: myChartClass_Deactivate: R6C1
11:09:08: Sheet1: Worksheet_SelectionChange: R1C1:R22C2,R1C26:R22C26
11:09:08: Class1: myChartClass_Activate: R6C1
11:09:08: Module1: TestSpecialCells: xlCellTypeFormulas Tested
11:09:08: Module1: TestSpecialCells: R1C1:R22C2,R1C26:R22C26

11:09:08: Module1: TestSpecialCells: Test Find

Set r = ActiveSheet.Range(Cells(1, 1), Cells(22, 26)).Find("Row()", LookIn:=xlFormulas)

RESULT
11:09:08: Class1: myChartClass_Deactivate: R6C1
11:09:08: Class1: myChartClass_Activate: R6C1
11:09:08: Module1: TestSpecialCells: Find Tested
11:09:08: Module1: TestSpecialCells: R2C1

11:09:08: Module1: TestSpecialCells: Test NumberFormat

x = ActiveSheet.Range(Cells(1, 1), Cells(22, 26)).Find("Row()", LookIn:=xlFormulas).NumberFormat

RESULT
11:09:08: Class1: myChartClass_Deactivate: R6C1
11:09:08: Class1: myChartClass_Activate: R6C1
11:09:08: Class1: myChartClass_Deactivate: R6C1
11:09:08: Class1: myChartClass_Activate: R6C1
11:09:08: Module1: TestSpecialCells: NumberFormat Tested
11:09:08: Module1: TestSpecialCells: General

Re: Chart Deactivate Event of Class Firing but not sure why

You should try it Badger, I was surprised as well. I was able to duplicate the behaviour you reported exactly, but as soon as I got rid of the special cells and changed it to a normal, simple range the problem disappeared. Put it back and the behaviour comes back. Every time without exception. Give it a go. Special cells is strange, it generates Worksheet Selection Events as a side effect and that is what deactivates the chart.

And the solution I posted is fully tested: it works. It absolutely fixes your problem. Try it...

Re: Chart Deactivate Event of Class Firing but not sure why

Like I said, I struggle to understand your explanation but anyway,

The problem with your existing code is that it isn't guarding against the possibility of the Chart being deactivated by something that VBA is doing and that's risky.
Turns out the SpecialCells method is indeed causing a Deactivate event on your Chart and maybe this is causing your problem.

The good news is that SpecialCells is generating an Application event so it can be suppressed with the EnableEvents property.
As you probably know, you do need to be a bit careful with EnableEvents, because it persists for the whole session, so I inserted some error handling to ensure it is transparently switched back on.

This will probably fix your problem without having to get dirty...

Public WithEvents myChartClass As Chart 
 
Public iRow As Integer 
 
Private Sub myChartClass_Deactivate() 
    Sheet3.Shapes("Picture111").Visible = False 
    UserForm1.Show 
End Sub 
 
Private Sub myChartClass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long) 
     
    If ElementID <> xlSeries Then 
        Exit Sub 
    End If 
    Dim r As Range 
    Dim x As Integer 
'*********************************************************
    Dim errNum As Long                                 


    On Error GoTo exitEnable


    Application.EnableEvents = False
'*********************************************************
    For Each r In Sheets("Partners").Columns(1).SpecialCells(xlVisible) 
         
        If x = Arg2 Then 
            Sheets("Summary").Range("a25").Value = r.Row 
            Sheet3.Shapes("Picture111").Visible = True 
             
        End If 
        x = x + 1 
        If r.Value = "" Then Exit Sub 
    Next r
'*********************************************************
exitEnable:
    If Err.Number <> 0 Then
        errNum = Err.Number
        Err.Clear
        Application.EnableEvents = True
        Err.Raise errNum
    Else
        Application.EnableEvents = True
    End If
'*********************************************************
End Sub

In terms of scope and life-cycle for the custom class, if it is declared in the UserForm module or in a standard module then it will be OK.
If declared in the UserForm module, it will persist until the form is Unloaded, which in this case is never.
If declared in a standard module, because the "As New" style declaration was used, it will be instanced (or instantiated, depending on which school you went to) every time it is referenced within that module. Since InitializeChart is not declared as Private, it will default to Public and therefore be in scope for UserForm.

Re: Rearranging pasted data of multiple columns to match an adjacent single column

Familiarise yourself with excel's search and replace feature, it's not a formula, it's accessed via the ribbon. the shortcut key is CONTROL-H (hold down the control key and type h.

Then insert a new column to the left of the account names in your old data and copy the account names into it.
Then select the that new column and type CONTROL-H and enter the name you want to replace in the top of the dialog and the thing you want to replace it with in the bottom one.
You will need to do this a few times until all of the names have been fixed.

Then change your vlookup to include the new column that has the same names as your new data and your done.

Re: Rearranging pasted data of multiple columns to match an adjacent single column

The easiest way would be to create a copy of the data and use search and replace (CONTROL-H) to clean the data so that only the desired Account names remain.

If you sure feeling adventurous, there are more technical ways to do it, involving creating one helper column to the left and a series of helper columns to the right (one for each account with variants) and using the Search function.

Re: Rearranging pasted data of multiple columns to match an adjacent single column

Or you can just allow the errors to come through and hide them with conditional formatting. In the conditional formatting you can make a rule using "Format only cells that contain", click on the cell value selector and select Errors. Set the Format so that the Font and the Fill are both white for example.