Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Address = "$D$9" then
Select Case Range("salestax").Value
Case 0.00001 To 100
ToggleButton1.Visible = True
Case Else
ToggleButton1.Visible = False
End Select
End If
End Sub


--
Regards,
Tom Ogilvy

"Jim at Eagle" wrote in message
...
I assume any cell change on sheet fires this code. Is there a way to limit
to
one cell only to fire code?
My code changes visibility of ToggleButton only, and that is dependent on
contents in cell D9, a user entry cell.
Thanks

"Peter T" wrote:

I didn't see Chip's first reply when I posted, but as he pointed out you
shouldn't try and change the Target cell/range in the change event. But
if
you do need to change that, or any cell within the event, you should
disable
events before and reset after or you will go round in circles.

However that's not your problem here - you can't change the address of a
range.

Do you really want to change any cell on the sheet that's just been
changed,
and if so how do you want to change it. Or, guessing - do you want to
put
the address of the just changed cell in D9, if so -

On Error GoTo errH
Application.EnableEvents = False
Range("D9") = Target(1).Address(0, 0)
'code
errH:
Application.EnableEvents = True

Regards,
Peter T

Something is amiss
I tried just the following:

Private Sub Worksheet_SheetChange(ByVal Target As Range)
Target.Address = "D9"
ToggleButton1.Visible = False
End Sub

Nothing change on the unprotected woorsheet after changing the value
of D9

"Jim at Eagle" wrote:

I tryed this with no effect.

Private Sub Worksheet_SheetChange(ByVal Target As Range)
Dim bVis As Boolean
If sheet1.Range("D9") = 0.00001 Then
bVis = True
Else
bVis = False
End If
ToggleButton1.Visible = bVis
End Sub

"Peter T" wrote:

Hi Jim,

Assuming your Toggle button is a worksheet control, try

Dim bVis as boolean

'code for bVis = True or False

Me.OLEObjects("ToggleButton1").Visible = bVis

Regards,
Peter T


"Jim at Eagle" wrote in
message
...
Trying to toggle visability of button based on value of named
range.
Private Sub Worksheet_SheetChange(ByVal Target As Range)
Target = sheet1.Range("d9")
ToggleButton1.Visible = True
Select Case salestax
Case 0.00001 To 100
ToggleButton1.Visible = True
Case Else
ToggleButton1.Visible = False
End Select
End Sub
Help Please
--
Jim at Eagle