VB

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Why do I get an error message with this code in Excel?
There is something wrong in this line: Range("A1").Select


Private Sub CommandButton1_Click()
Sheets("Forecast").Select
Range("A1").Select
End Sub
 
Hi

Probably because that code is in Sheet3. From there you have very limited power over
Sheet1. Put code like this in standard modules and call them from the sheets. Like:

Sub GoForecast()
Sheets("Forecast").Select
Range("A1").Select
End Sub

and in the sheet:

Private Sub CommandButton1_Click()
Call GoForecast
End Sub
 
Bob,

The problem is that you have the code in a Sheet module rather
than a general code module. In a general code module, unqualified
range references "roll up" through ActiveSheet and
ActiveWorkbook. Therefore, your line of code, if it were in a
general module, would be equivalent to

ActiveWorkbook.ActiveSheet.Range("A1").Select

However, in a Sheet module, unqualified range references "roll
up" through that particular sheet, not the ActiveSheet.
Therefore, your code, in the module for Sheet1, for example, is
the same as

ThisWorkbook.Worksheets("Sheet1").Range("A1").Select

But you just made another sheet active, so this line of code
fails when you attempt to select a cell that is not on the active
worksheet.

Change your code to

Sheets("Forecast").Select
Sheets("Forecast").Range("A1").Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Chip Pearson said:
(...) unqualified range references "roll
up" through that particular sheet, not the ActiveSheet. (...)
Change your code to
Sheets("Forecast").Select
Sheets("Forecast").Range("A1").Select
Click to expand...

Philosophy question: I never touch remote sheets from sheet modules, find that impossible
to overview and maintain. Maybe it's just my own non-standard way of organizing things.
Thoughts on this anyone ?


Best wishes Harald
Followup to newsgroup only please.
 
Back
Top