Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Hide a macro button
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2019
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Hide a macro button

    Hi everyone

    Please can anyone give me a hand? I have a macro button that does something, however is there a simple piece of code I can include within this macro that will hide another macro button called 'Export' for example?

    If so, is there then also another piece of code I can use to unhide this button when I click on another button?

    Is this simple to do?

    Many thanks for any help you can provide me

  2. #2
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    1,083
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    Hi,
    Me.btnExport.visible = false or = true

    depands on how you have set it in the properties of the command buttin in the userform.

    You can set the userform Buttin to visible true or false in the properties and then do accordingly the opposite in the userform.

    HTH
    Last edited by silentwolf; Jul 12th, 2019 at 06:00 AM.
    Silentwolf



    I use MS Office 2010 and Windows 7

  3. #3
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    Hi
    Once again, you could do with giving us more info, so other Folk don't have to ask....

    Try and tell people the whole story:
    What type of button? Command? is it a Forms one, or ActiveX?
    Where is the button? On a UserForm, or a worksheet?
    Where's the code?
    etc etc.
    This will save others time, and make Folk more likely to answer you.
    I've made assumptions - buttons are on a sheet, they're ActiveX, I've called mine ComandButton 1 & 2.
    I've put my code into the buttons' _click event...
    To make CB1 hide, this is CB2's _click event code:
    Code:
    Private Sub CommandButton2_Click()
    Me.CommandButton1.Visible = False
    End Sub
    I've used the .Me keyword, as both buttons are in the same place. Obviously change to "Visible = True" when you want a button to be visible again.
    You can also set the default visibility (when the WB & sheet first open) by going into the button's properties, and setting "Visible" to True or False.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    Board Regular
    Join Date
    Apr 2019
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    Quote Originally Posted by sykes View Post
    Hi
    Once again, you could do with giving us more info, so other Folk don't have to ask....

    Try and tell people the whole story:
    What type of button? Command? is it a Forms one, or ActiveX?
    Where is the button? On a UserForm, or a worksheet?
    Where's the code?
    etc etc.
    This will save others time, and make Folk more likely to answer you.
    I've made assumptions - buttons are on a sheet, they're ActiveX, I've called mine ComandButton 1 & 2.
    I've put my code into the buttons' _click event...
    To make CB1 hide, this is CB2's _click event code:
    Code:
    Private Sub CommandButton2_Click()
    Me.CommandButton1.Visible = False
    End Sub
    I've used the .Me keyword, as both buttons are in the same place. Obviously change to "Visible = True" when you want a button to be visible again.
    You can also set the default visibility (when the WB & sheet first open) by going into the button's properties, and setting "Visible" to True or False.
    Thanks for offering your support. I am very sorry, I purposely didn't paste my vba code this time as I thought there would just be an easy line of code, please excuse my ignorance.

    My code is below. It's a command button and it's a form one not ActiveX. It's on a worksheet and all buttons are on the same worksheet.

    Thanks very much



    Code:
    Sub ClearNEWTODAY()'
    ' ClearNEWTODAY Macro
    '
    
    
    '
        Range("F9:I14").Select
        ActiveWindow.SmallScroll Down:=9
        Range("F9:I14,F19:I41").Select
        Range("F17").Activate
        ActiveWindow.SmallScroll Down:=-36
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("C9").Select
        ActiveCell.FormulaR1C1 = "Select"
        Range("C11").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C13").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C15").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C17").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("A1").Select
        Range("F4").Select
        ActiveCell.FormulaR1C1 = "Click 'Run' once you have filled in the details in yellow and the BOM will appear below:"
        
        
        Range("F9:I45").Interior.Color = RGB(255, 255, 255)
        Range("F9:I45").Font.Color = RGB(255, 255, 255)
        
        Range("A1").Select
        
        Dim xRg As Range
        Application.ScreenUpdating = False
            For Each xRg In Range("I25:I45")
                If xRg.Value = "0" Then
                    xRg.EntireRow.Hidden = True
             
                Else
                    xRg.EntireRow.Hidden = False
                End If
            Next xRg
        Application.ScreenUpdating = True
        
        
        
        
       MsgBox "The form has been reset!", vbInformation
        
        
    End Sub

  5. #5
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    Have a look at Domenic's post here:https://www.mrexcel.com/forum/excel-...l-buttons.html
    You can see that you can specify the buttons you wish to hide, in an array, then deal with them all at once.
    Personally, I never use form controls any more - I believe that ActiveX ones are much more flexible, and easier to use. They're easier to manipulate, and also have much easier access to their (more numerous) properties.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  6. #6
    Board Regular
    Join Date
    Apr 2019
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    Quote Originally Posted by sykes View Post
    Have a look at Domenic's post here:https://www.mrexcel.com/forum/excel-...l-buttons.html
    You can see that you can specify the buttons you wish to hide, in an array, then deal with them all at once.
    Personally, I never use form controls any more - I believe that ActiveX ones are much more flexible, and easier to use. They're easier to manipulate, and also have much easier access to their (more numerous) properties.
    OK thanks mate.

    I would rather use ActiveX but they've never worked for me for some reason, whenever I click on them once I've configured them then the button goes really small in a random place on the screen and nothing happens. I've always thought it was a bug with my machine or with Excel - or maybe it's just me being stupid! Most probably the latter...

  7. #7
    Board Regular
    Join Date
    Apr 2019
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    Quote Originally Posted by sykes View Post
    Have a look at Domenic's post here:https://www.mrexcel.com/forum/excel-...l-buttons.html
    You can see that you can specify the buttons you wish to hide, in an array, then deal with them all at once.
    Personally, I never use form controls any more - I believe that ActiveX ones are much more flexible, and easier to use. They're easier to manipulate, and also have much easier access to their (more numerous) properties.
    Sykes

    I've tried using the code that you linked me to and have put it in my vba code below but it's not working as it says it doesn't like the 'Option Explicit'. Do you know if there's an easy fix or if i'm doing something stupid here?

    Code:
    Sub ClearNEWTODAY()'
    ' ClearNEWTODAY Macro
    '
    
    
    '
        Range("F9:I14").Select
        ActiveWindow.SmallScroll Down:=9
        Range("F9:I14,F19:I41").Select
        Range("F17").Activate
        ActiveWindow.SmallScroll Down:=-36
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("C9").Select
        ActiveCell.FormulaR1C1 = "Select"
        Range("C11").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C13").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C15").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C17").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("A1").Select
        Range("F4").Select
        ActiveCell.FormulaR1C1 = "Click 'Run' once you have filled in the details in yellow and the BOM will appear below:"
        
        
        Range("F9:I45").Interior.Color = RGB(255, 255, 255)
        Range("F9:I45").Font.Color = RGB(255, 255, 255)
        
        Range("A1").Select
        
        Dim xRg As Range
        Application.ScreenUpdating = False
            For Each xRg In Range("I25:I45")
                If xRg.Value = "0" Then
                    xRg.EntireRow.Hidden = True
             
                Else
                    xRg.EntireRow.Hidden = False
                End If
            Next xRg
        Application.ScreenUpdating = True
    
    
    
    
    Option Explicit
    
    
        Dim ButtonName As Variant
        Dim ButtonNames As Variant
        
    '   Change/add button names accordingly
        ButtonNames = Array("ExporthisBOM")
        
        For Each ButtonName In ButtonNames
            ActiveSheet.Buttons(ButtonName).Visible = False
        Next ButtonName
        
       MsgBox "The form has been reset!", vbInformation
        
        
    End Sub

  8. #8
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    Option Explicit forces the code writer to declare all variables. It normally resides at the top of each module. Some Folk use it all the time.
    On this occasion, I suggest that you delete that line for now.

    For the sake of good practice, I'd also put the two variables:
    Code:
    Dim ButtonName As Variant
        Dim ButtonNames As Variant
    ... at the top of that procedure:
    Code:
    Sub ClearNEWTODAY()'
    ' ClearNEWTODAY Macro
    '
    
    
    '
    Dim ButtonName As Variant
    Dim ButtonNames As Variant
    
        Range("F9:I14").Select
        ActiveWindow.SmallScroll Down:=9
    Blah blah
    Finally, make sure that the button name(s) you're using are the actual VBA names of the buttons, and not just their captions.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  9. #9
    Board Regular
    Join Date
    Apr 2019
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    Thanks Sykes

    I'm still having an issue unfortunately.

    Please see my code below:

    Code:
    Sub ClearNEWTODAY()'
    ' ClearNEWTODAY Macro
    '
    
    
    
    
    Dim ButtonName As Variant
    Dim ButtonNames As Variant
    
    
        Range("F9:I14").Select
        ActiveWindow.SmallScroll Down:=9
        Range("F9:I14,F19:I41").Select
        Range("F17").Activate
        ActiveWindow.SmallScroll Down:=-36
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("C9").Select
        ActiveCell.FormulaR1C1 = "Select"
        Range("C11").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C13").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C15").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("C17").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("A1").Select
        Range("F4").Select
        ActiveCell.FormulaR1C1 = "Click 'Run' once you have filled in the details in yellow and the BOM will appear below:"
        
        
        Range("F9:I45").Interior.Color = RGB(255, 255, 255)
        Range("F9:I45").Font.Color = RGB(255, 255, 255)
        
        Range("A1").Select
        
        Dim xRg As Range
        Application.ScreenUpdating = False
            For Each xRg In Range("I27:I45")
                If xRg.Value = "0" Then
                    xRg.EntireRow.Hidden = True
             
                Else
                    xRg.EntireRow.Hidden = False
                End If
            Next xRg
        Application.ScreenUpdating = True
        
        
    '   Change/add button names accordingly
        ButtonNames = Array("ExporthisBOM")
        
        For Each ButtonName In ButtonNames
        ActiveSheet.Buttons(ButtonName).Visible = False
        Next ButtonName
        
        
    
    
       MsgBox "The form has been reset!", vbInformation
        
        
    End Sub
    The error I receive says 'Unable to get the buttons property of the worksheet class'. Have you any idea what this could mean?

    I am sure I'm using the right name of the button I'm trying to hide - ExporthisBOM is the name i.e. Sub ExporthisBOM() etc etc...

  10. #10
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to hide a macro button

    1. Is the worksheet which contains the button, actually active, when the code runs?
    2. Where is this procedure held?
    3. Can you ensure the sheet in question is the active worksheet, go into your "Immediate" window (in the VBA browser), paste this code, press the return key, then tell me what the line says which is printed underneath:
    Code:
    debug.print Activesheet.name & " " & ActiveSheet.Buttons(1).name
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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