Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: hide sheet command button

  1. #1
    Join Date
    17th October 2005
    Location
    Brisbane Australia
    Posts
    553

    hide sheet command button

    hi
    is it possible to add code to a sheet command buton to hide it after it has been selected?

    i found a similar example in the forum but it didnt work.

    i have this code in a module to open the form but it dosnt hide the sheet command button?

    Code:
    Sub Button2_Click()
     ufmRoofing.Show
     Button2.Visible = false
    End Sub
    cheers
    toe
    (cheif idiot)
    Last edited by toecutter; January 19th, 2006 at 14:08.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th July 2005
    Posts
    214

    Re: hide sheet command button

    Hi toecutter,
    couldnt you just delete it?
    Code:
    ActiveSheet.Shapes("CommandButton2").Delete
    Stefan

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    17th October 2005
    Location
    Brisbane Australia
    Posts
    553

    Re: hide sheet command button

    no m8
    the user will do this with maybe 6 command buttons then start again .
    so i will also need a command button to unhide all the hidden ones.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th January 2003
    Location
    India
    Posts
    1,042

    Re: hide sheet command button

    Hi ToeCutter:

    Try this if your command button is from CONTROL TOOLBOX

    Code:
    Me.CommandButton1.Visible = False
    'OR
    Sheets(" iNFORMATION").Shapes("cOMMANDBUTTON1").Visible = False
    UserForm1.Show
    Regards

    Maqbool

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th October 2005
    Location
    Brisbane Australia
    Posts
    553

    Re: hide sheet command button

    Quote Originally Posted by Maqbool
    Hi ToeCutter:

    Try this if your command button is from CONTROL TOOLBOX

    Code:
    Me.CommandButton1.Visible = False
    'OR
    Sheets(" iNFORMATION").Shapes("cOMMANDBUTTON1").Visible = False
    UserForm1.Show
    Regards

    Maqbool
    thx Maqbool
    i tried your code in different variations with no luck.
    the command button is on a xls. sheet form the forms tools.
    maybe i should be using the CONTROL TOOLBOX?
    toe

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th January 2003
    Location
    India
    Posts
    1,042

    Re: hide sheet command button

    Hi ToeCutter:

    If you are using Forms Toolbar try this.
    Code:
    Sub Button5_Click()
    Dim ws As Worksheet
    Set ws = Sheet1
    With ws
        .Shapes("Button 4").Visible = msoFalse
    End With
    End Sub
    Regards

    Maqbool

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    17th October 2005
    Location
    Brisbane Australia
    Posts
    553

    Re: hide sheet command button

    Quote Originally Posted by Maqbool
    Hi ToeCutter:

    If you are using Forms Toolbar try this.
    Code:
    Sub Button5_Click()
    Dim ws As Worksheet
    Set ws = Sheet1
    With ws
        .Shapes("Button 4").Visible = msoFalse
    End With
    End Sub
    Regards

    Maqbool

    Maqbool
    correct me if iam wrong.
    on a xls sheet there is 2 options for forms?
    1: CONTROL TOOLBOX
    2: FORMS

    these 2 options do not include the VBA forms?
    toe
    Last edited by toecutter; January 19th, 2006 at 16:49.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th January 2003
    Location
    India
    Posts
    1,042

    Re: hide sheet command button

    Hi Toecutter:

    I don't understand your question. Anyway, I hope you need more information about Control ToolBox & Form Tool Bar Controls. Is so, a very details help you can find in Excel Help. In Excel Help look for

    About controls: when to use ActiveX or Forms toolbar controls

    Quote Originally Posted by toecutter
    Maqbool
    on a xls sheet there is 2 options for forms?
    1: CONTROL TOOLBOX
    2: FORMS
    these 2 options do not include the VBA forms?
    toe

    Regards

    Maqbool

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: hide sheet command button

    Hi,

    For buttons from the Forms toolbox this should work.
    [vba]Sub Button1_Click()
    ' make buttons visible
    With ActiveSheet
    .Shapes("Button 2").Visible = True
    .Shapes("Button 3").Visible = True
    End With

    End Sub
    Sub Button2_Click()
    ' hide button then show form
    ActiveSheet.Shapes(Application.Caller).Visible = False
    UserForm1.Show

    End Sub
    Sub Button3_Click()
    ' hide button then show form
    ActiveSheet.Shapes(Application.Caller).Visible = False
    UserForm1.Show

    End Sub[/vba]

    Cheers
    Andy


  10. #10
    Join Date
    17th October 2005
    Location
    Brisbane Australia
    Posts
    553

    Re: hide sheet command button

    hi
    Andy Pope, have made this code from your example.

    Code:
    Sub cmdRoofing_Click()
    ActiveSheet.cmdRoofing.Visible = True
    ActiveSheet.cmdRoofing.Visible = False   ' hide ufmRoofing button
    ufmRoofing.Show
    End Sub
    
    Sub cmdLeftAndRightFlashings_Click()
    ActiveSheet.cmdLeftAndRightFlashings.Visible = True
    ActiveSheet.cmdLeftAndRightFlashings.Visible = False   ' hide ufmLeftAndRightFlashings button
    ufmLeftAndRightFlashings.Show
    End Sub
    
    Sub cmdTopAndBottomFlashings_Click()
    ActiveSheet.cmdTopAndBottomFlashings.Visible = True
    ActiveSheet.cmdTopAndBottomFlashings.Visible = False   ' hide ufmLeftAndRightFlashings button
    ufmTopAndBottomFlashings.Show
    End Sub
    
    Sub cmdBoxGutter_Click()
    ActiveSheet.cmdBoxGutter.Visible = True
    ActiveSheet.cmdBoxGutter.Visible = False   ' hide ufmBoxGutter button
    ufmBoxGutter.Show
    End Sub
    
    Sub cmdNext_Click() ' make buttons visible
    With ActiveSheet
    .Shapes("cmdRoofing").Visible = True
    .Shapes("cmdLeftAndRightFlashings").Visible = True
    .Shapes("cmdTopAndBottomFlashings").Visible = True
    .Shapes("cmdBoxGutter").Visible = True
    End With
    End Sub
    but i get a error message when i use the 2nd commanbutton (cmdLeftAndRightFlashings)
    Run-time error '1004':
    Not enought system resources to display completely.

    if i end the error message the rest work fine!!

    I added the ( ActiveSheet.cmdBoxGutter.Visible = True ) because the buttons properties kept changing to false so when i opend the sheet they where hidden.
    any ideas?
    cheers
    toe

    [edit] seems to be happining to any button i use the 2nd time.
    Last edited by toecutter; January 19th, 2006 at 21:04.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Command Button Link To Open Sheet(s)
    By dejayajay in forum EXCEL HELP
    Replies: 12
    Last Post: February 28th, 2013, 23:01
  2. Hide Rows Based On Command Button Position
    By zfind in forum EXCEL HELP
    Replies: 3
    Last Post: June 3rd, 2008, 21:55
  3. Replies: 7
    Last Post: April 10th, 2008, 17:00
  4. Copy And Paste A Command Button To Another Sheet
    By aeromk in forum EXCEL HELP
    Replies: 1
    Last Post: April 19th, 2007, 05:58
  5. Replies: 6
    Last Post: January 13th, 2006, 19:11

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
  •  
porno