Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event to unhide columns based on value in range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event to unhide columns based on value in ran
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event to unhide columns based on value in ran
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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event to unhide columns based on value in ran
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" wrote: 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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event to unhide columns based on value in ran
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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event to unhide columns based on value in ran
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" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change Event with Range Protection | Excel Discussion (Misc queries) | |||
Lock or Unlock Range of Cells on Worksheet_Change Event | Excel Worksheet Functions | |||
Checkbox to Hide/Unhide Columns Based on Value in Cell | Excel Programming | |||
Using Named Range in Worksheet_Change event | Excel Programming | |||
Hide or Unhide certain columns based on a cell value | Excel Programming |