Why can't i select things on non-active sheet?

[chamster]

I can'r run the code below.

With Worksheets("Settings")
.Cells(1, 2).Select
End With


The solution i've been applying this far was to activate the sheet as follows.

Dim tempSheet as Worksheet
Set tempSheet as ActiveSheet
With Worksheets("Settings")
.Activate
.Cells(1, 2).Select
End With
tempSheet.Activate


However, that seems as an unneccessary complex approach. Hence, two questions arise.

1. How can i select a cell/range on a non-active sheet without activating it? Can it be done at all?

2. Why is the first piece of code not working? If it can't be done - why not?

For all it can help, when i try .Paste i experience the same kind of problematics. I'm guessing the thing i wish to do is easily achievable but that i picked a method that isn't the approach concieved for that purpose. Keep in mind that even if i've been a programmer for quite few years, the VBA is still very new to me.

[johnske]

...Hence, two questions arise.

1. How can i select a cell/range on a non-active sheet without activating it? Can it be done at all?No


2. Why is the first piece of code not working? If it can't be done - why not?The real question is - why do you want to? If you're selecting the range so the user can enter something in it then why isn't the sheet 'active', if you're not selecting the range so the user can enter something in it there's no need to use select on it :)

[chamster]

The real question is - why do you want to? ...if you're not selecting the range so the user can enter something in it there's no need to use select on it :)

I'm not trying to get the user to enter anything. Hence, we're arriving at what i expected - i'm using a screwdriver to hammer a spike. "Why?", somebody will ask. "Because i only got a screwdriver this far", i'll respond. :dunno

So, let's approach this issue from another angle. At a certain spot in my application, i need to get the number of things i'm regarding. Since i already have a tabel of width equal to that number i wish to perform the following.

ws.Cells(3, 4).Select
ws.Range(Selection, Selection.End(xlToRight)).Select
number = Selection.Columns.Count

For the above, i need to ws.Activate, though.

If there's an other way to simulate the CTRL+RightArrow, alternatively, an other way to learn the width of a set of non-empty cells, i'll be delighted to hear about it.

If it is th eonly way, then i cast a course at the creators of VBA! :motz2:

Perhaps i'm not the right man for the job, then... :whistle:

[johnske]

no need to select, try With ws
Number = .Range(.Cells(3, 4), .Cells(3, 4).End(xlToRight)).Columns.Count
End With

[chamster]

no need to select, try With ws
Number = .Range(.Cells(3, 4), .Cells(3, 4).End(xlToRight)).Columns.Count
End With

Ah, this way... Much nicer and cleaner. Thanks!

A while ago, i got to know that F2 in the editor will bring up the properties and methods for the current object. That is true, however, i don't seem to learn that .Cells can be done .End() on that way...

How should one go about it, if one wishes to get to know ALL the things that can be done with/to/on a Range, Worksheet, Cells, etc.?

[Bob Phillips]

The reason that you cannot selct on a non-active sheet is because select means the screen gets repainted (which is why it is SO inefficient), which cannot be done if that sheet does not have a window.

[johnske]

the VBA help files, programming guides (http://xlvba.3.forumer.com/index.php?showtopic=97), articles, trial and error, read posts in forums, ..., etc :)

[chamster]

the VBA help files, programming guides (http://xlvba.3.forumer.com/index.php?showtopic=97), articles, trial and error, read posts in forums, ..., etc :)

Got the point. I was hoping for something like JavaDocs but i guess i'll have to manage. The worst thing that can happen is that i'll learn something, ooooooh, or that you're going to send a hit-squad to Sweden to make me shut up. :wot

Thank you so much both for help this far and the goodies yet to come...: pray2:

[rory]

FYI, if you look at the Cells property of the WOrksheet object in the Object Browser, you will see that it returns a Range object. The End property belongs to the Range object (there is no Cell(s) object) and returns another range.