Cell movement but screen remained

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
382
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi Guys, I have the code to set the movement of cell and it does the job but the view pane not moving to the selected cell pane. Please advise how to make the view pane moves as well.
My example is here after I entered value in cell AL6, it will move to cell H7 but my view pane remains there.


VBA Code:
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)


'Set Cell Movement within The Range
    Dim rngR As Range
    Dim lastRowR As Long, lastColR As Long
    Set rngR = Range("A1").CurrentRegion
    lastRowR = Cells(Rows.Count, "A").End(xlUp).Row
    lastColR = Cells(1, Columns.Count).End(xlToLeft).Column
    
If rngR.Rows.Count > 2 Then
        Set rngR = Intersect(Target, rngR.Offset(1, 0).Resize(rngR.Rows.Count - 1, rngR.Columns.Count))
    Else
        Set rngR = Nothing
End If
If Not rngR Is Nothing Then
            If Target.Column = 1 And Not (IsEmpty(Target)) Then
                  Target.Offset(, 7).Select
            ElseIf Target.Column = lastColR And Target.Row = lastRowR Then
                  Target.Offset(, -30).Select
                'Application.Goto Target.Offset(, -30)
                
            ElseIf Target.Column = lastColR Then
                Target.Offset(1, -30).Select
          
            ElseIf Target.Column = 1 And (IsEmpty(Target)) Then
                Target.Cells.Select
            Else
                 Target.Offset(, 1).Select
            End If
End If

End Sub
 

Attachments

  • cellmove.png
    cellmove.png
    55 KB · Views: 10

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
No, the range from col H to col Al is too wide the view pane cannot display the whole range.
 
Upvote 0
Try changing all the Selects to Activate
 
Upvote 0
Hi Alex Blakenburg, See if this helps

TestDropDownList_2.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1MDateAttendance27-Jan28-Jan29-Jan30-Jan31-Jan1-Feb2-Feb3-Feb4-Feb5-Feb6-Feb7-Feb8-Feb9-Feb10-Feb11-Feb12-Feb13-Feb14-Feb15-Feb16-Feb17-Feb18-Feb19-Feb20-Feb21-Feb22-Feb23-Feb24-Feb25-Feb26-Feb27-Feb28-Feb1-Mar2-Mar3-Mar
2DateSummary(5)(4)(3)(2)(1)12345678910111213141516171819202122232425262728293031
3Larry QT:16 L:0.5 D:2.5 E:0 N:0DD2KGGGGDND1D3DGENNAMNNNDKKDDD2D2D3D3
4Mandy HT:16 L:0.5 D:3.5 E:0 N:0D2DDDDKDKKKEEEEEDD1KENNNALALDDD D EEE
5T:16 L:0 D:1 E:2 N:0dDD3
6T:16 L:1 D:2 E:0 N:1DDGggNALG
7T:16 L:2 D:1 E:0 N:0D2ALGALAL
8T:16 L:0 D:3 E:0 N:2DGNNDDd1
9T:16 L:0 D:1 E:1 N:0EEEED1D1
10T:16 L:0 D:1 E:2 N:1GGGNED1E
11T:16 L:0 D:1 E:0 N:1ND3e
12T:16 L:1 D:3 E:0 N:0D4KGCL
13T:16 L:2 D:1 E:0 N:0DDVLVL
202202
Cells with Data Validation
CellAllowCriteria
D8List=Data!$G$2:$G17
I11List=Data!$G$2:$G17
G9List=Data!$G$2:$G17
C9List=Data!$G$2:$G17
A2List=Data!$P$2:$P14
A3List=Data!$P$2:$P$14
A4List=Data!$P$2:$P$13
A5List=Data!$P$2:$P$12

I need a better screen shot of the problem and possibly a copy of your workbook.
 
Upvote 0
Hi All,
I add Application.ScreenUpdating=True . It works now. Thanks.
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0
Hi All,
I add Application.ScreenUpdating=True . It works now. Thanks.
That is one of the first things I thought of but your code didn't have a line
Application.ScreenUpdating=False

Unless you ran some other code that crashed before running this, you might want to try and find the code that you are running which is turning it off at the start and not turning it back on at the end.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top