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...

    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...



    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,

    Code
    Set sh = Worksheets("Sheet2")


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

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


    with this line...

    Code
    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...

    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: 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...


    Code
    Selection.Names
    ActiveSheet.Names
    ThisWorkbook.Names


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


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

    Re: VBA List Worksheet Names as String


    Something like this...


    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


    Code
    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


    Code
    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


    Code
    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


    Code
    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...



    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.