Announcement

Collapse
No announcement yet.

hide sheet command button

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 14:08.

  • #2
    Re: hide sheet command button

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

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment


        • #5
          Re: hide sheet command button

          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

          Comment


          • #6
            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

            Comment


            • #7
              Re: hide sheet command button

              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, 16:49.

              Comment


              • #8
                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

                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

                Comment


                • #9
                  Re: hide sheet command button

                  Hi,

                  For buttons from the Forms toolbox this should work.
                  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

                  Comment


                  • #10
                    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, 21:04.

                    Comment


                    • #11
                      Re: hide sheet command button

                      ok
                      upon further investigation i got this to work

                      Code:
                      Sub cmdRoofing_Click()
                      With ActiveSheet
                      .Shapes("cmdRoofing").Visible = True
                      End With
                      ActiveSheet.cmdRoofing.Visible = False   ' hide ufmRoofing button
                      ufmRoofing.Show
                      End Sub
                      
                      Sub cmdLeftAndRightFlashings_Click()
                      With ActiveSheet
                      .Shapes("cmdLeftAndRightFlashings").Visible = True
                      End With
                      ActiveSheet.cmdLeftAndRightFlashings.Visible = False   ' hide ufmLeftAndRightFlashings button
                      ufmLeftAndRightFlashings.Show
                      End Sub
                      
                      Sub cmdTopAndBottomFlashings_Click()
                      With ActiveSheet
                      .Shapes("cmdTopAndBottomFlashings").Visible = True
                      End With
                      ActiveSheet.cmdTopAndBottomFlashings.Visible = False   ' hide ufmLeftAndRightFlashings button
                      ufmTopAndBottomFlashings.Show
                      End Sub
                      
                      Sub cmdBoxGutter_Click()
                      With ActiveSheet
                      .Shapes("cmdBoxGutter").Visible = True
                      End With
                      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

                      Comment


                      • #12
                        Re: hide sheet command button

                        Glad you managed to resolve the problem and thanks for posting.

                        But why are you making the button visible just before making it invisible? Surely if you clicked it you could see it.

                        Also if you are now using buttons from the control toolbox would it not be better to Enabled and Disable the buttons rather than hide them.
                        Sub cmdRoofing_Click() 
                        ActiveSheet.cmdRoofing.Enabled=False
                        ufmRoofing.Show
                        End Sub
                        Sub cmdNext_Click() ' make buttons visible
                        With ActiveSheet
                        .cmdRoofing.Enabled=True
                        End With
                        End Sub

                        Cheers
                        Andy

                        Comment


                        • #13
                          Re: hide sheet command button

                          Originally posted by Andy Pope
                          Glad you managed to resolve the problem and thanks for posting.

                          But why are you making the button visible just before making it invisible? Surely if you clicked it you could see it.

                          Also if you are now using buttons from the control toolbox would it not be better to Enabled and Disable the buttons rather than hide them.
                          Sub cmdRoofing_Click() 
                          ActiveSheet.cmdRoofing.Enabled=False
                          ufmRoofing.Show
                          End Sub
                          Sub cmdNext_Click() ' make buttons visible
                          With ActiveSheet
                          .cmdRoofing.Enabled=True
                          End With
                          End Sub
                          cool works great
                          thx again Andy Pope

                          Comment


                          • #14
                            Re: hide sheet command button

                            mmm
                            well it works fine in my test sheet but not in my project
                            toe

                            Comment


                            • #15
                              Re: hide sheet command button

                              hi
                              the code seems fine until i change the zoom from 100%
                              It then produces this error message upon exercusion of the 2nd command button
                              Run-time error '1004':
                              Not enought system resources to display completely.
                              cheers
                              toe

                              Comment

                              Working...
                              X