Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Chart Enhancement with Macro Change

Posted on 2011-09-22
5
Medium Priority
?
351 Views
Last Modified: 2012-05-12
EE Professionals,

I have a Chart that EE helped me create that needs an enhancement.  Presently if you add a Task and assign it a number in the Critical Path, it will adjust the data but not the chart.  It's also difficult to add tasks because the data is in line with the chart for part of it.  

Can the "Update" macro be made automatic?  So that when there is a change to the data and the "Enter" key is pressed, the table is automatically updated?

B.
RASCI-Sheetv3.xlsm
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50
ID: 36579793
Hello Bright,

You can combine the Worksheet_Change event and the macro that updates the RACI table.

Right-click on the sheet tab, click View Code to open the VB Editor
Double-click "Sheet1 (RASCI)" in the Navigation pane of the VBE
Scroll down to the end of the Worksheet_Change procedure and before the "End Sub" line enter

    PopulateRasciTable

The last four lines of the code should now read

        End If
    End With
    PopulateRasciTable
End Sub

Open in new window


Now, whenever you add a row of data to the text table, as soon as you enter a number in column A, the row will be sorted into the data table, and an entry for that row value will be created in the RASCI chart. Whenever you edit the number, description or RASCI letter for a row, it will be reflected in the RASCI table.

cheers, teylyn
0
 

Author Comment

by:Bright01
ID: 36579986
Teylyn,

Thank you for  the quick response.  It works great!  Is there a way where the cursor won't revert back to the top?   Thank you,

B.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36580083
In the Worksheet_Change event of the RASCI sheet, add this line before End Sub


    Target.Select

The last few lines of the code should read:

            With Application
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    End With
    PopulateRasciTable
    Target.Select
End Sub

Open in new window


The cursor will stay on the same row as it was in when the new data was entered.

Due to the macro that sorts the table by the number in column A upon each change, I don't see an easy way to move the active cell to the row where the data lives after the table has been sorted.

To reduce the screen flickering, you could add

Application.ScreenUpdating = False

at the start of the procedure ...

Private Sub Worksheet_Change(ByVal Target As Range)
    
Application.ScreenUpdating = False

    Dim LastR As Long

Open in new window


... and

    Application.ScreenUpdating = True

before the sub ends

            With Application
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    End With
    PopulateRasciTable
    Target.Select
    Application.ScreenUpdating = True
End Sub

Open in new window


0
 

Author Closing Comment

by:Bright01
ID: 36580174
Teylyn!  Great job.  Thank you very much.  I will most likely be asking a related question on this as this function continues to be built out. And I thought you said you were a formulas person!

Cheers,

B.
0
 
LVL 50
ID: 36580246
Yeah, well, stretching and all :-)

Thanks for the grade.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question