Excel VBA code crash - Access denied

I have added the following code to a button in a worksheet. The selected worksheets print Ok. However, after running and then I select anywhere in the spreadsheet I get runtime error and access denied. Is it the way I have closed it off? I am not sure what the range I50 is either? I set the print area in each worksheet. Regards Paul

Sub printworksheets()
'
' printworksheets Macro
'

'
    Sheets(Array("TG2013", "Windspeed map", "T wind", "Season", "Environment", _
        "Displacement ", "Ce(z)", "Ce,t")).Select
    Sheets("TG2013").Activate
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets(Array("TG2013", "Windspeed map", "T wind", "Season", _
        "Environment", "Displacement ", "Ce(z)", "Ce,t")).Select
    Sheets("TG2013").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Range("I50").Select
End Sub
vipamanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
On what line does the error occur?
0
vipamanAuthor Commented:
That is the strange thing. it points to some other code. It highlights the code with a load of plus signs next to it, but it worked ok until I put the new code in.

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Range("S16").Value

Case "Type B"
          Sheets("T wind").Shapes("Picture b").Visible = True +++++++++++++++++
          Sheets("T wind").Shapes("Picture c").Visible = False
          Sheets("T wind").Shapes("Picture d").Visible = False
          Sheets("T wind").Shapes("Picture e").Visible = False

Case "Type C"
          Sheets("T wind").Shapes("Picture b").Visible = False
          Sheets("T wind").Shapes("Picture c").Visible = True
          Sheets("T wind").Shapes("Picture d").Visible = False
          Sheets("T wind").Shapes("Picture e").Visible = False
       
Case "Type D"
          Sheets("T wind").Shapes("Picture b").Visible = False
          Sheets("T wind").Shapes("Picture c").Visible = False
          Sheets("T wind").Shapes("Picture d").Visible = True
          Sheets("T wind").Shapes("Picture e").Visible = False
         
Case "Type E"
          Sheets("T wind").Shapes("Picture b").Visible = False
          Sheets("T wind").Shapes("Picture c").Visible = False
          Sheets("T wind").Shapes("Picture d").Visible = False
          Sheets("T wind").Shapes("Picture e").Visible = True
         
End Select
End Sub
0
Mike in ITIT System AdministratorCommented:
At a quick glance at your code:
You are doing the same thing twice:
  1. You are selecting an array of cells
  2. Then activating the sheet
  3. Then you TAB backward
  4. Then you do it all again (steps 1 and 2)
  5. Then you print the sheet
  6. Then select a cell
.
The first 3 lines of code are completely unnecessary.

Range("I50").select = this is sending your cursor to cell I50 and selecting it, this happening on sheet TG2013 as it is the last sheet that is made active. I don't see a reason for this in the code that you provided

Then like MarcoShadow says what line does the error occur? I'm guessing it's on the line: ActiveWindow.ScrollWorkbookTabs Sheets=-1

Your code should probably look more like this:

Sub printworksheets()
' printworksheets Macro
	Sheets(Array("TG2013", "Windspeed map", "T wind", "Season", "Environment", "Displacement ", "Ce(z)", "Ce,t")).Select
	Sheets("TG2013").Activate
	ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
	Range("I50").Select
End Sub

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mike in ITIT System AdministratorCommented:
Could you provide a copy of your workbook so that we can work through and see what is happening?
0
vipamanAuthor Commented:
File attached
scaffoldv27.xlsm
0
NorieAnalyst Assistant Commented:
I can run the first set of code without problem either from the button on sheet 'TG2013' or directly from VBE.

Note, if you just want to print the sheets in the array you could use this.
    Sheets(Array("TG2013", "Windspeed map", "T wind", "Season", "Environment", _
        "Displacement ", "Ce(z)", "Ce,t")).PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

Open in new window

0
Mike in ITIT System AdministratorCommented:
I am also able to run this without a problem, both from the button and from manually running the macro. I am then able to click anywhere on the sheet and even change sheets and click anywhere without a problem.

You can do as Norie suggests and update the macro to be just this:
Sub printworksheets()
        Sheets(Array("TG2013", "Windspeed map", "T wind", "Season", "Environment", "Displacement ", "Ce(z)", "Ce,t")).PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
End Sub

Open in new window

0
vipamanAuthor Commented:
Mmm can you try adding some text in the page column. At 1st, all seems ok and then error! Ps updated to the new simplified code
0
vipamanAuthor Commented:
Argh. I know what the problem is but not sure how to fix it without user intervention. It appears on completion of printing, all tabs are selected apart from the google tab. If I reselect say tab TG20:13 everything is OK.
0
Mike in ITIT System AdministratorCommented:
You can try this:
Sub printworksheets()
        Sheets(Array("TG2013", "Windspeed map", "T wind", "Season", "Environment", "Displacement ", "Ce(z)", "Ce,t")).PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        Sheets("TG2013").Activate
End Sub

Open in new window


This should then activate that sheet only instead of all of them.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vipamanAuthor Commented:
No joy unfortunately. I still have to select the google button and then reselect TG2013.
0
NorieAnalyst Assistant Commented:
What happens if you run the code both MIke and myself suggested when there is only one sheet selected?
0
vipamanAuthor Commented:
To enable the code to run (as a user) you have to be in TG2013. The printout works fine now, but on finish it has all works selected apart from Google. I found some other code similar to yours but it still does not deselect work sheets or make TG2013 active.
0
NorieAnalyst Assistant Commented:
The code Mike and I doesn't select/activate any sheets, that isn't needed to print multiple sheets.

Can you post the exactl code you arr using now?

PS How does 'Google' not work?

Do you mean that sheet isn't printed?
0
vipamanAuthor Commented:
Sorry I am just offsite at the moment. I wasn't clear on the google issue.

I am using Mike's last entry

Sub printworksheets()
        Sheets(Array("TG2013", "Windspeed map", "T wind", "Season", "Environment", "Displacement ", "Ce(z)", "Ce,t")).PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        Sheets("TG2013").Activate
End Sub

Google worksheet is not printed - this is correct. On completion of printing all worksheets apart from google are selected. Because this happens and when you try to enter some text you get an error. I have to select the goggle tab and then TG2013 to stop the error. Hope this makes sense.
0
NorieAnalyst Assistant Commented:
To print the 'Google' sheet you would need to add its name to the array alongside all the other sheets to be printed.

Before you run the code which sheets are selected?
0
vipamanAuthor Commented:
Just the TG20:13 one as I want. Following printing, all apart from Google appear to be selected. I have seen this before where having multiple tabs causes VBA to throw a wobble. Mike's code Sheets("TG2013").Activate should cure this but it doesn't.
0
Ejgil HedegaardCommented:
When you activate the sheet, it is still within the array selection.
Use Sheets("TG2013").Select and you replace the array selection with a single sheet.
0
vipamanAuthor Commented:
Thanks chaps. My knowledge has expanded further
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.