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

Re: 40NZD Make chart hold it's set format and not go back to automatic setting

Thanks GC!

Re: 40NZD Make chart hold it's set format and not go back to automatic setting

Thanks KjBox, have sent you an email.

Re: 40NZD Make chart hold it's set format and not go back to automatic setting

Hi guys

Would anyone else be interested in having a look?

Re: 40NZD Make chart hold it's set format and not go back to automatic setting

Any ideas on what might be causing the problem SMC?

Re: 40NZD Make chart hold it's set format and not go back to automatic setting

Thanks SMC, have emailed you the file.

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,

SChalaev

*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: https://www.reddit.com/r/excel…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

Thanks again

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.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
WorksheetChanged Target
End Sub




Private Sub WorksheetChanged(ByVal Target As Range)




Dim PrevCell As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim Rng5 As Range
Dim Rng6 As Range




Set PrevCell = ActiveCell
Set Rng1 = Range("C3")
Set Rng2 = Range("C4")
Set Rng3 = Range("C5")
Set Rng4 = Range("C6")
Set Rng5 = Range("C7")
Set Rng6 = Range("C8")




If Not Intersect(Target, Range("C3")) Is Nothing Then
If Rng1 > 1 Then
With Range("E3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng1
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
Rng1.Select
End With
End If
End If




If Not Intersect(Target, Range("C4")) Is Nothing Then
If Rng2 > 1 Then
With Range("E4").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng2
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
Rng2.Select
End With
End If
End If


..... etc


..... etc


..... etc




If Not Intersect(Target, Range("C8")) Is Nothing Then
If Rng6 > 1 Then
With Range("E8").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng6
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
Rng6.Select
End With
End If
End If




Application.Goto PrevCell




End Sub

Re: Dependent Data Validation with VBA

This is a link to the test file if that helps:
https://www.dropbox.com/s/0z6t…lidation%20Test.xlsm?dl=0

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.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Range("C3")
If Not Intersect(Target, Range("C3")) Is Nothing Then
With Range("E3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Rng.Select
End If
End Sub

So far it works only in E3 depending on the choice in only C3......but if I have data validations going down to, say, C10....how 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.

Re: Y-axis Labels - Dynamic Chart

Is this what you're after?

Then you can create a relationship (via formula) between Grade and Results and the labels will be dynamic as well.

Edit: my bad OP, I see what you mean now.

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,

Stas

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

Solution has been found from ExcelHelper84 from reddit.

Quote

I think the problem might be that you're using the LOOKUP function, which finds an exact answer that you are then trying to match in the list. To correct it, remove the LOOKUP function and leave the MATCH function by itself since the MATCH function finds the relative position in the list. This will take care of duplicates since a position cannot be a duplicate. Type this formula into C1 and instead of hitting Enter, hit Ctrl+Shift+Enter (Ctrl, Shift, Enter):=IF(MATCH(9.99E+307,$B$1:$B$12)=A1,"Hello","")
That will work fine, but you would still need to keep the numbers in the first column to match against.
The formula I prefer is this:
=IF(MATCH(9.99E+307,$B$1:$B$12)=ROWS($C$1:C1),"Hello","")
Instead of trying to match the relative position to the numbers in the first column, it uses an expanding range to count the row number you are in. Try this formula and it will work even if you delete all of the numbers in column A.
By the way, the formulas above give you the last number in the list. If you want the last word in a list, use this. It works in a list with words only and it also works in a list with words and numbers:
=IF(MATCH(REPT("z",255),$B$1:$B$12)=ROWS($C$1:C1),"Hello","
If your list contains mixed data and you want the last non-blank (i.e,, you want whatever the last value is regardless of whether it is a word or number), use this:
=IF(MATCH(2,1/($B$1:$B$12<>""))=ROWS($C$1:C1),"Hello","")
Remember to use Ctrl+Shift+Enter for all of these.

Display More