PDA

View Full Version : [SOLVED] Toggle Visibility of the Drop Down of A Specified Table Column



vanhunk
07-20-2016, 04:30 AM
Using Structured References, Toggle the Visibility of the Drop Down of a Specified Column in a Table:

I have a table called “myTable1” with 3 columns, “Line”, “Name”, and “Salary”.

I can hide the dropdown arrow of the column “Salary” with the following code:

With Range("myTable1[[#Headers],[Salary]]")
.AutoFilter , _

field:=.Column, _
Visibledropdown:=False
End With

and want to be able to have a toggle function. I.e. when I run the code if the dropdown is visible it must hide it and if it is hidden it must make it visible.

Kind Regards,
vanhunk

mdmackillop
07-20-2016, 04:58 AM
Untested but try

Visibledropdown:=Not Visibledropdown

vanhunk
07-20-2016, 05:25 AM
Thx mdmackillop, but unfortunately it doesn't work (pity).

mdmackillop
07-20-2016, 06:04 AM
Post a sample workbook

vanhunk
07-20-2016, 06:58 AM
mdmackillop, as requested.

Regards,
vanhunk

mdmackillop
07-21-2016, 12:36 AM
I cannot see a way to return the VisibleDropDown value. As a result, no toggle seems possible. Tried a google search and this looks like an old question with no result.

vanhunk
07-21-2016, 03:54 AM
mdmackillop, thank you so much for your effort.

It can be returned by changing "Visibledropdown:= False" to "Visibledropdown:=True"

If in some or other way it was possible to test if Visibledropdown equals False, or True, then an If statement or case statement could probably do the trick. I could however not find a way to do it.

Regards,
vanhunk

mdmackillop
07-21-2016, 04:51 AM
A workaround, using an ActiveX button

Private Sub CommandButton1_Click()
With Range("myTable1[[#Headers],[Salary]]")
If CommandButton1.Caption = "TRUE" Then
.AutoFilter , _
field:=.Column, _
Visibledropdown:=False
CommandButton1.Caption = "FALSE"
Else
.AutoFilter , _
field:=.Column, _
Visibledropdown:=True
CommandButton1.Caption = "TRUE"
End If
End With
End Sub

Zack Barresse
07-21-2016, 10:47 AM
This becomes difficult to toggle a single field because, as it stands right now, you can't read all filtered criteria. VBA falls short of being able to get filtered dates. Since we can only toggle a drop down visibility when setting a filter, we would have to know all current filters and re-apply them, specifying the fields you want to remove the drop downs from.

You can check if the filter for the table is on, even if a specific field is on (which is separate from it's visible state), but you can't tell which fields have the drop-down invisible. So unfortunately you can't use the table or it's autofilter method to accomplish what you want. I would recommend a workaround like mdmackillop posted.

snb
07-22-2016, 01:30 AM
Maybe this suits your purpose ?


Sub M_snb()
With Sheet1.ListObjects(1)
.ShowAutoFilter = Not .ShowAutoFilter
End With
End Sub

vanhunk
07-22-2016, 02:49 AM
Thank you guys,
@Zack/@mdmackillop:
I will go with this suggestion.

@snb:
Unfortunately your suggestion won't suit the purpose, but thank you anyway your suggestions are always helpful.

Best Regards,
vanhunk