PDA

View Full Version : Sheet Not Fully Active after Selecting or Activating



DaveGib
10-14-2017, 05:57 AM
Hi All,
Can someone please help me with a strange problem i am encountering?:banghead:

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!! :help

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
10-15-2017, 12:17 PM
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
10-15-2017, 10:32 PM
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. :banghead:
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
10-15-2017, 10:36 PM
Sorry,!!!! ........ I don't know why the code was split above, - I tried editing, but couldn't change it ...... my apologies!!!!!!

DaveGib
10-15-2017, 10:39 PM
Should be........



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[Code],'Prepour_Issue_Sheet'!RC[-7],Table2[House No],'Prepour_Issue_Sheet'!R4C6)"
End If

End If

Range("F2").Select

End Sub

DaveGib
10-15-2017, 10:42 PM
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
10-16-2017, 07:43 AM
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
10-16-2017, 08:41 AM
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
10-16-2017, 08:59 AM
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
10-16-2017, 09:14 AM
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..........