"Selection" on VBA Excel SpreadSheet

  • Thread starter Thread starter C Brandt
  • Start date Start date
C

C Brandt

I am having a problem that I have yet to understand.

I started a new workbook and had a problem that I have seen before, but much
more repeatable. Clearly I am missing a key understanding on how selection
works.

I have a button on a sheet called "Control" that will work with another
sheet labelled "POS_DL".

The following are the first few lines of the subroutine:

Private Sub DLPOS_Click()
'
' Import Data File to POS_DL Sheet'

Sheets("POS_DL").Select
Range("A1:D1").Select


When it trys to execute the Range Select it gets a run time error '1004'
(Select method of Range Class Failed).

When I replace the line with:
Sheets("POS_DL").Range("A1:D1").Select
it works.

I had thought that there was a hierarchical selection scheme where I didn't
have to repeat the sheets addressing on every access.

What am I missing?

Thanks in advance,
Craig
 
try:

Private Sub DLPOS_Click()
'
' Import Data File to POS_DL Sheet'

Sheets("POS_DL").Select
Sheets("Pos_DL").Range("A1:D1").Select

========
Your range("a1:d1") wasn't qualified by anything.

When excel sees that kind of code in a General module, it uses the active sheet.

When excel sees that kind of code behind a worksheet, it uses the worksheet that
owns the code. And since you can only select cells on the activesheet and you
were trying to select cells on the sheet with the button, your code blew up!

with sheets("pos_dl")
.select
.range("a1:d1").select
end with

is another way to qualify the range.
 
If you detail what you are trying to do we can make suggestions that may
make your project work smoothly.
You do not need to select a worksheet to copy from it. This works from
anywhere in the workbook.

Sheets("POS_DL").Range("A1:D1").COPY sheets("destsheet").range("a1")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
C Brandt said:
I am having a problem that I have yet to understand.

I started a new workbook and had a problem that I have seen before, but
much
more repeatable. Clearly I am missing a key understanding on how selection
works.

I have a button on a sheet called "Control" that will work with another
sheet labelled "POS_DL".

The following are the first few lines of the subroutine:

Private Sub DLPOS_Click()
'
' Import Data File to POS_DL Sheet'

Sheets("POS_DL").Select
Range("A1:D1").Select


When it trys to execute the Range Select it gets a run time error '1004'
(Select method of Range Class Failed).

When I replace the line with:
Sub makeformula()
Click to expand...
cRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("h2").Formula = "=RANDBETWEEN(1," & cRows & ")"
End Sub> it works.
 
Dave:

What is confusing me is that the code I am attempting to use was working
elsewhere. In my first note, I looked for the simplest example for clarity.
I would have thought the by first selecting the Sheet with
[Sheets("POS_DL").Select] that the range would not need to be qualified
further. I thought that the system would look on the "selected" sheet.

As a sidebar: What is the difference between ".select" and '.activate"?

I have been going through my sub-routine (which, by-the-way, I changed from
a "Private" routine) and am systematically adding the "Sheets("POS_DL")."
and slowly getting it to work. BUT lines like the following are difficult to
review:
was: Range(Cells(2, 3), Cells(MaxPosDL, 3)).Select
now is: Sheets("POS_DL").Range(Sheets("POS_DL").Cells(2, 3),
Sheets("POS_DL").Cells(MaxPosDL, 3)).Select

Like I said, all this code worked in another workbook.
Why is it failing in this workbook.

Craig
 
OK. A little progress.There are several ways of dropping a button on the
spreadsheet. One way is to use the Toolbar/ Forms tool that floats around on
the screen and the other is the tool that is fixed to the border of the
Excel window. They act a little different. The sub-routine works when I call
it from a button generated with the floating toolbar but not when generated
with the fixed tool.
What is the difference between these two methods?

Craig
 
It depends on where the code is.

If the code is in a general module, then the unqualified range belongs to the
activesheet.

If the code is behind a worksheet, then the unqualified range belongs to the
worksheet that owns the code.

====
Start a new workbook.
Put 5 worksheets in that workbook (sheet1, ..., sheet5)
Select sheet1 and ctrl-click on sheet3 and ctrl-click on sheet5
You have now grouped those 3 sheets.

Then try:
worksheets("Sheet1").select
in your test code.
Look back at excel to see what's selected.

Now group Sheets 1, 3, 5 and do:
worksheets("Sheet1").activate
and look back at excel to see what's selected.

Notice the difference in what worksheets are grouped and what's the activesheet.

=======
It's kind of the same thing with
range("c3").activate and range("c3").select

It depends on what was selected first:

Range("A1:g99").select
range("c3").activate

vs:

Range("A1:g99").select
range("c3").select



C said:
Dave:

What is confusing me is that the code I am attempting to use was working
elsewhere. In my first note, I looked for the simplest example for clarity.
I would have thought the by first selecting the Sheet with
[Sheets("POS_DL").Select] that the range would not need to be qualified
further. I thought that the system would look on the "selected" sheet.

As a sidebar: What is the difference between ".select" and '.activate"?

I have been going through my sub-routine (which, by-the-way, I changed from
a "Private" routine) and am systematically adding the "Sheets("POS_DL")."
and slowly getting it to work. BUT lines like the following are difficult to
review:
was: Range(Cells(2, 3), Cells(MaxPosDL, 3)).Select
now is: Sheets("POS_DL").Range(Sheets("POS_DL").Cells(2, 3),
Sheets("POS_DL").Cells(MaxPosDL, 3)).Select

Like I said, all this code worked in another workbook.
Why is it failing in this workbook.

Craig

Dave Peterson said:
try:

Private Sub DLPOS_Click()
'
' Import Data File to POS_DL Sheet'

Sheets("POS_DL").Select
Sheets("Pos_DL").Range("A1:D1").Select

========
Your range("a1:d1") wasn't qualified by anything.

When excel sees that kind of code in a General module, it uses the active sheet.

When excel sees that kind of code behind a worksheet, it uses the worksheet that
owns the code. And since you can only select cells on the activesheet and you
were trying to select cells on the sheet with the button, your code blew up!

with sheets("pos_dl")
.select
.range("a1:d1").select
end with

is another way to qualify the range.
Click to expand...
Click to expand...
 
If you use a commandbutton from the Control toolbox toolbar, where does your
code reside?

If you use a button from the Forms toolbar, where does the code reside?
 
Dave:

I'm getting there.
Allow me to paraphrase to see if I now understand.
Because I used the Control Toobox to generate the button, any subsequent
code was put under the same umbrella as the button (the sheet on which it
was placed). Therefore any reference to cells on a different sheet always
requires a full address string. Had the code referenced only data on the
sheet on which the button resided, I could use the refernce to cells, rows,
and columns without the full path.

Why do I have so many Modules, half of which are empty, and how do I get
coding to go into a specific module. Some code I would like to group.

Thanks,
Craig
 
Instead of using "full address", I would have used a "fully qualified range".

And I like to qualify all my ranges--even when the code is in a General module
and refers to the activesheet and even when the code is behind a worksheet and
refers to the worksheet that owns that code.

....General module code...
Dim wks as worksheet
dim rng as range
set wks = activesheet
set rng = wks.range("a1:A10")

....Worksheet module code...
dim rng as range
set rng = me.range("a1:a10")

Me is a keyword that refers to the thing owning the code--it could be a
userform, it could be a workbook, but in a worksheet module, it refers to that
worksheet.

I think that when I do this, it makes life easier if I copy|paste the code
elsewhere. And I get the added benefit of VBE's intellisense--when the various
options pop up after you hit a dot.

======
As for combining code, if the code that you want to combine is in General
modules, then you can just select the code and Cut and paste to an every growing
module.

There are limits on how much code can fit in a module--about 64k if you copy the
code and paste into Notepad and save that file. But that's a rule of thumb. I
don't think it's ever been officially documented by MS.

Then rightclick on the empty module in the project explorer and choose delete.

And you may not just want to group code by "size". I'd put similar stuff
(convert to caps, convert to lower case, ...) in a single module. Or functions
that are only called by routines in that same module. But if that
function/subroutine can be called by other stuff, I'd put it in a separate
module.

===
And if you're recording a new macro, you may want to record into a new workbook
(save that workbook often while testing/enhancing), then when you're satisfied,
either drag the module from one project to the other workbook's project--or just
copy|Paste the code into a new (or existing) module in that original workbook.
 
As always Dave, Thanks for the help.
I fall into the beginners trap where you think that this is a one-time code
and will not likely be repeated, so why go to all the effort needed by
bullet proof code.
I'm begining to see the wisdom, but lack the background and knowledge.
Slowly gaining on the last two, thanks to the people at this location.
Thanks again,
Craig
 
Back
Top