Help in creating a chart

[leontin]

Hi, guys. I'm pretty new to excel and i'm struggling to create a basic chart like in the image.
Can anyone help me with a video\tutorial or something to create this? I guess it's not complicated to do.
I have searched on the internet buy i couldn't find something similiar. I guess it will be a combination of doughnut with pie chart?

What i want is up to six pointers\lines, eache one of them coresponding to a value in a cell.
Example: cell: A1=25, A2=6 etc and i need pointers in the circle to match the value in the cell with the number in the circle and if no value in the cell then no pointer.

Thank you in advance.

 

[Snakehips]

This begs more questions then.
ATM the code runs when a manual change occurs in A1:A6
Do you need to have it react instantly?
Alternatively, is there some other manual change that can be associated with the change of values in A1:A6?
Or can you eg click a button to run the macro every time you need to refresh?

 

[leontin]

This begs more questions then.
ATM the code runs when a manual change occurs in A1:A6
Do you need to have it react instantly?
Alternatively, is there some other manual change that can be associated with the change of values in A1:A6?
Or can you eg click a button to run the macro every time you need to refresh?

Yes, I need it to react instantly. The A1:A6 cells for the input of the numebrs have formulas in them and the numbers are changing automaticaly.

 

[Snakehips]

Try deleting the previous Change Event code and Paste the below into the Workbook code module

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

With Sheets("Sheet3")  '<<< EDit to suit****

.ChartObjects("Chart 4").Chart.Rotation = Range("I10").Value
.ChartObjects("Chart 4").Visible = Not (Range("I13").Value)
End With
End Sub

Edit the sheet name as required.

Hope that helps.

 

[leontin]

Try deleting the previous Change Event code and Paste the below into the Workbook code module

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

With Sheets("Sheet3")  '<<< EDit to suit****

.ChartObjects("Chart 4").Chart.Rotation = Range("I10").Value
.ChartObjects("Chart 4").Visible = Not (Range("I13").Value)
End With
End Sub

Edit the sheet name as required.

Hope that helps.

Yes, That code fixed it Great man! I had to modify also the function in "Hide" section from COUNTA to just COUNT because it was showing FALSE even though the cells were empty.
Now it's working perfectly
I have one more question about VBA if you don't mind. I have noticed that if I insert a new column before another, for example, B becomes C, the ranges are changing accordingly on the spreadsheet, but in the VBA code they are not and the code stops working...I have to manually change the range in the VBA code from B to C as for this example. Is there a workaround for this?

 

[Snakehips]

Difficult to say without more knowledge of what you have and what you want. Vba coded to to refer to current column B will always do so regardless of how many columns you add or delete.
Is there some data detail / feature, eg a specific column header, to the right of the insertion point that will always enable your vba to determine a relative number of columns ??????

 

[leontin]

Difficult to say without more knowledge of what you have and what you want. Vba coded to to refer to current column B will always do so regardless of how many columns you add or delete.
Is there some data detail / feature, eg a specific column header, to the right of the insertion point that will always enable your vba to determine a relative number of columns ??????

Ok, let me be more specific. Let's take for example the vba code you gave me.

.ChartObjects("Chart 4").Chart.Rotation = Range("I10").Value
.ChartObjects("Chart 4").Visible = Not (Range("I13").Value)

If I insert a new column before the "I" column on the spreadsheet "I" becomes "J" and i have to go to VBA section and manually change the range to "J10" and "J13" for the code to work again.
On the spreadsheet all the formulas are changing automatically so "I" becomes "J" accordingly, but in the VBA code stays the same... Even if I CUT and PASTE the entire "I" column somewhere else makes no difference to VBA Code. Do you understand now what I'm trying to say?

 

[Snakehips]

Yes, I do understand. Native Excel will react to insertions and deletions and adjust formulas accordingly. VBA does not. Vba does what you code it to do.
I was under the impression that you are regularly needing to insert columns or whatever and thus having to keep amending vba. Is this the case or are you just having to insert in order to set things up properly? If you do have a dynamic situation then any code will need to be similarly dynamic.

 

[Snakehips]

Did you manage to resolve this?

 

[leontin]

Did you manage to resolve this?

Hi. No. I do it manually. If I insert new column in my sheet i go to VBA code and manually change the range accordingly. It's ok, just a couple of ranges, not to much trouble.

 

[Snakehips]

Ok, I don't know what aspect of your code this relates to but maybe try this approach.
Can you define the the ranges in question as Named ranges? That way, Excel will keep track of them as and when you insert or delete.

Then within the vba code, refer to the ranges by Name rather than reference.
Eg Named range in Excel for I10 is "Rotation"
Refer to it in vbs as Range("Rotation") rather than Range("I10") and it will hold good if insertion causes I10 to become J10