none
VBA - Show Custom Views on Intersect Selection Change RRS feed

  • Question

  • Hi friends,

     

    a good Friday to everyone.

     

    I have got stuck on trying to show Custom Views from a list.

     

     

     

     
        Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    
          Dim oCustomView As Excel.CustomView
        
           On Error Resume Next
           If Not Intersect(ActiveCell, Range("B2:B10")) Is Nothing Then
           oCustomView = ActiveCell.Name
    
            'ActiveWorkbook.CustomViews("test1").Show
    
            ThisWorkbook.Sheets("CustomViewList").Name(oCustomView).Show
            
            End If
            End Sub
            

     

    I'm pretty sure it may be something  minor - but I just can't spot the error, please enlighten

     

    Thank you


    Cheers Dan :)

    Friday, November 18, 2016 12:26 PM

Answers

  • Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
      Dim oCustomView As Excel.CustomView
      'Be sure
      On Error GoTo Exitpoint
      'Do nothing if more then one cell is selected
      If Target.Count > 1 Then Exit Sub
      'Inside our names?
      If Not Intersect(Target, Range("B2:B10")) Is Nothing Then
        'Get the view
        Set oCustomView = ThisWorkbook.CustomViews(Target.Value)
        'Show it
        oCustomView.Show
      End If
    Exitpoint:
    End Sub


    • Edited by Andreas Killer Saturday, November 19, 2016 9:42 AM
    • Marked as answer by Dan_CS Saturday, November 19, 2016 10:46 AM
    Friday, November 18, 2016 4:06 PM

All replies

  • Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
      Dim oCustomView As Excel.CustomView
      'Be sure
      On Error GoTo Exitpoint
      'Do nothing if more then one cell is selected
      If Target.Count > 1 Then Exit Sub
      'Inside our names?
      If Not Intersect(Target, Range("B2:B10")) Is Nothing Then
        'Get the view
        Set oCustomView = ThisWorkbook.CustomViews(Target.Value)
        'Show it
        oCustomView.Show
      End If
    Exitpoint:
    End Sub


    • Edited by Andreas Killer Saturday, November 19, 2016 9:42 AM
    • Marked as answer by Dan_CS Saturday, November 19, 2016 10:46 AM
    Friday, November 18, 2016 4:06 PM
  • Hello Andreas,

     

    Nice to see you.

    thank you for helping :)

    I clicked on Cell B2 to show the first Custom View but nothing showed up, I pasted in the code as above.


    Cheers Dan :)

    Friday, November 18, 2016 4:27 PM
  • I clicked on Cell B2 to show the first Custom View but nothing showed up

    Sorry, my fault, the CustomViews collection needs a value instead of a Range object as argument.

    I've changed the code above (change marked bold), please try again.
    I've tested the code, if the custom view is not shown means it doesn't exists in that file.

    Andreas.

    Saturday, November 19, 2016 9:46 AM
  •  

    Hello Andreas,

     

    Yay it works now!

     

    I have been looking for this Target issue since yesterday everywhere.

     

    But there is hardly any good documentation or examples especially with Custom Views.

     

    I am glad you spotted that -

     

    Thank you very much and have a great weekend!

                                             :)


    Cheers Dan :)

    Saturday, November 19, 2016 10:46 AM