What do you want to be variable. I am sure this can be altered to
accomodate your needs.

For instance if you want to handle a range of cells

if not intersect(Target,Range("A1:A5,F3:G20,H3,H10:12")) Then

for a whole column

If Target.column = 4 then

and so forth.

--
Regards,
Tom Ogilvy


"Jim at Eagle" wrote in message
...
Thanks Tom
To bad it has to be absolute ref not variable.
I've learned a lot from you and couple others over the last few weeks.
I'ts not bad to get stuck when people like yourself are there and are
willing to push.
Thanks again.
Jim

"Tom Ogilvy" wrote:

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