Worksheet_Change event to unhide columns based on value in range

[Steve E]

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

 

[Jim Cone]

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

CodeFailure:
Application.EnableEvents = True
End Sub
'~~~~~~~~~

"Steve E" <[email protected]>
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

 

[Steve E]

Jim,

Thanks so much for looking at this with me.

That code works fine but I am trying to incorporate the "unhide rows R:T
when O18:O32 = "wordswrittenincell" " bit with the E1 = Now when there is a
change to the ("C18:X32,C37:H43") range.

The only thing that the two statements have in common is that they are both
worksheet_change events...

Am I really so dense that I can't make sense of this?

SE

 

[Jim Cone]

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

Dim N As Long
Dim rng As Excel.Range
Set rng = Me.Range("O18:O32")

Application.EnableEvents = False
Me.Unprotect (PWORD_Worksheet)
With Me.Range("E1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With

For N = 1 To rng.Count
If rng(N).Value = "wordsincell" Then
Me.Range("R:T").EntireColumn.Hidden = False
Exit For
End If
Next

Me.Protect (PWORD_Worksheet)
Application.EnableEvents = True
End If
End With
End Sub
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html




"Steve E" <[email protected]>
wrote in message
Jim,
Thanks so much for looking at this with me.
That code works fine but I am trying to incorporate the "unhide rows R:T
when O18:O32 = "wordswrittenincell" " bit with the E1 = Now when there is a
change to the ("C18:X32,C37:H43") range.
The only thing that the two statements have in common is that they are both
worksheet_change events...
Am I really so dense that I can't make sense of this?
SE

 

[Steve E]

Bingo.

Is there a good site where I can try and learn more about how to structure
multiple worksheet_change events like this? At a VBA for beginners level?

Many many thanks.

Steve

 

[Jim Cone]

Steve,

I think you have already found the site.
Chip Pearson has had some great explanatory posts in this group on
a number of programming subjects and you can always seem to learn
something if you follow Tom Ogilvy around.
Plus almost every contributor here seems to have some unique knowledge
that can widen one's scope.
Having a John Walkenbach book is an essential.

Jim Cone
San Francisco, USA


"Steve E" <[email protected]>
wrote in message
Bingo.
Is there a good site where I can try and learn more about how to structure
multiple worksheet_change events like this? At a VBA for beginners level?
Many many thanks.
Steve

 

[Steve E]

Jim,

Thanks again.
I feel like I need to learn the fundamentals better and reading EXCEL VBA
Programming for Dummies leaves me feeling dumber... I'll have to keep at it
though cuz this is one powerful tool (even when you only know what little I
know).
Regards,
Steve

 

[Jim Cone]

Steve,
Also, check these out.
http://www.contextures.on.ca/tiptech.html

http://www.cpearson.com/excel.htm

Jim Cone




"Steve E"
(e-mail address removed)>
wrote in message
Jim,
Thanks again.
I feel like I need to learn the fundamentals better and reading EXCEL VBA
Programming for Dummies leaves me feeling dumber... I'll have to keep at it
though cuz this is one powerful tool (even when you only know what little I
know).
Regards,
Steve