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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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

W
  • Solved
vba/macro to Find a line on another sheet and replace information in just 1 cell in that line
Replies
5
Views
95
mumps
mumps
R
  • Question Question
Button to hide/unhide rows with specific text
2
Replies
11
Views
519
NoSparks
N
S
  • Solved
Cannot assign macro to button
2
Replies
10
Views
295
SPS41
S
I
  • Solved
Hide command button if cell has a value advice
Replies
8
Views
489
dmt32
D
P
  • Solved
VBA to export several sheets into PDF
Replies
2
Views
136
PinkUnicorn
P

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

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