MsgBox pop up when cell is selected

[Portucale]

Hi,

I have a Pivot Table which one of the Labels contains a long string, so to adjust the cell it would be up to the maximum of 255 characters, which wouldn't make an easy read. So I am looking if is possible to set via VBA or other means that if the user selects a cell within the label a Box would popup with the complete string within the cell.

Thanks in advance,

All the help and any help is very much appreciated,

 

[NARAYANK991]

Hi ,

Something like this :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            Dim PT As PivotTable
            On Error Resume Next
            Set PT = Target.Cells(1, 1).PivotTable
            On Error GoTo 0
            If Not (PT Is Nothing) Then
               If Target <> vbNullString Then MsgBox Target
            End If
End Sub

Narayan

 

[Portucale]

Hi ,

Something like this :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            Dim PT As PivotTable
            On Error Resume Next
            Set PT = Target.Cells(1, 1).PivotTable
            On Error GoTo 0
            If Not (PT Is Nothing) Then
               If Target <> vbNullString Then MsgBox Target
            End If
End Sub

Narayan

This is perfect, but can I go a bit further and have the MsgBox enabled just for one Label which is in column CB?
Thanks

 

[NARAYANK991]

Hi ,

Do you know which cell it will be in , or should the macro work for any cell in column CB ?

Narayan

 

[Portucale]

Hi ,

Do you know which cell it will be in , or should the macro work for any cell in column CB ?

Narayan

Hi,

Should work for any cell from CB16:CB..., if possible

Thanks

 

[NARAYANK991]

Hi ,

One additional line is required :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            If Application.Intersect(Target, Range("CB16:CB200")) Is Nothing Then Exit Sub  ' Change the reference to suit
           
            Dim PT As PivotTable
            On Error Resume Next
            Set PT = Target.Cells(1, 1).PivotTable
            On Error GoTo 0
            If Not (PT Is Nothing) Then
              If Target <> vbNullString Then MsgBox Target
            End If
End Sub

Narayan

 

[Portucale]

Hi ,

One additional line is required :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            If Application.Intersect(Target, Range("CB16:CB200")) Is Nothing Then Exit Sub  ' Change the reference to suit
         
            Dim PT As PivotTable
            On Error Resume Next
            Set PT = Target.Cells(1, 1).PivotTable
            On Error GoTo 0
            If Not (PT Is Nothing) Then
              If Target <> vbNullString Then MsgBox Target
            End If
End Sub

Narayan

Thank you so much, it works like a dream. May I be a pain and be a bit more difficult, if I would add a second range, lets say B16:B200 how would I do that?

so would be B16:B200 & CB16:CB200

 

[NARAYANK991]

Hi ,

Just make the following change :

FROM the existing :

If Application.Intersect(Target, Range("CB16:CB200")) Is Nothing Then Exit Sub

TO :

If Application.Intersect(Target, Union(Range("B16:B200") , Range("CB16:CB200"))) Is Nothing Then Exit Sub

Narayan

 

[Portucale]

Hi ,

Just make the following change :

FROM the existing :

If Application.Intersect(Target, Range("CB16:CB200")) Is Nothing Then Exit Sub

TO :

If Application.Intersect(Target, Union(Range("B16:B200") , Range("CB16:CB200"))) Is Nothing Then Exit Sub

Narayan

Thank you so much, invaluable help.