Hide a macro button

C

Cuzzaa

Board Regular
Joined
Apr 30, 2019
Messages
86
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
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:
Upvote 0
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.
 
Upvote 0
Re: Help please - how to hide a macro button

sykes said:
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.
Click to expand...

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
 
Upvote 0
Re: Help please - how to hide a macro button

Have a look at Domenic's post here:https://www.mrexcel.com/forum/excel-questions/608333-vb-code-hiding-form-control-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.
 
Upvote 0
Re: Help please - how to hide a macro button

sykes said:
Have a look at Domenic's post here:https://www.mrexcel.com/forum/excel-questions/608333-vb-code-hiding-form-control-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.
Click to expand...

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...
 
Upvote 0
Re: Help please - how to hide a macro button

sykes said:
Have a look at Domenic's post here:https://www.mrexcel.com/forum/excel-questions/608333-vb-code-hiding-form-control-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.
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
macro to filter spreadsheet by customer code, send relevant data via email
Replies
2
Views
192
Peter_SSs
Peter_SSs
M
  • Question
Need multiple buttons to hide/show rows on another tab
Replies
8
Views
375
skillilea
S
N
  • Question
Copy the button text to a cell
Replies
1
Views
80
Micron
M
sparky2205
  • Solved
Toggle CheckBox to Hide/Unhide worksheet
Replies
6
Views
366
sparky2205
sparky2205
A
  • Question
Help Using VBA To Create A Workbook Containing VBA
Replies
2
Views
375
Aldk_1
A

Forum statistics

Threads
1,214,415
Messages
6,119,382
Members
448,889
Latest member
TS_711

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top