Solved: ActiveCell is current/horrible solution... need fix

[thomaspatton]

ElseIf Range("i4").Value = "x" Or Range("i4").Value = "X" Then
Dim AdminDropDate As String
AdminDropDate = InputBox("Enter the date that Student was Admin Dropped.", "Enter Admin Drop Date")
Range("j4:u4").Select
Selection.MERGE
With Selection.Interior
.ColorIndex = 3
End With
Selection.Font.ColorIndex = 6
ActiveCell.Value = "Admin Drop " & AdminDropDate


Got some help yesterday, which solved the issue here (http://vbaexpress.com/forum/showthread.php?t=17383), but then I ran into a different one. Basically, as you can see from the snippet above, everytime I would update a different row, it would change only row4.

So I rewrote mine as such :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
Call MergePJtoGrad
End If
End Sub

Sub MergePJtoGrad()
Worksheets("Scores").Activate
c = 1
Do Until c = 2
With Me
If ActiveCell.Value = "" Then
With .Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
c = c + 1
ElseIf ActiveCell.Value = "x" Or ActiveCell.Value = "X" Then
Dim AdminDropDate As String
AdminDropDate = InputBox("Enter the date that Student was Admin Dropped.", "Enter Admin Drop Date")
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
.Cells(1, 1).Value = "Admin Drop " & AdminDropDate
End With
c = c + 1
ElseIf ActiveCell.Value < 70 And ActiveCell.Value < 70 Then
Dim AcadDropDate As String
AcadDropDate = InputBox("Enter the date that Student was Acad dropped.", "Enter Acad Drop Date")
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
.Cells(1, 1).Value = "Acad Drop " & AcadDropDate
End With
c = c + 1
Else
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
c = c + 1
End If
End With
Loop
End Sub


I'm sure you advanced users can see the issue with that, but I can't seem to find a fix for the ActiveCell references.

First off, the active cell becomes the one you hit enter to, which means it's not calculating the scores correctly. Second, even IF it was hitting the correct activecell first, you HAVE to have Excel setup to jump-down upon data entry, or it will grab the wrong cells/data/and on and on.

What do I use to correct this ActiveCell junk?

[Bob Phillips]

If I understand the code then



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If .Column = 9 Then
If .Value = "" Then
With .Offset(0, 1)
.Value = ""
With .Resize(, 12)
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
End With
ElseIf UCase(.Value) = "X" Then
Dim AdminDropDate As String
AdminDropDate = InputBox("Enter the date that Student was Admin Dropped.", "Enter Admin Drop Date")
With .Offset(0, 1)
.Value = "Admin Drop " & AdminDropDate
With .Resize(, 12)
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
End With
End With
ElseIf .Value < 70 And .Value < 70 Then
Dim AcadDropDate As String
AcadDropDate = InputBox("Enter the date that Student was Acad dropped.", "Enter Acad Drop Date")
With .Offset(0, 1)
.Value = "Acad Drop " & AcadDropDate
With .Resize(, 12)
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
End With
End With
Else
.Value = ""
With .Offset(0, 1)
.Value = ""
With .Resize(, 12)
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
End With
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

[thomaspatton]

Excellent. Thanks again for the education.

I had to remove the .Value="" of the final Else statement since it kept erasing the score I entered, but otherwise it's working as intended.

Really appreciate it once again xld.

[thomaspatton]

Also, let me see if I get what you did.

First off you changed the procedure into an always active type event instead of calling it everytime column 9 is changed. Then, instead of referring to a change in the active cell, you set it to refer to changes made to cells only in column 9.

What I don't get is how you refer it to the appropriate row...

Is that what "With Target" does? Instead of referring to a particular cell it's looking for only the qualifying cells in column 9?

[Bob Phillips]

When you make a change to a worksheet in Excel, and you have a Worksheet_Change procedure active on that sheet, Excel/VBA passes the cell in question to the procedure as an argument, the one that the default procedure signature calls Target. So you have a cell object passed, the cell(s) change, so you can get any information about that changed cell, its row, its column, its (changed) value, and so on.

Sorry about the extra .Value, the one I didn't test. BTW I notice that in the 3rd nest IF test, both conditions are for <70 - a tad redundant.

[thomaspatton]

Great info. Definately will be used in the future.


BTW I notice that in the 3rd nest IF test, both conditions are for <70 - a tad redundant.

Yeah, that was from back when I was referring to both column 8 and 9. No longer needed.