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

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

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

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

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

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

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