I almost have it - excel hidden - see message

[Nils]

With attached code - how do I add if cell a1 equals "x"
then run code? I can get it to run the column hidden
function with any input, but I would like to be able to
have specific. Also, Can I set keycells to be several
cells in different locations on worksheet/
Thanks a lot for your help Jim!

Nils

==============================================




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("A1:a1")

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.
MsgBox "Cell " & Target.Address & " has changed."

'
' hidden2 Macro
' Macro recorded 10/28/2003 by Nils
'
Columns("I:L").Select
Selection.EntireColumn.hidden = True

End If
End Sub

 

[J.E. McGimpsey]

one way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A1, B2, C3, D4")
If Not Intersect(Target, KeyCells) Is Nothing Then
If LCase(Range("A1")) = "x" Then
MsgBox "Cell " & Target.Address & " has changed."
Columns("I:L").Hidden = True
End If
End If
End Sub

will fire when A1, B2, C3, or D4 are changed, but will only display
the msgbox and hide the columns if A1="x" or "X".

Note that no selections are needed - you can refer to the range
objects directly.

 

[Bob Phillips]

Nils,

On the worksheet, select all of the key cells, and then create a named range
(I use KeyCells as the name), by going into Insert>Name>Define, and input
the name.

This code then does as you want

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("KeyCells"), Target) Is Nothing Then

If Target.Value = "x" Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
MsgBox "Cell " & Target.Address & " has changed."

'
' hidden2 Macro
' Macro recorded 10/28/2003 by Nils
'
Columns("I:L").Select
Selection.EntireColumn.Hidden = True
End If

End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)