'Selection' Compile Error

[gplhl]

Hi All,

having a problem with Excel 2016, code that had always worked has stopped working! When it is run I get an error. "Compile error: Expected Function or variable"
and highlighted is "Selection" in the code.

I am trying to write new code to do other jobs and anything with "Selection." in it fails with the same error!

Anyone else come across this or have an answer, it's completely messing up my work at the moment.

 

[nemmi69]

can you post a sample of your code.

 

[gplhl]

Yep, where I have selection in bold is where it fails, this worked previously:

Sub SelectedRangeToImage()
' modified
Dim tmpChart As Chart, n As Long, shCount As Long, sht As Worksheet, sh As Shape
Dim fileSaveName As Variant, pic As Variant
'Create temporary chart as canvas
Set sht = Selection.Worksheet
Run ("UnprotectWorkbook")
Run ("unprotectSheet")
Run ("Selection")
Range("B1:H17").Copy
Selection.Copy
sht.Pictures.Paste.Select
Set sh = sht.Shapes(sht.Shapes.Count)
Set tmpChart = Charts.Add
tmpChart.ChartArea.Clear
tmpChart.Name = "PicChart" & (Rnd() * 10000)
Set tmpChart = tmpChart.Location(Where:=xlLocationAsObject, Name:=sht.Name)
tmpChart.ChartArea.Width = sh.Width
tmpChart.ChartArea.Height = sh.Height
tmpChart.Parent.Border.LineStyle = 0
'Paste range as image to chart
sh.Copy
tmpChart.ChartArea.Select
tmpChart.Paste
'Save chart image to file
fileSaveName = "N:\Continuous Improvements\Gary\Image_Save\Slide - " & ActiveSheet.Name & ".jpg" 'Application.GetSaveAsFilename(fileFilter:="Image (*.jpg), *.jpg")
If fileSaveName <> False Then
tmpChart.Export Filename:=fileSaveName, FilterName:="jpg"
End If
'Clean up
sht.Cells(1, 1).Activate
sht.ChartObjects(sht.ChartObjects.Count).Delete
sh.Delete

Run ("ProtectSheet")
End Sub

 

[gplhl]

Even this doesn't work anymore:

Sub CentreAcrossSelection()


With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

 

[Fluff]

Do you have any Subs/Functions or variables called "Selection"

 

[gplhl]

Mmm I’ll take a look and report back in the morning.
I may have inadvertently created one!

 

[Norie]

What is Selection meant to refer to here?

Set sht = Selection.Worksheet

 

[gplhl]

Do you have any Subs/Functions or variables called "Selection"

Thanks for the pointer, its been a while since I coded. Still a bit rusty. Schoolboy error. That's exactly what it was...

 

[Fluff]

Glad it's sorted & thanks for the feedback