I have a drop down list in one sheet1. the cell that the drop down is i
is named "Audit". The values that user selects is "Site" o
"Electronic". if user selects "Site" i want sheet3 to be visible. i
user selects electronic, i want sheet3 to be hidden.

so my question-
how do i code this that it will only run when that cell is changed?
(if it can only be done when sheet is changed that's fine because use
can only make a couple of changes on this sheet anyway but i dont kno
how to do this either)

i currently have this in the code of the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If (Audit = "Electronic") Then
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Visible = False
Else
Sheets("Sheet3").Visible = True
End If

End Sub

but it's not working. i assume i need some declarations, just not sur
what.

thanx in advance

--
tkapla
-----------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...fo&userid=2298
View this thread: http://www.excelforum.com/showthread.php?threadid=40041

Change you first line of code. Change A1 to suit.

If Range("A1").Value = "Electronic" Then

Note: Once Sheet 3 is not visible, selection automatically reverts to the
previous sheet (sheet 2 in my case). An alternative is to change your
second line.

Sheets("Sheet3").Visible = False

You don't need to select the sheet to hide it.

--
Ian
--
"tkaplan" wrote in
message ...

I have a drop down list in one sheet1. the cell that the drop down is in
is named "Audit". The values that user selects is "Site" or
"Electronic". if user selects "Site" i want sheet3 to be visible. if
user selects electronic, i want sheet3 to be hidden.

so my question-
how do i code this that it will only run when that cell is changed?
(if it can only be done when sheet is changed that's fine because user
can only make a couple of changes on this sheet anyway but i dont know
how to do this either)

i currently have this in the code of the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If (Audit = "Electronic") Then
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Visible = False
Else
Sheets("Sheet3").Visible = True
End If

End Sub

but it's not working. i assume i need some declarations, just not sure
what.

thanx in advance.


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile:
http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=400414

your code is evaluating for any cell change on the sheet
so first narrow it

if Target.address = Audit.address then (I would hard code the address not
name)
if target.value = "Electronic" then
thisworkbook.sheets("Sheet3").visible=false
else
thisworkbook.sheets("Sheet3").visible=true
end if
end if

without error code this will crash if there is no "sheet3" or if sheet3 is
the only visible sheet

"tkaplan" wrote:


I have a drop down list in one sheet1. the cell that the drop down is in
is named "Audit". The values that user selects is "Site" or
"Electronic". if user selects "Site" i want sheet3 to be visible. if
user selects electronic, i want sheet3 to be hidden.

so my question-
how do i code this that it will only run when that cell is changed?
(if it can only be done when sheet is changed that's fine because user
can only make a couple of changes on this sheet anyway but i dont know
how to do this either)

i currently have this in the code of the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If (Audit = "Electronic") Then
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Visible = False
Else
Sheets("Sheet3").Visible = True
End If

End Sub

but it's not working. i assume i need some declarations, just not sure
what.

thanx in advance.


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=400414