Making validation arrows INvisible
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Making validation arrows INvisible

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Making validation arrows INvisible

     
    Hi,

    I have a sheet where only a few cells can be altered. The sheet can be sorted and filtered by macro's. This works fine. But I do find the validation arrows confusing for the users. This part of the sheet is protected so they can't filter by the arrows (they need to use the macro's behind the buttons). Is there a way to make the arrows invisible ?


  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,068
    Post Thanks / Like
    Mentioned
    143 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Making validation arrows INvisible

    Something like
    Code:
    Sub HideFilter()
    Dim i As Long
    For i = 1 To 10
       Range("A1:J1").AutoFilter i, , , , xlHidden
    Next i
    Range("A1:J1").AutoFilter 2, "No"
    End Sub
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making validation arrows INvisible

    Hi,

    sorry for my ignorance but could you explain it a little more.

    The filtered headers are on row 7 from A till AG.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,854
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Making validation arrows INvisible

    Quote Originally Posted by Fluff View Post
    Something like
    Code:
    Sub HideFilter()
    Dim i As Long
    For i = 1 To 10
       Range("A1:J1").AutoFilter i, , , , xlHidden
    Next i
    Range("A1:J1").AutoFilter 2, "No"
    End Sub
    You can do that without using a loop. If your column range is fixed and unchanging, then this works...
    Code:
    Range("A1:J1").AutoFilter [COLUMN(A:J)], , , , False
    If the range is not "set in stone", then you could do something like this...
    Code:
    Dim Rng As Range Set Rng = Range("A1", Cells(1, Columns.Count).End(xlToLeft)) Rng.AutoFilter Evaluate("COLUMN(" & Rng.EntireColumn.Address(0, 0) & ")"), , , , False
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,068
    Post Thanks / Like
    Mentioned
    143 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Making validation arrows INvisible

    Ok
    Code:
    Sub HideFilter()
    Dim i As Long
    For i = 1 To 33
       Range("A7:AG7").AutoFilter i, , , , xlHidden
    Next i
    Range("A7:AG7").AutoFilter 2, "No"
    End Sub
    This will hide all the arrows & then filter col B on "No"
    The first part will not only remove the arrows, but also any active filter, so you will need to reset the filter afterwards
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making validation arrows INvisible

    oke, now (I think) I understand. I need to adjust the current filters to this format where the bottom part is the filter.

    Still need to se if I can pull this off, but TIA
    Last edited by perrush; Apr 15th, 2018 at 02:14 PM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,068
    Post Thanks / Like
    Mentioned
    143 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Making validation arrows INvisible

    If you have any problems, just post back.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,854
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Making validation arrows INvisible

      
    Quote Originally Posted by perrush View Post
    Hi,

    sorry for my ignorance but could you explain it a little more.

    The filtered headers are on row 7 from A till AG.
    Okay, if the columns are fixed at A:AG starting on Row 7, then executing this single line of code (either in its own macro or within some other procedure) will hide the arrows but leave the AutoFilter active...
    Code:
    Range("A7:AG7").AutoFilter [COLUMN(A:AG)], , , , False
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com