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

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

Usually that means you don't have a proper event declaration in the correct
module.

--
regards,
Tom Ogilvy

"Jim at Eagle" wrote in message
...
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

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

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

Earlier I didn't pay as close attention to your code as others, particularly
the Change vs SelectionChange. I'll try and make a better answer of this.

if Target.Address = "$D$9" then
I try to use named ranges incase the worksheet is changed I don't need to
go
into program and edit the references.

Depending on your overall needs -

If Target(1).Address = Range("MyName").Address Then
or
If Not Intersect(Target, Range("MyName")) Is Nothing Then

The (1) in Target(1) denotes the first or active cell in the changed range.
Don't forget Target could be more than one cell, eg if user has pasted data
to a multicell range. You may or may not want to include the (1) in either
of these methods.

If for any reason your named cell is deleted the code will fail. Eg if user
deletes the name in Ctrl-F3, or if the row or column that contained the
named cell is ever deleted. Also, because the code is in a Worksheet module
the code would fail if the Named cell is not on the same sheet. Unless you
are sure none of this will ever occur it would be an idea to trap for the
error and take appropriate action.

Dim rNamed As Range
On Error Resume Next
Set rNamed = Range("Myname")
If rNamed Is Nothing Then
MsgBox "MyName does not exist"

Else
do normal code

End If

Regards,
Peter T

"Tom Ogilvy" wrote:

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