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

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

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

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

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

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" <(E-Mail Removed)> wrote in message
news:11EECC1D-3E7A-45DA-B739-(E-Mail Removed)...
> 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

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