[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

simple macro button

Posted on 2011-10-31
8
Medium Priority
?
323 Views
Last Modified: 2012-05-12
Why will the code below work fine when stored in a module but as soon as I put it in a button and the sub becomes private it bombs out at Range("E2").Select
Private Sub CommandButton1_Click()

'
' Test Macro
'

'
    Sheets("PL and CF details").Select
    Range("E2").Select

Open in new window

0
Comment
Question by:BigWill5112
  • 5
  • 2
8 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 37058487
Change it to Public.
0
 

Author Comment

by:BigWill5112
ID: 37058552
Still doesn't work when changing it to public.  Still errors out at the range E2 line.
0
 

Author Comment

by:BigWill5112
ID: 37058558
The exact same code works fine when put on a button on a userform.  So I solved my problem but would like to know why the button on a worksheet doesn't work.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37059072
>> The exact same code works fine when put on a button on a userform.  So I solved my problem but would like to know why the button on a worksheet doesn't work.

That's easy!

When the button is on the worksheet then the focus is that sheet.  The first line changes the focus to the second sheet and therefore any attempt to select the range on the first sheet fails.

It will work fine if you use:

    Sheets("PL and CF details").Select
    Sheets("PL and CF details").Range("E2").Select

Chris
0
 

Author Comment

by:BigWill5112
ID: 37059080
So you can't have a button on a sheet that can access other sheets?  Just want to confirm Chris.  Thanks for your response!
0
 

Author Comment

by:BigWill5112
ID: 37059084
I mean...you always have to do all that classifying stuff when you have a button that you want to access other sheets???
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 37059101
IN teh context of a sheet based button yes ... but you can simplify for example:


    with Sheets("PL and CF details")
        .Select
        .Range("E2").Select
    end with
OR
    set ws = Sheets("PL and CF details")
    ws.select
    ws..Range("E2").Select

Essence is however it has to be qualified all that varies is how easy the qualification is made to be.

Chris
0
 

Author Closing Comment

by:BigWill5112
ID: 37059113
I don't like your answer, lol.  Kills my macro recording skills.  guess I will have to suffer writing the code.   I know - you consider it easy.  all jokes aside - THX!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question