Solved

Navigating with macros on a existing Workbook

Posted on 2011-09-22
8
278 Views
Last Modified: 2012-05-12
EE Professionals,

EE has helped me develop a neat little App. for scheduling tasks and assigning roles/responsibilities.  I'm having some challenges with "navigation" - When you put in a priority, the app. automatically changes the order/priority. The updating for the chart is automatic when you put in new data in the ajointing cells.  

What I'd like to see is that you can put in all the data on a row and the information is updated either when the priority is set (Col. A) or when the Role is assigned (Col. E).  Also, the cursor needs to move to the next cell rather then reset to a particular cell.

Any improvement would be much appreciated.

Thank you,

B.
RASCI-Sheetv5.xlsm
0
Comment
Question by:Bright01
  • 6
  • 2
8 Comments
 
LVL 5

Expert Comment

by:slycoder
ID: 36582065
I'm working on a fix - but a simple way would be to have a flag for "AutoUpdate" I put one in E2.  This can be tied to a Form Control Checkbox.

Hope this helps.


Copy-of-RASCI-Sheetv5.xlsm
0
 
LVL 5

Expert Comment

by:slycoder
ID: 36582108
Easier than I thought - just wrap your Worksheet_Change code with:

        If ActiveCell.Column = 1 Or ActiveCell.Column = 5 Then
            ...
        End if

Feel free to remove the other wrapper if you do not want the Checkbox:
       If (UCase(Trim(Range("E2").Value)) = "TRUE") Then
        ...
       End if

Thanks

Copy-of-RASCI-Sheetv5.xlsm
0
 
LVL 5

Expert Comment

by:slycoder
ID: 36582694
>> Also, the cursor needs to move to the next cell rather then reset to a particular cell.

This is fixed by adding a "memory" cell regarding the activecell rather than the target

    Dim myCurrentCell As String
    myCurrentCell = ActiveCell.Address


and referencing myCurrentCell in place of:

            'Target.Select

            Range(myCurrentCell).Select

Hope this covers all the bases


Copy-of-RASCI-Sheetv5.xlsm
0
 

Author Comment

by:Bright01
ID: 36582872
slycoder,

This looks great!  Two small things here, is there a way that when you put a priority in a cell in col. A, that it won't flicker and that it will put the cursor into the blank cell that has opened up?  So if I put in 7.5, and it opens up another row, the cursor will be in cell B next to 7.5?

Also, what considerations should I have if I want to move around either the input area or the chart?  I don't need specifics, just a few sentences of caution or do this......

Much thanks, again, looks great.

B.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 5

Expert Comment

by:slycoder
ID: 36583124
I'll stop posting the full spreadsheet (unless requested).

Change the Worksheet_Change function to have the following:

 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim myCurrentCell As String
    myCurrentCell = ActiveCell.Address
    
    If (UCase(Trim(Range("E2").Value)) = "TRUE") Then
    
        If Target.Column = 1 Or Target.Column = 5 Then
        
            Application.ScreenUpdating = False
        
            Dim LastR As Long
            
            With Me
                LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
                If Not Intersect(Me.Range("a12:a" & LastR), Target) Is Nothing Then
                    With Application
                        .EnableEvents = False
                        .ScreenUpdating = False
                    End With
                    With .Sort
                        .SortFields.Clear
                        .SortFields.Add Key:=Range("A12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("B12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("C12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("D12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("E12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                            
                            
                        .SetRange Range("A12:E" & LastR)
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With
                    With Application
                        .EnableEvents = True
                        .ScreenUpdating = True
                        End With
                End If
            End With
            PopulateRasciTable
            'Target.Select
            Range(myCurrentCell).Select
            
            If Target.Column = 1 And ActiveCell.Column = 2 Then
           '     ActiveCell.Offset(-1, 0).Select
            Debug.Print ActiveCell.Value, ActiveCell.Address
                Do While ActiveCell.Value <> ""
                    ActiveCell.Offset(-1, 0).Select
                Loop
            End If
    
            Application.ScreenUpdating = True
            
        End If
    
    End If

End Sub

Open in new window


Glad to hear it's what you need.

Thanks
0
 
LVL 5

Accepted Solution

by:
slycoder earned 500 total points
ID: 36583174
Bug Fix - when you you change the priority - it runs to the top of the list.

use this one instead:


 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim myCurrentCell As String
    Dim myAllowFindEmptyCell As Boolean
    myCurrentCell = ActiveCell.Address
    myAllowFindEmptyCell = False
    
    If (UCase(Trim(Range("E2").Value)) = "TRUE") Then
    
        If Target.Column = 1 Or Target.Column = 5 Then
        
            If Target.Offset(1, 0) = "" Then
                myAllowFindEmptyCell = True
            End If
            
            Application.ScreenUpdating = False
        
            Dim LastR As Long
            
            With Me
                LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
                If Not Intersect(Me.Range("a12:a" & LastR), Target) Is Nothing Then
                    With Application
                        .EnableEvents = False
                        .ScreenUpdating = False
                    End With
                    With .Sort
                        .SortFields.Clear
                        .SortFields.Add Key:=Range("A12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("B12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("C12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("D12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("E12"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                            
                            
                        .SetRange Range("A12:E" & LastR)
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With
                    With Application
                        .EnableEvents = True
                        .ScreenUpdating = True
                        End With
                End If
            End With
            PopulateRasciTable
            'Target.Select
            Range(myCurrentCell).Select
            
            If (Target.Column = 1 And ActiveCell.Column = 2) And myAllowFindEmptyCell = True Then
                Debug.Print ActiveCell.Value, ActiveCell.Address
                Do While ActiveCell.Value <> ""
                    ActiveCell.Offset(-1, 0).Select
                Loop
            End If
    
            Application.ScreenUpdating = True
            
        End If
    
    End If

End Sub

Open in new window


This little bit of code is a "hack" to say - if the new position is in column 2 (from column 1) - then keep going up until there is an empty spot - Only if this was added as a new item.

Thanks.
0
 
LVL 5

Expert Comment

by:slycoder
ID: 36583241
>>> what considerations should I have ...

I'm not sure - basically work with the users that will be doing the data entry and see how they work, what pitfalls they come across and what ideas they have.

I've learned - it's hard to make programs fool-proof because fools are so ingenious!

0
 

Author Closing Comment

by:Bright01
ID: 36583419
Much thanks!  Great coding.

B.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 3 22
How to compare data in excel 5 26
Excel split data and output all combinations. 2 18
TT Auto DashBoard 4 20
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now