+ Reply to Thread
Results 1 to 7 of 7

How to hide a selection of objects but show objects automatically when opening wkbk

  1. #1
    Registered User
    Join Date
    05-24-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    How to hide a selection of objects but show objects automatically when opening wkbk

    Hello,

    I am wondering if there is a way to setup a couple of objects (shapes) that appear in the middle of the screen any time the workbook is opened but be able to hide the object after making a selection?

    For details:

    I have a Shape in the middle of my spreadsheet that asks a yes or no question. Regardless of which button is pressed (yes or no) I would like the box along with the yes/no buttons to disappear. Once the person saves and closes I want the boxes to appear again if they reopen the workbook.

    ie. If 'No' is pressed, nothing happens except the objects disappear
    If 'Yes' is pressed a sub is run that created a new column, etc. and then the objects disappear

    (Objects being two rectangle "buttons" over top of one large rectangle)

    For aesthetic purposes I have used shapes as buttons (two rectangles over top of one large rectangle) instead of inserting the controls from the developer tab.

    Thank you in advance for your help!

  2. #2
    Registered User
    Join Date
    10-04-2012
    Location
    US
    MS-Off Ver
    Word, Excel, Access all 2007 & 2010. Outlook 2010
    Posts
    56

    Re: How to hide a selection of objects but show objects automatically when opening wkbk

    If you attach your workbook we may be able to help.

    After you start a new message in the "Quick Reply" area.

    • Click the [Go Advanced] button, below the "Quick Reply" box.
    • Scroll down to click [Manage Attachments] button.
    • Click [Add Files] button with green + in the new "Manage Attachments" window.
    • Click the [Select Files] button.
    • Navigate to select your file and click [Open] button.
    • Click the [Upload Files] button.
    • Click the {check box} in the bottom right corner of your file.
    • Click [Done] button in bottom right of window.
    • Click [Submit Reply] to send your complete message with attachments.


    Now, see, wasn't that easy..? Trust me it's WAY worth it.

    Cheers!
    K

  3. #3
    Registered User
    Join Date
    05-24-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to hide a selection of objects but show objects automatically when opening wkbk

    Example Workbook.xlsm

    I have attached an example of what I am trying to do.

    I realized that a way simpler version of this would just be to create a permanent button that says "Click to add week" but I think if someone can provide the answer to this is will help me much more in the future as well.

    Thank you!

  4. #4
    Registered User
    Join Date
    10-04-2012
    Location
    US
    MS-Off Ver
    Word, Excel, Access all 2007 & 2010. Outlook 2010
    Posts
    56

    Lightbulb Re: How to hide a selection of objects but show objects automatically when opening wkbk

    Okay! I'm with you on this; I actually like the idea and I can likely use it too.

    I assume that the 'Redo_Macro' isn't done completely..? I'm not sure I understand the logic of copying the data and setting the color. Is the intent to later change colors? Otherwise all the columns will eventually be the same color again.

    So here's the thing, I have an obligation tonight but I can look at this tomorrow morning. Essentially shapes do not have the properties needed to do what you want but I think I can work around that and make them appear to have the properties we need to do this. I'll take a swing at this in the morning. Alright!?

    Cheers!
    K

    This may be a really good idea, you know..? If it catches on we can say 'we' did it. Who knows...

  5. #5
    Registered User
    Join Date
    05-24-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to hide a selection of objects but show objects automatically when opening wkbk

    The reason that I am changing the colours is because this is a spreadsheet that gets updated weekly and different people do it randomly throughout the week. Usually there will be formulas in each cell that change slightly every week, which makes it easier to copy the column. Unfortunately the formulas are not consistent enough for me to easily make it completely automated (it would take a lot of time since the information is pulled from about 20 different tabs, all with changing formats - that's a new project in itself). Anyways, if the text automatically turns purple, when people update the formulas with the correct numbers they can change it to black to mark that it is "complete". That way no one will waste their time trying to update numbers that have already been finished and everyone will have an idea of what needs to be completed before the end ofthe week (does that make sense?). So at the end of the week when you want to add on a week all of the numbers in the newest column should ideally be black.

    I know that that issue can be worked through but I simply don't have the time to go through and automate the sheet completely right now.


    That would be awesome if you could take a look at it. Thank you so much!

  6. #6
    Registered User
    Join Date
    10-04-2012
    Location
    US
    MS-Off Ver
    Word, Excel, Access all 2007 & 2010. Outlook 2010
    Posts
    56

    Re: How to hide a selection of objects but show objects automatically when opening wkbk

    I have good news and bad news...

    Okay, the bad news first (I guess it's not terrible though) I messed around with this a bit but have nothing to show for it just yet.

    The good news is that shapes do have the the properties we need! Who knew... Anyway I just wanted to let you know I was still thinking through this.
    I did get hung up slightly with trying to present a ShapeRange as 'Modal' so if there are any MOD's out there watching this one...? Just don't try using Google for "Modal Shapes"; there went half my day[LOL]! I may have to ask for help on that but I haven't given it up so stay tuned.

    Let me know if you've developed your own or changed direction otherwise I'll have more by Monday.


    Cheers!
    K

  7. #7
    Registered User
    Join Date
    10-04-2012
    Location
    US
    MS-Off Ver
    Word, Excel, Access all 2007 & 2010. Outlook 2010
    Posts
    56

    Re: How to hide a selection of objects but show objects automatically when opening wkbk

    These functions work to answer the OP directly but I'd really like to finish it a bit with a 'Modal Form' effect. Let me know if you need help applying these in your workbook. I figured that since you already have the macro assigned in the shape that using these would come easy enough.

    Just change the name of the shape in the worksheet "Name" or change the quoted names below to what you are using.

    Please Login or Register  to view this content.

    I may still add more on this someday but I wanted to get you your solution rather than try to add mine too. LOL

    Happy Day!!
    K

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1