" Select method of range class failed" if sheet not on top

[jad700]

Dim Pict As Object
Dim p As Object
Filename$ = "C:\aScopePic\jdpic7.png"
DoThis$ = "c:\windows\wgetbin\wget.exe -t1 -O"
DoThis$ = DoThis$ + "C:\aScopePic\jdpic8.png"
DoThis$ = DoThis$ + " 10.32.54.19/Image.png"
If Dir(Filename$) = "" Then Stop
ret = Shell(DoThis$):
' Works so far, pic saved locally
' Now the following works ONLY if the Effic tab was on top when I run
the VBA
' Otherwise the next line returns " Select method of range class
failed"
Worksheets("Effic").Range("B3:F18").Select
Worksheets("Effic").Range("B3:F18").Activate
ActiveSheet.Pictures.Insert (Filename$)
' I would like to be able to insert the picture onto any of the
sheets
' THANK YOU !!!!

 

[Paul Robinson]

Hi
Never activate if you don't have to

Worksheets("Effic").Range("B3:F18").Pictures.Insert (Filename$)

will probably work if the code worked originally with the sheet on
top.
Not checked though.
regards
Paul

 

[jad700]

Many thanks Avi and Paul
I got that fixed last night, but now have several more questions.
----
Q1
I use lines like
Sheet4.Cells(8, CurColSh4%) = "XYZ" and
Sheet4.Cells.ClearContents
extensively through my code, but I am concerned that the order of the
worksheets might change and would be a disaster.
I think I would like to use
Sheet("Effic").Cells(8, CurColSh4%) = "XYZ" but do not know the
right syntax.
----
Q2
The buttons I have made move around when running, specifically one
moves on top of the other. How can I lock their location.
---
Q3 the file size just keeps getting bigger, currently 5 meg for a 1000
line program,
even if I clear contents of the sheets. How can I fix this ?
---
Q4 - How can I get the current Worksheet and location (Range) as
variables, so I can jump back to them.
---
Optional -
Why Never activate if you don't have to? What is the difference
between activate and select.?
---
By the way, to fix it I used
' Store the pic on the Picts tab
Worksheets("Picts").Activate
Worksheets("Picts").Range("F5").Activate
ActiveSheet.Pictures.Insert (Filename$)
Worksheets("AllData").Activate
( Sorry Paul, I will go back and try your line of code )

 

[jad700]

Worksheets("Effic").Range("B3:F18").Pictures.Insert (LocalPicFilename
$)
Did not work !
The error was ....
Error 438
Object does not support this property or method
.....
However this does work
Worksheets("Picts").Select
Worksheets("Picts").Range("M16").Select
ActiveSheet.Pictures.Insert (LocalPicFilename$)
Worksheets("AllData").Select

Is using Select b etter than using Activate ??
Please see questions in previous post.
Thanks again !!!

 

[Paul Robinson]

Hi
Q1
Sheet("Effic").Cells(8, CurColSh4%).Value = "XYZ"

will work if CurColSh4% is a number.

Q2
Right click each button, go to Format Control and choose Properties.
Click on "Don't move or size with cells".

Q3
Don't know - very code dependent.

Q4
For a worksheet you can use

Set MyRange = Worksheets("ThisOne").Range("B1:C4")

or
With Worksheets("ThisOne").
Set MyRange = .Range(.Cells(1,2), .Cells(4,3))
End With

(note the dots in front of range and Cells). The second syntax is
useful if you generate cell locations with numerical variables.

On your other question, the macro recorder tends to generate code
which selects and activates as that is what your mouse does. You can
pretty much always avoid this in code and it gives you more control
over what you are doing (and makes it easier to read!). As a general
principle this example,

Worksheets("ThisOne").Activate
Range("A1").Select
Selection.Value = 3

which is what you might do with the mouse to put 3 in A1 on sheet
"ThisOne", can be replaced with

Worksheets("ThisOne").Range("A1").Value = 3

I don't know why
Worksheets("Picts").Range("F5").Activate
ActiveSheet.Pictures.Insert (Filename$)

works for you! I cannot see Pictures as a worksheet Property, Method
or function (introduced in Excel 2007 maybe?). As you don't refer to
the Range("F5") in the insert then it may be superfluous and this
might work (should work!),

Worksheets("Picts").Pictures.Insert (Filename$)

using the principle above.

regards
Paul