Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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?

    VB:
    Sub Button2_Click() 
        ufmRoofing.Show 
        Button2.Visible = False 
    End Sub 
    
    
    cheers
    toe
    (cheif idiot)
    Last edited by toecutter; January 19th, 2006 at 13: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?
    VB:
    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,039

    Re: hide sheet command button

    Hi ToeCutter:

    Try this if your command button is from CONTROL TOOLBOX

    VB:
    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

    VB:
    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,039

    Re: hide sheet command button

    Hi ToeCutter:

    If you are using Forms Toolbar try this.
    VB:
    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.
    VB:
    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 15:49.

    Excel Video Tutorials / Excel Dashboards Reports


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

    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.
    VB:
    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 
    
    

    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.

    VB:
     
    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 20: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, 22:01
  2. Hide Rows Based On Command Button Position
    By zfind in forum EXCEL HELP
    Replies: 3
    Last Post: June 3rd, 2008, 20:55
  3. Replies: 7
    Last Post: April 10th, 2008, 16:00
  4. Copy And Paste A Command Button To Another Sheet
    By aeromk in forum EXCEL HELP
    Replies: 1
    Last Post: April 19th, 2007, 04:58
  5. Replies: 6
    Last Post: January 13th, 2006, 18: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