Hide/unhide button based on data validations

**Jazzy Max** · 10-08-2009, 07:56 PM

Hi All,

I am creating a disclaimer in one of my sheets.

Cell I15 contains 'Are you a resident for tax purposes?' Cell N15 has the data validation list of Yes or No.

Then I have a button below which covers rows18, 19 and 20.

Is there a way I can have this button hidden until Cell N15 = Yes??

I have tried to read other posts for this but was not able to apply that to my sheet.

Thank you in advance for all your help.

**Leith Ross** · 10-08-2009, 08:34 PM

Hello Jazzy Max,

Copy this macro to the worksheet that has cell "N15". This macro is for a Forms button on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Cells.Count > 1 Then Exit Sub

  If Target.Address = "$N$15" And Target.Value = "Yes" Then
     ActiveSheet.Buttons("Button 1").Visible = True
  Else
     ActiveSheet.Buttons("Button 1").Visible = False
  End If
  
End Sub

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

**Jazzy Max** · 10-08-2009, 08:54 PM

Hi Leith,

I have pasted the code and changed N15 to L13. But what it is doing at the moment is that once the button (in my case is "Bevel 6") is hit only then are the cells showing drop down lists.

Otherwise they are not.

Also, with the codes here is what I have:

PHP Code:


Sub Workbook_Open()



    Msgbox "Please enable macros before proceeding"

   

End Sub



Private Sub Worksheet_Open()

    ActiveWindow.Zoom = 80

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)



  If Target.Cells.Count > 1 Then Exit Sub



  If Target.Address = "$L$13" And Target.Value = "Yes" Then

     ActiveSheet.Buttons("Bevel 6").Visible = True

  Else

     ActiveSheet.Buttons("Bevel 6").Visible = False

  End If

  

End Sub

The first code is a Msgbox that should appear as soon as the workbook is open. But it doesn't any ideas??

Thank You for replying

**Leith Ross** · 10-08-2009, 09:00 PM

Hello Jazzy Max,

The macro expected you were using a command button from the Forms Toolbar - not an Auto Shape.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Cells.Count > 1 Then Exit Sub

  If Target.Address = "$L$13" And Target.Value = "Yes" Then
     ActiveSheet.Shapes("Bevel 6").Visible = True
  Else
     ActiveSheet.Shapes("Bevel 6").Visible = False
  End If
  
End Sub

**Jazzy Max** · 10-08-2009, 09:07 PM

Great!! worked well.

Last question the other codes I have above it, for instance the msgbox. How come that wont work? Is my wording correct?

I want the msgbox to pop up as soon as the workbook is open.

And the only field I have unlocked is L13 yet and everything is locked, yet when I open the book in its locked stated it has some other cell selected??

Once I press enter it goes to L13..