Bright01
asked on
Navigating with macros on a existing Workbook
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
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
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").Va lue)) = "TRUE") Then
...
End if
Thanks
Copy-of-RASCI-Sheetv5.xlsm
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").Va
...
End if
Thanks
Copy-of-RASCI-Sheetv5.xlsm
>> 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).Selec t
Hope this covers all the bases
Copy-of-RASCI-Sheetv5.xlsm
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).Selec
Hope this covers all the bases
Copy-of-RASCI-Sheetv5.xlsm
ASKER
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.
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.
I'll stop posting the full spreadsheet (unless requested).
Change the Worksheet_Change function to have the following:
Glad to hear it's what you need.
Thanks
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
Glad to hear it's what you need.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>> 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!
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!
ASKER
Much thanks! Great coding.
B.
B.
Hope this helps.
Copy-of-RASCI-Sheetv5.xlsm