opening main worsheets at same cell

nedbarra

New Member
Joined
Dec 3, 2018
Messages
18
Office Version
  1. 365
Have 'office 365' solely used for a selection of notebooks that contain a series of accounts worksheets.
They've been in use for some time but have recently had problems with opening at a specific cell...which for working needs is set at A16
There have been quite a few MS online repairs to try and solve things...but problem continues...
Here is the code being used...

Private Sub worksheet_activate()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Me.ScrollArea = "A16:y56"

With ThisWorkbook.Sheets("prsnldet")
Me.Range("c4").Value = .Range("g28").Value 'business
Me.Range("c5").Value = .Range("g28").Value 'description

Me.Range("w4").Value = .Range("n16").Value 'date from
Me.Range("w5").Value = .Range("n18").Value 'date to

Application.ScreenUpdating = True
Worksheets("sept").Range("a16").Select

End With
End Sub

Anyone spot anything that could maybe cause this to not function....or maybe recommend
some changes that may get this working as meant
Thank you
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,418
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Possibly, events have been disabled. Open the VBE (alt+F11 key), open the Immediate Window (ctrl + g), type : Application.EnableEvents = True and press enter.
 

nedbarra

New Member
Joined
Dec 3, 2018
Messages
18
Office Version
  1. 365
Possibly, events have been disabled. Open the VBE (alt+F11 key), open the Immediate Window (ctrl + g), type : Application.EnableEvents = True and press enter.
Thanks JoeMo, did as you advised
My machine does not seem to recognise the ALT + F11 shortcut so I went through by DEVELOPER - Visual Basic to Immediate window where a message box appeared and I typed the 'Application.EnableEvents' ...but there was nowhere to press enter.

I then went back through the worksheet tab 'April' and entered the 'EnableEvents'...trying both before and after the 'Screenupdating = Tre...
Unfortunately did not work

It all works fine with my previous way using a COMMAND BUTTON option....all is well
But I need to enter 'NAME and BUSINESS' on each worksheet to identify client and rather than have large data stored in the THISWORKBOOK... I am attempting to do things with the help of the worksheet.ACTIVATE

I have read many negative comments regarding SELECT and ACTIVATE
This should be a minor task with VBA...
Why is it so diffivult to do
nedbarra
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,418
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are you on a Mac or a PC? If PC, I have never seen a message box pop up when activating the Immediate Window with ctrl+g. The Enter is done on your keyboard after typing Application.EnableEvents = True in the Immediate Window.
 

nedbarra

New Member
Joined
Dec 3, 2018
Messages
18
Office Version
  1. 365

ADVERTISEMENT

Are you on a Mac or a PC? If PC, I have never seen a message box pop up when activating the Immediate Window with ctrl+g. The Enter is done on your keyboard after typing Application.EnableEvents = True in the Immediate Window.
JoeMo
Sorry for delay, - family birthday so have had little chance to get back to you
It's an HP Compaq Elite 8300 ~Im using...which has served me well...up to now
Have a couple of other HP's which are also used for client's accounts...and an ASUS

I have taken the time to go back and check some quite old files which were all given the same code
The one file I spent time on today worked perfectly on all worksheets when opened first time...but when I moved the cell off screen, and activated them again, no worksheets opened at cell A16...at all
I'm no heavy VBA user and simply use for transfer of accounts figures to TP & L sheets and calc of tax / NIC dues...etc
What concerns is I've been using the same 'standard code' all these years...with no hiccups
So, what on earth has suddenly gone wrong with the coding to make this happen

The notebook today had modules with command button actions and the 'activate' opening was NOT used...
But now it seems that they have been affected as well and are not opening at A16

I have been 'repairing' online almost every night for the past week to see if there is any fault that is maybe causing this....but I'm absolutely baffled as to why this has all started... and the difficulty I'm having trying to rectify things
I give my clients copies of these monthly .worksheets which all have the Command Button strategically placed for their pressing at each 'sitting'...when complete, they return email their file and I do the standard working and checking needed
But, there have been several occasions when they have NOT pressed (as clients do)...so I tried to work in the 'activate and deactivate' procedures so that the actions would be done automatically...

Now it seems that all actions I am using are having problems
Can you give any help on what the best code would be for 'opening each worksheet' so the top of the worksheet is taking screen view, if you know what I mean

Thank you
nedbarra
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,418
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can you give any help on what the best code would be for 'opening each worksheet' so the top of the worksheet is taking screen view, if you know what I mean
Thank you
nedbarra
Don't know what the 'best' is, but the simplest would be to use a worksheet_activate event and simply select cell A1 to put the top of the sheet in view.
 

nedbarra

New Member
Joined
Dec 3, 2018
Messages
18
Office Version
  1. 365
Don't know what the 'best' is, but the simplest would be to use a worksheet_activate event and simply select cell A1 to put the top of the sheet in view.


It's the 'activate event' that I've been relying on for ages and it has always worked according to plan ...up till now...
I've no idea at all why this has suddenly become such an issue...
The 'activate' was shown in the 'sample' I sent...
I've used this to put client name and description + dates and then used the 'de-activate' to get the figures into the respective columns on the used worksheets. on closure...
all seemed well until just last week
I'm stumped...
I went and retried the 'command button' option only to find the client name and business detail are not now responding to the 'button'...

As said, I have been 'online repairing' my OFFICE 365 ...quite a few tiles recently
I wonder if I should maybe attempt to contact MS chatline and put in a few questions to them

Thanks for your help...
nedbarra
 

Watch MrExcel Video

Forum statistics

Threads
1,129,430
Messages
5,636,227
Members
416,908
Latest member
Streetsweeper

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
Top