Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,616
- Office Version
- 365
- 2016
- Platform
- Windows
I have code that allows a user to enter a value into cell A6. The code then checks that value to those values currently in column A (excluding A6) to see if that value already exists. If it does, a prompt allows the user to view the row of data of the original value. How can I go about scrolling that roll up to row 6? (Row 6 is the default data entry row, and if a duplicate is found, it is deleted).
Here is my code in which I tried using activewindow scrollcolumn and scrollrow functions. I don't think they are working though as I had expected. Probably because I don't know how to use them.
Here is my code in which I tried using activewindow scrollcolumn and scrollrow functions. I don't think they are working though as I had expected. Probably because I don't know how to use them.
Code:
Sub Worksheet_Change(ByVal Target As Range)
Dim cval As String, aval As String
Dim bval As String
Dim msg1 As String, msg2 As String, msg3 As String
Dim acnt As Long
Dim lrow As Long
Dim ui1 As Variant
If Not Application.Intersect(Columns(1), Range(Target.Address)) Is Nothing Then
Stop
If IsEmpty(Target) Then
Exit Sub
Else
'aval = "R" & Target.Value
aval = Target.Value
lrow = ws_pdata.Cells(ws_pdata.Rows.Count, "A").End(xlUp).Row
acnt = Application.WorksheetFunction.CountIf(ws_pdata.Columns(1), CLng(aval))
If acnt > 1 Then
acnt = Application.WorksheetFunction.Match(CLng(aval), ws_pdata.Range("A7:A" & lrow), 0) + 6
ui1 = MsgBox("Permit already exists in database at row " & acnt & "." & Chr(13) & "View exisiting entry?", vbYesNo, "Permit Entry Error")
If ui1 = vbYes Then
ws_pdata.Unprotect
Application.EnableEvents = False
ws_pdata.Rows(6).EntireRow.Delete
Application.EnableEvents = True
MsgBox "Scrolling to row " & acnt
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = acnt - (acnt - 6)
ws_pdata.Protect
Exit Sub
Else
Exit Sub
End If
End If
End If