| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change cell value by code
I am trying to get the text value in cell D5 to alternate from "show dates"
to "hide dates" depending whether cols A-C (where the dates are) are visible or not using the following: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 'right clicking D5 will show/hide columns A-C (date, time and user name)) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then Columns("A:C").EntireColumn.Hidden = Not Columns("A:C").EntireColumn.Hidden ==Target.Value = "hide dates": Target.Value = "show dates"<== this bit pls! Cancel = True End If I'm a bit stuck. Thanks -- Traa Dy Liooar Jock |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change cell value by code
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then If Columns("A:C").EntireColumn.Hidden = True Then Columns("A:C").EntireColumn.Hidden = False Target.Value = "show dates" Else Columns("A:C").EntireColumn.Hidden = True Target.Value = "hide dates" End If End If Cancel = True End Sub -- Regards! Stefi €˛Jock€¯ ezt Ć*rta: I am trying to get the text value in cell D5 to alternate from "show dates" to "hide dates" depending whether cols A-C (where the dates are) are visible or not using the following: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 'right clicking D5 will show/hide columns A-C (date, time and user name)) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then Columns("A:C").EntireColumn.Hidden = Not Columns("A:C").EntireColumn.Hidden ==Target.Value = "hide dates": Target.Value = "show dates"<== this bit pls! Cancel = True End If I'm a bit stuck. Thanks -- Traa Dy Liooar Jock |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change cell value by code
Your code worked ok for me (after I fixed the line wrap).
What problem are you having? Maybe you're not rightclicking on D5 (since it moves when A:C is shown/hidden). Jock wrote: I am trying to get the text value in cell D5 to alternate from "show dates" to "hide dates" depending whether cols A-C (where the dates are) are visible or not using the following: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 'right clicking D5 will show/hide columns A-C (date, time and user name)) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then Columns("A:C").EntireColumn.Hidden = Not Columns("A:C").EntireColumn.Hidden ==Target.Value = "hide dates": Target.Value = "show dates"<== this bit pls! Cancel = True End If I'm a bit stuck. Thanks -- Traa Dy Liooar Jock -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change cell value by code
Oops. I missed the part about how you wanted the text to alternate.
Sorry. Dave Peterson wrote: Your code worked ok for me (after I fixed the line wrap). What problem are you having? Maybe you're not rightclicking on D5 (since it moves when A:C is shown/hidden). Jock wrote: I am trying to get the text value in cell D5 to alternate from "show dates" to "hide dates" depending whether cols A-C (where the dates are) are visible or not using the following: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 'right clicking D5 will show/hide columns A-C (date, time and user name)) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then Columns("A:C").EntireColumn.Hidden = Not Columns("A:C").EntireColumn.Hidden ==Target.Value = "hide dates": Target.Value = "show dates"<== this bit pls! Cancel = True End If I'm a bit stuck. Thanks -- Traa Dy Liooar Jock -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change cell value by code
Works a treat, thanks.
-- Traa Dy Liooar Jock "Stefi" wrote: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then If Columns("A:C").EntireColumn.Hidden = True Then Columns("A:C").EntireColumn.Hidden = False Target.Value = "show dates" Else Columns("A:C").EntireColumn.Hidden = True Target.Value = "hide dates" End If End If Cancel = True End Sub -- Regards! Stefi €˛Jock€¯ ezt Ć*rta: I am trying to get the text value in cell D5 to alternate from "show dates" to "hide dates" depending whether cols A-C (where the dates are) are visible or not using the following: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 'right clicking D5 will show/hide columns A-C (date, time and user name)) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then Columns("A:C").EntireColumn.Hidden = Not Columns("A:C").EntireColumn.Hidden ==Target.Value = "hide dates": Target.Value = "show dates"<== this bit pls! Cancel = True End If I'm a bit stuck. Thanks -- Traa Dy Liooar Jock |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change cell value by code
Here is another way to write your code...
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) If Target.Address(0, 0) = "D5" Then With Columns("A:C").EntireColumn .Hidden = Not .Hidden Target.Value = IIf(.Hidden, "show", "hide") & " dates" End With End If Cancel = True End Sub -- Rick (MVP - Excel) "Stefi" wrote in message ... Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then If Columns("A:C").EntireColumn.Hidden = True Then Columns("A:C").EntireColumn.Hidden = False Target.Value = "show dates" Else Columns("A:C").EntireColumn.Hidden = True Target.Value = "hide dates" End If End If Cancel = True End Sub -- Regards! Stefi €˛Jock€¯ ezt Ć*rta: I am trying to get the text value in cell D5 to alternate from "show dates" to "hide dates" depending whether cols A-C (where the dates are) are visible or not using the following: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 'right clicking D5 will show/hide columns A-C (date, time and user name)) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then Columns("A:C").EntireColumn.Hidden = Not Columns("A:C").EntireColumn.Hidden ==Target.Value = "hide dates": Target.Value = "show dates"<== this bit pls! Cancel = True End If I'm a bit stuck. Thanks -- Traa Dy Liooar Jock |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change cell value by code
You are welcome! Thanks for the feedback!
-- Regards! Stefi €˛Jock€¯ ezt Ć*rta: Works a treat, thanks. -- Traa Dy Liooar Jock "Stefi" wrote: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then If Columns("A:C").EntireColumn.Hidden = True Then Columns("A:C").EntireColumn.Hidden = False Target.Value = "show dates" Else Columns("A:C").EntireColumn.Hidden = True Target.Value = "hide dates" End If End If Cancel = True End Sub -- Regards! Stefi €˛Jock€¯ ezt Ć*rta: I am trying to get the text value in cell D5 to alternate from "show dates" to "hide dates" depending whether cols A-C (where the dates are) are visible or not using the following: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 'right clicking D5 will show/hide columns A-C (date, time and user name)) If Not Application.Intersect(Target, Range("D5")) Is Nothing Then Columns("A:C").EntireColumn.Hidden = Not Columns("A:C").EntireColumn.Hidden ==Target.Value = "hide dates": Target.Value = "show dates"<== this bit pls! Cancel = True End If I'm a bit stuck. Thanks -- Traa Dy Liooar Jock |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow VBA code to change a locked cell?? | Excel Programming | |||
| code to change color of negative cell | Excel Programming | |||
| How to Run VBA Code on cell or range change | Excel Programming | |||
| How to Run VBA Code on cell or range change | Excel Programming | |||
| run code after cell contents change | Excel Programming | |||
Linear Mode
