Hide Buttons based on Result

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
586
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have 6 buttons which have an individual macro. Based on the result in Cell A1 I want it to only show the button/macro and hide the other 5 but not sure how to do. ~When I right click on a MAcro and click on Assign these are the names that they are currently called and assigned to.

'Book1.xls'!Belgium
'Book1.xls'!Italy
'Book1.xls'!England
'Book1.xls'!France
'Book1.xls'!Spain
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
What are the names of your buttons (in the order of the macro assignments)?

Not really sure.

They arent actually buttons just rectangular boxes I created using the drawing toolbar then place text on them and then assigned them to a macro, i.e. first one is Belgium and when I right Click and assign macro it says 'Book1.xls'!Belgium and the MAcro Starts

Sub Belgium()
 
Upvote 0
If you right click the shape its name will appear in the Name Box to the left of the Formula Bar. You can also change the name there, pressing Enter when done.
 
Upvote 0
If you right click the shape its name will appear in the Name Box to the left of the Formula Bar. You can also change the name there, pressing Enter when done.

Rectangle 1, Rectangle 2 though to Rectangle 6
 
Upvote 0
Try code like this in the module for the Worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    With Target
        Me.Shapes("Rectangle 1").Visible = .Value = "Belgium"
        Me.Shapes("Rectangle 2").Visible = .Value = "Italy"
        Me.Shapes("Rectangle 3").Visible = .Value = "England"
        Me.Shapes("Rectangle 4").Visible = .Value = "France"
        Me.Shapes("Rectangle 5").Visible = .Value = "Spain"
    End With
End Sub
 
Upvote 0
Try code like this in the module for the Worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    With Target
        Me.Shapes("Rectangle 1").Visible = .Value = "Belgium"
        Me.Shapes("Rectangle 2").Visible = .Value = "Italy"
        Me.Shapes("Rectangle 3").Visible = .Value = "England"
        Me.Shapes("Rectangle 4").Visible = .Value = "France"
        Me.Shapes("Rectangle 5").Visible = .Value = "Spain"
    End With
End Sub

Created Module 2 and inserted code above, however what ever the fomula/result in A1 is the rectangles still appear
 
Upvote 0
As I said, the code goes in the module for the Worksheet (like Sheet1). It's event code which will fire when you change what's in A1 on that worksheet.
 
Upvote 0
As I said, the code goes in the module for the Worksheet (like Sheet1). It's event code which will fire when you change what's in A1 on that worksheet.

Not sure what you mean. I copied it from module 2 and place at end of module 1 where the other macros are.

I then clicked on Module 1 then clicked on sheet 1 and placed it there and it still shows all the rectangles??????
 
Upvote 0
No, it doesn't go in a General module like Module1. Right click the worksheet tab and choose View Code. Put the code in there.
 
Upvote 0

Forum statistics

Threads
1,224,570
Messages
6,179,610
Members
452,931
Latest member
The Monk

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