Select or msgbox last cell edited by code?

[kantian1]

Hi Simon,

I am stumped with a little problem. I've tried to solve this myself but I am still learning.

When I add an entry I need a msgbox to display 'Entry Added' and I also need the screen to update and goto the last added entry so I can confirm visually that the entry has been added. I can get it to work on sheet2 '2008-2009' but not on the first sheet. Also If I am further down the screen than the entry that has been added, the screen will not goto the last updated cell.

Thanks.

Here's the code you did:




Private Sub CommandButton1_Click()

sheet1.Unprotect Password:="machine"
sheet2.Unprotect Password:="machine"

Dim Sht As Worksheet
Dim rFound As Range, dFound As Range
Dim rRow As Long, dCol As Long
Dim Rng As Range
Dim OriginalSheet As String

OriginalSheet = ActiveSheet.Name

Application.ScreenUpdating = False
For Each Sht In Sheets


If Sht.Name = "Sheet1" Then GoTo Nxt
On Error Resume Next

Set dFound = Cells.Find(What:=Me.DTPicker1.Value, After:=Range("C22"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

On Error GoTo 0
If Not dFound Is Nothing Then
dCol = dFound.Column

Else

MsgBox "Date Not Found on Sheet " & Sht.Name
GoTo Nxt

End If
On Error Resume Next

Set rFound = Range("C" & dFound.Row - 1 & ":C" & Range("C" & Rows.Count).End(xlUp).Row).Find(What:=Me.ComboBox1.Value, _
After:=Range("C" & dFound.Row - 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
On Error GoTo 0
If Not rFound Is Nothing Then
rRow = rFound.Row

Else

MsgBox "Name Not Found On Sheet" & Sht.Name


GoTo Nxt

End If
Cells(rRow, dCol).Value = "RECEIVED"
Cells(rRow, dCol).Interior.ColorIndex = 10

Nxt:

Next Sht

Sheets(OriginalSheet).Activate
Application.ScreenUpdating = True

sheet1.Protect Password:="machine"
sheet2.Protect Password:="machine"

End Sub

[Simon Lloyd]

Post Moved from here (http://www.vbaexpress.com/forum/showthread.php?t=20641&page=2)

[Simon Lloyd]

Place this immediately before Nxt: in the code
msgbox Cells(rRow, dCol).address
Cells(rRow, dCol).select

[kantian1]

Thanks for that Simon, but that adjustment makes the msgbox pop up three times and the screen does not update and goto the cell just modified?

However, if I put the code at the very end before the End Sub but before the sheet1.Protect Password:="machine"
Sheet2.Protect Password:="machine", it all works fine, until I enter a wrong date or name and then it goes to the debugger?

Also How would I change the message box to display for example: "Entry Added in Cell D254"

Thanks.