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?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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
 
NorieVBA ExpertCommented:
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
 
vipamanAuthor Commented:
No joy unfortunately. I still have to select the google button and then reselect TG2013.
0
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.