View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Jim Cone Jim Cone is offline
external usenet poster
 
Posts: 3,290
Default Worksheet_Change event to unhide columns based on value in range

Steve,
I am uncertain as to exactly what your rules are, but see if this is close.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo CodeFailure
If Not Intersect(Me.Range("O18:O32"), Target(1)) Is Nothing Then
If Target.Value = "wordswrittenincell" Then
Application.EnableEvents = False
Me.Unprotect ("PWORD_Worksheet")
Me.Range("R:T").EntireColumn.Hidden = False
With Me.Range("E1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Me.Protect ("PWORD_Worksheet")
Application.EnableEvents = True
End If
End If
Exit Sub

CodeFailu
Application.EnableEvents = True
End Sub
'~~~~~~~~~

"Steve E"
wrote in message
Hi,

I'm trying to add another IF statement into my worksheet change code that
will unhide columns "R:R to T:T" if a value in the range O18:O32 =
"wordswrittenincell" but do nothing if no values in that range = "samewords"
I am wanting the columns to unhide as soon as someone enters the magic word
within that range...
I have the following already in my sheet code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:X32,C37:H43"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect (PWORD_Worksheet)
With Me.Range("E1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Me.Protect (PWORD_Worksheet)
Application.EnableEvents = True
End If
End With
End Sub

Can anyone help me with this?
TIA,
Steve