1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro to select after activation

Discussion in 'Software Development' started by AngelaCasale, Oct 31, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. AngelaCasale

    AngelaCasale Thread Starter

    Joined:
    Oct 31, 2011
    Messages:
    3
    Hi All, I am very new to macros and would like some help!

    I work in a school and have created a spreadsheet that tracks each students progress in maths. On on sheet I have a fed sheet from the input that bring through and individual students progress. I have created macros that move around this sheet as it is aimed for staff that arent too friendly with excel. The macros jump to name ranges. However once they jump there they are still selected which knocks out my formatting where I have hidden text by making it white.

    So far I have

    Application.Goto Reference:="Overview"

    I have tried figuring it out on my own but now joy. Tried deselect etc but I dont really know what I am doing!

    Any help would be much appreciated.

    Regards

    Angela :)
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Angela, welcome to the forum.
    I hope you excuse me, but I cannot picture what you mean or what you are trying to chieve.
    Is it possible to post a sample without the sensitive data?
    If "Overview" is a renage, the moment you dor Application.Goto it it will select the whole range when found, but as far as I understand you don't want the whole range selected.
    If Overview is als a worksheet the you can go to that sheet by saying Sheet("Overview").Activate
    But like I said, I cannot picture what you are doing so it's difficult to explain and solve (for me at least)
    Also, you forgot to mention which version of Excel you are using, not that that will clarify the issue but it's handy for the one who is trying to help you.
     
  3. AngelaCasale

    AngelaCasale Thread Starter

    Joined:
    Oct 31, 2011
    Messages:
    3
    Hi Hans, thanks.

    Yeh its excel 2010.

    I understand how I wrote it was a little confusing sorry. There is one worksheet that has lots of information on it. The teachers only need to see a collection of information at a time so I have created the macro buttons so they can navigate straight to that information within the sheet. Therefore the screen shows only the selected cells (as I have made them fill one screen size). So once it has put this into the screen view, I would like to deselect it.

    Hope this makes more sense.

    Angela
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Angela,

    It doesn't make sense to me, at least I can't picture it.
    If the data does not contain sensitive data maybe you could post a sample?
     
  5. AngelaCasale

    AngelaCasale Thread Starter

    Joined:
    Oct 31, 2011
    Messages:
    3
    I have removed sensitive data and just attached the relevant sheet with no formulas just the macros and the sheet. If you use them you can see what they are trying to do and why they arent working properly. Hope this is ok.

    Regards

    Angela
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I'll take a quick look this evening and let you know if I see anything that I can solve.
    I'll see if I have the time, I'm leaving the country for almost 3 weeks, but I'll put it my ToDo list
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I did a quick qone, Check module2 and see the Function GetStartAddress I wrote
    You can click the 7 Years and see the difference, add tha same line but with the corresponding range address as in Module 2 for 7_years and it whould do the job (I hope)

    Else you'll have to wait til after Nov 24
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I grouped your macro buttons to one named MyButton

    I used the references to trigger the macros just a way of keeping it easy to maintain, it uses the button's caption to filter
    Just a way of approaching the use of buttons an avoiding to have to write a different macro.

    Like I said I won't be on-line 'til around November 22.
     

    Attached Files:

  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Instead of using Application.Goto Reference use the line below to select only the first cell in the range instead of the entire named range. If the first cell contains hidden text also you can always include an extra empty cell in your named range and the macro would select it instead or you could also use the "offset" function to select the cell just above the first cell in the range.

    Code:
    Range("Year_7")(1).Select
    Here is an example of how to select the cell immediately above the first cell in the named range.

    Code:
    Range("Year_7")(1).Offset(-1, 0).Select
    To select the first cell to the left of the named range you can use the line below.

    Code:
    Range("Year_7")(1).Offset(0, -1).Select
    Rollin
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1024808

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice