Posts by SChalaev

    Hi guys,
    I've got the following piece of code:

    If iSeriesNumber = 4 Then
    .Format.Line.Visible = False
    .MarkerStyle = xlMarkerStyleTriangle
    .MarkerSize = 8
    .MarkerForegroundColor = RGB(255, 255, 255)
    End If

    How do I set marker's border line width to 0.25 pt while still hiding the series line? At the moment border line comes up as 1pt (default?).

    Many thanks

    Hi guys,

    In a workbook there's a chart which I've finished formatting that is plotted from a mirror data table below it.

    In the main data table I've used checkboxes to hide/unhide Chart series. For example, if I click on the checkbox in the main data table then a short macro (that is assigned to that specific checkbox) runs to hide/unhide a row (a series) in the mirror table.

    This causes the chart to be a bit more dynamic such that it hides/unhides data in the chart, unfortunately the chart doesn't keep it's sets format for the data that's plotted and reverts back to 'automatic format' in some cases, which is a pain. So I want the chart to be able to keep it's set formatting.

    I've tried to open the workbook in Excel 2010 and it seems to be working fine, Excel 2013 is another story.

    It could be just a bug of some sort in Excel 2013 so if it can't be resolved, I'd appreciate it if you could advise another possible solution to this problem.

    I will email the file if there's interest.

    Many thanks,


    *Payed 10% via PP

    Re: Chart to keep its set formatting and not change back to 'automatic format'.

    Thanks for the suggestion pike.

    I have finally managed to figure it out... When I opened up 'Select Data' then went to 'Hidden and Empty Cells' - I've had 'Show data in hidden rows and columns' unchecked.

    So when I would hide a series (a row with series data in it) with a macro, Excel would lose it's set formatting and 'cycle' through automatic or formatting for other graphs.

    When I checked the option to show data in hidden rows and columns I was still able to hide the series but keep the formatting. :cheers:

    Hi guys

    I've got a data table and a graph is plotted from it. Using checkboxes (form control, not active X) I can temporarily remove the chart's series via VB code (e.g. 'if checkbox is true, then hide this row' type of thing) as this makes the chart's legend dynamic. The graph is your typical xy graph (two y-axis and one x-axis).

    As far as functionality everything is working as it should but the problem lies in line/marker formatting on the graph. If I hide/unhide rows (and thus series) the graph format is lost - it goes back to automatic (changes to a different colour or adopts new markers), starts missing markers or line.

    Not sure what's going on here, since I have done the same project before and everything works fine. Any ideas?

    Many thanks.

    Also posted here:…o_automatic_format_after/

    Re: Removing Data Labels with zero value

    My first thought was to use something like [White][=0]0.00; for data label formatting but looking at your chart and colours that are used, it's not an option.

    Have you thought about using IF statements and NA() in your data table to eliminate series so they don't get plotted on the graph?

    Re: Dependent Data Validation with VBA

    Thank you Stephen, that works.

    I've used the following because it helped bypass error in case Range("C3:C8") got emptied on the sheet by highlighting that range and selecting Clear Content.

    If RngX > 1 Then
    End If

    How can I adapt it or similar method to your code?

    Re: Dependent Data Validation with VBA

    Managed to extend first and second data validations by bloating up the WorksheetChange sub. Any way or ideas to shorten the code because I probably have >100 cells to validate?

    Many thanks.

    Hi guys,
    I'm trying to set up two dependent data validation in a worksheet. Based on the selection in the first data validation cell, the second cell data validation list is adjusted.
    I've had a look at INDIRECT () function in other tutorials but my data is not set up to allow the use of that function so I'm turning to VBA as an alternative.

    In a simple Test sheet:

    First data validation cell has a dynamic named range in C3: =OFFSET(Data!$B$4,,,COUNTA(Data!$B$4:$B$30))
    Second data validation cell is created by this code in VBA in E3 (that I have found in another tutorial) that basically pulls the corresponding dynamic named range based on selection in C3.

    So far it works only in E3 depending on the choice in only C3......but if I have data validations going down to, say, can I populate the second data validation (by changing range in VBA??) to, for example, E10?
    Many thanks

    Re: Y-axis Labels - Dynamic Chart

    I clicked on each individual data label on your graph and typed "=" (without ""), then clicked on the corresponding grade in the column G. The end result would look something like this =Sheet1!$G$4.

    Depending on the amount of graphs/data points you have, there may be a solution already available through VBA to automatically do this with a macro.

    Re: Formatting of custom made Data Labels for a line chart?

    Hi Pike,

    Many thanks.

    Can I ask what Excel you're using?

    I tried doing that in 2013 but for some reason it highlights the whole data point label (e.g. Toyota AND Levin) and won't let me differentiate between highlighting different words. However, in 2010 I managed to do what you've pointed out.

    Hi guys,

    I've created a dynamic line chart and have added custom data labels to it.

    Is there a way (I'm guessing this solution will have to have VBA in it) to change the format of custom labels to make the second word in the label a different colour?

    For example, Toyota can stay the same colour while Levin is black, Camry is red and Yaris is green.

    PS. I can't change the colour of the lines in the chart, they must stay the same.

    Many thanks,


    Re: Finding the last duplicate in a column and returning a text string.

    Solution has been found from ExcelHelper84 from reddit.