Sheet Not Fully Active after Selecting or Activating

[DaveGib]

Hi All,
Can someone please help me with a strange problem i am encountering?

I have 2 option buttons the user can select, and depending on which one is selected will Unhide and unlock a relative sheet, then hide the original sheet.

The problems I am having are :
1.) The sheet that is revealed is displayed okay, and the previous sheet is hidden, but if any data is entered the information is recorded on the original sheet.
2.) If i try and scroll down the sheet using the Mouse wheel, the scroll bar on the side of the screen moves down (or up), but the actual screen remains unchanged.
3.) If I click on any other tab, and without doing anything else, but return to the tab in question - everything works fine.

I have tried referring to the sheet in four different ways - see code below, but nothing works!!

Thanks in advance for any assistance with this baffling problem.

Private Sub CBProceed_Click()
    Dim pwd As String
    
    If OptionButton1 = False And OptionButton2 = False Then
        MsgBox "Please Select One!!.."
        Exit Sub
    End If
    
    If OptionButton1 = True Then
            Sheet8.Visible = xlSheetVisible
            Sheet1.Visible = xlSheetHidden
            Sheet8.Unprotect pwd = "Dave"
            Sheet8.Select
'            Sheet8.Activate
'            Sheets("Prepour_Issue_Sheet").Select
'            Sheets("Prepour_Issue_Sheet").Activate
            
            Range("A11:G16").ClearContents
            
        
    Else: OptionButton2 = True
            Sheet9.Visible = xlSheetVisible
            Sheet1.Visible = xlSheetHidden
            Sheet9.Unprotect pwd = "Dave"
            Sheet9.Select
            
            Range("A11:A20").ClearContents
            Range("H11:H20").ClearContents
    End If
    
    Range("F4").Select
    
    Unload Me
End Sub

[SamT]

Try using

Sheet8|9.Activate 'vs .Select

Also specify Range Parents

Sheet8.Range("A11:G16").ClearContents

OR

With Sheet9
        .Range("A11:A20").ClearContents 
        .Range("H11:H20").ClearContents 
End With

[DaveGib]

Hi SamT,
Thanks very much for your suggestions.
I did try .Activate, - as well as referring to the sheet name, using .Select and .Activate, but they all gave the same results.
Everything else is working - i.e. ranges are cleared and the active cell is correct, - i can click in any cell and enter data, but when i press enter, the data 'disappears'...... to be found on the sheet that has just been left ( i.e. the 'Home' page).
I have used this technique dozens of times and not had a problem, but what I have just realised since posting the problem is that I have a worksheet change event on these two sheets that I was helped with on the forum about a month ago, and what I have noticed is that if I right click on the so called 'active', or displayed sheet, and select View Code, - the 'Home' page code screen appears in the editor and not the Sheet 8 code.
however, if i click on any other tab and go back again so that the tab is 'properly' active and I rt Click the tab, the code is correctly displayed on the right sheet.(8)
I.E. if I click off the tab, and go back everything works as it should - the code above, and the code below, - it's just when the tab first opens that there is a problem.
The code for the worksheet change event on sheet 8 is as below, ....... maybe you can spot something there that will cause the problem?
Thanks so much for your time!! Greatly appreciated!!
Dave

Private Sub Worksheet_Change(ByVal Target As Range)
     '  ** Check if Cell J6 is updated
    Dim r As Range
    Dim ar As Integer
    If Not Intersect(Target, Range("J6")) Is Nothing Then   'There is a drop down with a list of possible defined named ranges to choose from in J6
        
        Set r = Sheet4.Range(Target)
        Sheet8.Range("A11").Resize(r.Rows.Count, r.Columns.Count).Value = r.Value
        ar = Range("B21").End(xlUp).row - 9
        If ar > 1 Then
            Range("H11").Resize(ar - 1).FormulaR1C1 = "=SUMIFS(Table2[Issue Qty],Table2[Co,de],'Prepour_Issue_Sheet'!RC[-7],Table2[House No],'Prepour_Issue_Sheet'!R4C6)"
        End If
        
    End If
    
    Range("F4").Select
 
End Sub

[DaveGib]

Sorry,!!!! ........ I don't know why the code was split above, - I tried editing, but couldn't change it ...... my apologies!!!!!!

[DaveGib]

Should be........

[CODE]
Private Sub Worksheet_Change(ByVal Target As Range)
' ** Check if Cell J6 is updated
Dim r As Range
Dim ar As Integer
If Not Intersect(Target, Range("J6")) Is Nothing Then 'There is a drop down with a list of possible defined named ranges to choose from in J6

Set r = Sheet4.Range(Target)
Sheet8.Range("A11").Resize(r.Rows.Count, r.Columns.Count).Value = r.Value
ar = Range("B21").End(xlUp).row - 9
If ar > 1 Then
Range("H11").Resize(ar - 1).FormulaR1C1 = "=SUMIFS(Table2[Issue Qty],Table2

,'Prepour_Issue_Sheet'!RC[-7],Table2[House No],'Prepour_Issue_Sheet'!R4C6)"
        End If
        
    End If
    
    Range("F2").Select
 
End Sub

[DaveGib]

Sorrrrrryyyy again!!!... I am really making a hash of this. - I see the problem is that in my formula in the code I am referring to a Table range called 'Code' ( with square brackets) and it is confusing things, - i have changed it to Code1 for the upload ...... hope it works this time!

Private Sub Worksheet_Change(ByVal Target As Range)
     '  ** Check if Cell J6 is updated
    Dim r As Range
    Dim ar As Integer
    If Not Intersect(Target, Range("J6")) Is Nothing Then  'There is a drop down with a list of possible defined named ranges to choose from in J6
        
        Set r = Sheet4.Range(Target)
        Sheet8.Range("A11").Resize(r.Rows.Count, r.Columns.Count).Value = r.Value
        ar = Range("B21").End(xlUp).row - 9
        If ar > 1 Then
            Range("H11").Resize(ar - 1).FormulaR1C1 = "=SUMIFS(Table2[Issue Qty],Table2[Code1],'Prepour_Issue_Sheet'!RC[-7],Table2[House No],'Prepour_Issue_Sheet'!R4C6)"
        End If
        
    End If
    
    Range("F2").Select
 
End Sub

[SamT]

Maybe this line

Set r = Sheet4.Range(Target)

Should read

Set r = Sheet4.Range(Target.Address)

Because Target is a Cell on a Specific sheet, so that code might be saying

Set r = Sheet4.Range(Sheet2.Range)

Ir r is supposed to be the same cell, on the same sheet, as Target, then all you need is

Set r = Target

[DaveGib]

Hi SamT,
Thanks again for taking the time to try and resolve my problem, Unfortunately that did not work either, but it got me to thinking.... I went back to the post where snb and mdMckillop gave solutions to the change event, and i changed it to snb's solution as below, but that still didn't solve the problem, so I commented out the whole procedure, and the screen still won't scroll!! (unless I select a different tab, then come back)

SamT, - I appreciate very much that you have tried to help, but i think it must be inherent in the actual worksheet, which I can live with as it is a minor irritation, - what I think I might do eventually, is recreate the sheet completely from scratch and see if that solves the problem.

Many, many thanks!!! Dave

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ar As Integer
    If Target.Address = "$J$6" Then
        With Sheet4.Range(Target)
            Sheet8.Range("A11").Resize(.Rows.Count, .Columns.Count) = .Value
        End With
    End If
    ar = Range("B21").End(xlUp).row - 10
        If ar > 1 Then
            Range("H11").Resize(ar - 1).FormulaR1C1 = "=SUMIFS(Table2[Issue Qty],Table2[Code1],'Prepour_Issue_Sheet'!RC[-7],Table2[House No],'Prepour_Issue_Sheet'!R4C6)"
        End If
End Sub

[SamT]

so I commented out the whole procedure, and the screen still won't scroll!!

Good! that proves it wasn't that Procedure.

it must be inherent in the actual worksheet,

Can't be, unless... you need to repair or reinstall Excel.

recreate the sheet completely from scratch

First copy the sheet to an empty xlsm workbook, delete it from the original, then copy it back to the original Wkbk.

[DaveGib]

SamT - thanks for all your advice, .... I will try as you suggest, re copying, and if that fails, will try a repair/reinstall.
It might come down to the latter as I have been experiencing strange behaviour in Excel recently, and have already checked for Virus or malware, but found none.
Keep well..........