Show message and command button when cell value changes

[sense_sven]

Dear all,
I looked up several posts within this and other forums but I cannot get my code to work. One of the reasons might be that I am absolutely VBA-naive ... Here is the simple problem:

I have a drop-down (via Data - Data validation - list) in cell D8 of a Excel 2013 work sheet. I inserted a command button which I renamed in the "Name Box" to FillTableButton (a click on the button fills up all empty cells of a cell range via a macro). What I want is that the command button only appears i.e. is visible when a certain value is selected in the drop-down i.e. "Individualized" (without ""). In addition a message box should appear. Below is my code that does not work. Whenever I change the drop-down an error message appears "Runtime error '424': Object required". The debugger marks the line below in red (but I do not know why).

I hope somebody has an idea.

Sven

Code starts here
---

Private Sub Worksheet_Change(ByVal Target As Range)




Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.

Set KeyCells = Range("D8")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then


If Range("D8").Value = "Individualized" Then
FillTableButton.Visible = True ' marked in debugger whenever I select Individualized
MsgBox "Make sure that you complete the table with individual hourly rates on the right >>"
Else
FillTableButton.Visible = False ' marked in debugger whenever I select others
End If
End If


End Sub

 

[Anthony47]

Try fully qualifying the control, eg:

Sheets("WichSheet").FillTableButton.Visible = True

Bye

 

[sense_sven]

Find the solution myself ...
It appears that my original code is for ActiveX button whereas I have a forms button. the correct code is:

Private Sub Worksheet_Change(ByVal Target As Range)




Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("D8")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then


' Display a message when one of the designated cells has been
' changed.
' Place your code here.
If Range("D8").Value = "Individualized" Then
Shapes("FillTableButton").Visible = True
MsgBox "Make sure that you complete the table with individual hourly rates on the right >>"
Else
Shapes("FillTableButton").Visible = False
End If
End If



Dim Rng As Range
Set Rng = Target.Parent.Range("D13")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Rng) Is Nothing Then Exit Sub
If Target.Value = "no" Then
Range("D14:D15").ClearContents
End If
End Sub