Posts by Fluppe

Hi Andy,
The code Application.Run "Overzicht celbody bldg 16.xls!UpdateChart", Target
gives an error : the macro cannot be found.
While in the workbook "Overzicht celbody bldg 16.xls" I placed in module1 the code Public Sub UpdateChart(Target As Range)

I deleted the code
Public Sub Worksheet_SelectionChange (Byval Target as Range)
UpdateChart Target
End sub
and it seems to work perfectly?
I thought excel needed the code to check if there are any changes made to the worksheet. It seems that it isn't necessary
Maybe it's because I didn't sleep well last night or I just don't now VBA.

Can someone explaine it to me?:(

Hi,
I think I have to change something because I get a compile error.
Sub or Function not defined.
I’m using 2 workbooks: in workbook1 I placed the userform and the module with the code Public Sub UpdateChart (Target as Range) and another module where I placed the UpdateChart Range (“metingen1”).Cells(1,1) and this module is called after pressing the OK button on the userfrom.
In workbook2 I placed the code
Public Sub Worksheet_SelectionChange (Byval Target as Range)
UpdateChart Target
End sub
This gives the problem because the UpdateChart is not recognized in workbook2.
I have to use those 2 workbooks.
What can I do?

Thanx a lot,
Fluppe

Hi,
I have a workbook with data on sheet1 and 3 charts on sheet2.
3 Charts because there is too much data for 1 chart.
The Y-scale values of the charts are automatically set.
This works by entering the following code in sheet1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim intIndex As Integer
Dim r As Range

For intIndex = 1 To 3
Set r = Range("metingen" & CStr(intIndex))
If Not Intersect(r, Target) Is Nothing Then
With Worksheets("Grafiek").ChartObjects("Chart" & CStr(intIndex)).Chart.Axes(xlValue)
.MinimumScale = Int(Application.Min(r)) - 0.5 'Int returns integer portion of number.
.MaximumScale = Int(Application.Max(r)) + 0.5
End With
Exit For
End If
Next

End Sub

Here is the problem:
A user enters data by using a userform.
The chart will be adjusted when excel detect any changes on the worksheet.
But because of entering data by a userform it doesn’t detect any changes. I have to select the changed cells my self before the chart will adjust.
How can I solve the problem so that excel detects changes after pressing OK on the userform?

Thanx,
Fluppe

Andy,
this is working faster!:))

thanx,
Fluppe

Hi Derk,
Maybe I'm doing it wrong:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False

code

Application.EnableEvents = True
end sub
This is not speeding up the process.
Did I do something wrong?

My worksheet "grafiek" is protected.
The macro can only work when the protection is disabled.

Stupid me!

Hi Andy,
I only need the macro when I want to show the sheet where the charts are stored.

What do you mean with "filling the range in one go"?

thanx,
Fluppe

Hi,
When I open my workbook and select a cell within a range ie "metingen1" the following error occurs: 'Runtime error 1004' unable to set the minimumscale property of the axis class.
I have 4 ranges on sheet1 and 4 charts (chart1 to chart4 using range "metingen1" to "metingen4") on sheet2
I'm using the following code to adjust the scale factor of the y-axis:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim intIndex As Integer
Dim r As Range

For intIndex = 1 To 4
Set r = Range("metingen" & CStr(intIndex))
If Not Intersect(r, Target) Is Nothing Then
With Worksheets("Grafiek").ChartObjects("chart" & CStr(intIndex)).Chart.Axes(xlValue)
.MinimumScale = Int(Application.Min(r)) - 0.5 'Int returns integer portion of number.
.MaximumScale = Int(Application.Max(r)) + 0.5
End With
Exit For
End If
Next

End Sub

I think it is strange because it worked yesterday and know I get those problems??

Any help welcome
Fluppe

Hi,
The user can enter data in the range by using a userform.
But now, when I use code for adjusting the y-axis the process of transporting the values in the textboxes to the cells is going much slower.
Is there something we can do about it?
thanx,
Filip

Hi Andy,
the following code was the problem in my sheet:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
This must be:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) like you used.

Thanx,
Fluppe

Hi Andy,
I tried the code you posted here but it doesn't seem to work.
I defined a second range ie "metingen2" in sheet 1 for my second chart in sheet2.
Only the first chart is working ("metingen1"). I don't know what went wrong.

I will try to name the chartobjects themselves.

thanx

Hi Andy,
I'm using the code that Derk and you gave me:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim r As Range
Set r = Range("metingen1")

If Intersect(r, Target) Is Nothing Then Exit Sub
With Worksheets("grafiek").ChartObjects(1).Chart.Axes(xlValue)
.MinimumScale = Int(Application.Min(r)) - 0.5 'Int returns integer portion of number.
.MaximumScale = Int(Application.Max(r)) + 0.5
End With

End Sub

I only tried to Set another range
ie Set s as range. But that is not the way it works.

Hi,
I don't know what I have to change in the code to make it usefull for more then 1 chart in sheet2?
I'm using 4 ranges. Each range has a chart. The ranges are in sheet1.
Is someone willing to help me?

Thanx,
Fluppe

Thanx Andy,
I didn't know it would be that easy!
I love excel vbe

thanx,
Fluppe :tumble:

Hi,
This chart works great while the range and the chart are on the same sheet.
What do I have to change in the code when the range is on sheet1 and the chart on sheet2?
Is this still possible?

Thanx,
Fluppe:wink1:

Derk I really can start with this!
That is what I was looking for.
Tanx alot. :wink1:

Hi Derk,
The user is entering data in columns. There are ie 3 colums with data, each column is a serie (y-value).
The x-value contains the datums when they entered the data.
The values are between ie 10 and 60. When the max value is ie 20 and the min value is 10 I want the max value displayed on the chart 1 more than the max value (=21) and the min value 1 less than the min value (=9). Is it possible that excel does it automatically?
If not can you give me a demo how you would do it, or do you need more info?

Thank you very much,
Fluppe

Hi,
Is it possible to automaticaly set the Y-value scale of a chart?
A user has to enter values but I don't know what the min and the max of the value is. It is possible to set the min and the max value of the Y-axis to auto in the format axis. But when I set the min value to auto excel always use 0 as the min value. This makes the chart unreadable because the Y-axis becomes to big.
Is it possible to set the min. value to ie 1 less than the min value the user entered?

Thanx,
Fluppe

Hi again,
I think I found another problem.
It doesn't seem to work when the formula links to a cell in another workbook =if('[WorkbookA1.xls]sheet1'!A1="","",'[WorkbookA1.xls]sheet1'!A1) what can I do about this?