[Solved] Problems when using Worksheet_SelectionChange

  • 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

  • Hi Fluppe,


    The easiest way would be to move your code into a module and call it from the event. You can then call the same macro from the userform.



    Hope this helps


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • 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

  • 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 Fluppe,


    Public Sub Worksheet_SelectionChange (Byval Target as Range)
    Application.Run "Book1.xls!UpdateChart" , Target
    End sub


    Should sort the scope problem.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • 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)