Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: Buttons in Excel

    Hi,

    Does anyone know if it's possible to turn a button placed on sheet1 to visible=False and visible=True.

    What I'm trying to do is something along the lines of the below, but I can't find a way in VB to pick up control of the buttons.

    Worksheets.("Sheet1").Button("Button1").visible = False

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably best to ask this in the Excel forum....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    VB Buttons in Excel

    Hi,

    Does anyone know if it's possible to turn a button placed on sheet1 to visible=False and visible=True.

    What I'm trying to do is something along the lines of the below, but I can't find a way in VB to pick up control of the buttons.

    Worksheets.("Sheet1").Button("Button1").visible = False

    Thanks

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    What do you want to achieve? The code is correctly formatted, but on its own it will not do anything.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I have 3 buttons on an excel page (Sheet1). The buttons are named Button1, Button2, Button3.

    My ideal starting point would be to make button2, and button3 invisible from the starting point.

    Button1 is a submit button. When pressed I would like the submit button to be hidden, and Button1, and Button2 to become visible.

    No matter what I try, I'm just getting messages 'Object doesn't support this property or message".

    I actually don't care how it's done - The buttons could be made invisible; greyed out; shrunk in size; not even present to start with and then created as part of the command behind button1...

    The long winded solution I have at the moment is to put if commands behind button1 and 2 stating that if the current windows user, is the same user who pressed submit, then do nothing... (There's a hidden audit sheet which tracks who pressed submit).

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Are you using form buttons or ActiveX buttons? I think that you will only be able to use VBA to control their status with the latter.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    The Worksheet class does not have a Button property which is why you get that error. As weejas said, the correct answer depends on what type of buttons they are (ActiveX vs Forms) - but both can be made hidden via VBA. Tell use which type they are and we can show you how to do it!

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads merged
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Only duplicate because you asked me to post it in the excel forum, and then moved the 1st one after I posted the 2nd!

    Thanks for your help everyone. I'll consider using ActiveX buttons but it might be easier just to put my if condition in the subs. At least I know now that the route I was trying was a dead-end.

  11. #11
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Quote Originally Posted by christyxo View Post
    At least I know now that the route I was trying was a dead-end.
    It wasn't a dead end!

    As I said, you can do it with either forms controls or activeX controls: we were just waiting for you to clarify which you are using so we could show you the correct approach.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by christyxo View Post
    Only duplicate because you asked me to post it in the excel forum, and then moved the 1st one after I posted the 2nd!
    not quite true

    I made the posting about Excel and moved the thread at the same time.....
    but who cares, ultimately you got the answer you needed (I hope)
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Thanks all; I revisited this and found that it's a lot simpler than expected;

    Code:
    
        ActiveSheet.Shapes("Button 01").Visible = True / False

  14. #14
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    In that case you are using Forms controls.
    Code:
    ActiveSheet.Buttons("Button 01").Visible = True / False

Posting Permissions

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