kantian1
07-14-2008, 08:35 AM
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
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