vba excel排序_Excel VBA单击“形状以对列进行排序”

People are lazy! Shocking, I know, but who wants to click twice in Excel, if you can do the same thing by only clicking once?

人们很懒！ 我知道令人震惊，但是如果您只单击一次就可以做同样的事情，谁想在Excel中单击两次？

Dave Peterson, champion of weary Excel users, created this sample Excel VBA sort code, that adds invisible rectangles at the top of each column in a table.

疲惫的Excel用户的拥护者Dave Peterson创建了此示例Excel VBA排序代码 ，该代码在表格中每列的顶部添加了不可见的矩形。

A macro is automatically assigned to each rectangle, and it sorts the table by that column, when you click it.

宏会自动分配给每个矩形，当您单击它时，它将按该列对表格进行排序。

Two benefits of using Dave's code:

使用Dave的代码有两个好处：

1. Reduced wear and tear on clicking fingers
   减少点击手指的磨损
2. Less risk of table scrambling, because it ensures the entire table is selected before sorting
   减少表加扰的风险，因为它可以确保在排序之前选择了整个表

编辑设置宏 (Edit the Setup Macro)

There are two macros in Dave's sample file.

Dave的示例文件中有两个宏。

• SetupOneTime - run this once, to add the hidden rectangles
  SetupOneTime-运行一次，以添加隐藏的矩形
• SortTable - sorts table by selected column, when heading is clicked
  SortTable-单击标题时，按选定的列对表进行排序

Before you run the SetupOneTime macro, you should edit both macros, to adjust them for your workbook

在运行SetupOneTime宏之前，应编辑两个宏，以针对工作簿进行调整。

1. On the Excel Ribbon, click the Developer tab, then click Macros
   在Excel功能区上，单击“开发人员”选项卡，然后单击“宏”
2. Click SetupOneTime, and click Edit
   单击SetupOneTime，然后单击编辑

In the SetupOneTime macro, change the iCol variable to match the number of columns in your table. If your table doesn't start in cell A1, change that reference.

在SetupOneTime宏中，更改iCol变量以匹配表中的列数。 如果您的表不在单元格A1中开始，请更改该引用。

编辑SortTable宏 (Edit the SortTable Macro)

Next, change the variables in the SortTable macro, to suit your table settings. You can adjust:

接下来，更改SortTable宏中的变量以适合您的表设置。 您可以调整：

• TopRow (row where headings are located)
  TopRow(标题所在的行)
• iCol (number of columns in the table)
  iCol(表中的列数)
• strCol (column to check for last row)
  strCol(要检查最后一行的列)

If you want to see the rectangle outlines, change the Line.Visible setting to True.

如果要查看矩形轮廓，请将Line.Visible设置更改为True。

运行SetupOneTime宏 (Run the SetupOneTime Macro)

After you've edited the macros, you can run the setup macro:

编辑宏后，可以运行setup宏：

1. Select the sheet where your table is located.
   选择表所在的表。
2. On the Excel Ribbon, click the Developer tab, then click Macros
   在Excel功能区上，单击“开发人员”选项卡，然后单击“宏”
3. Click SetupOneTime, and click Run
   单击SetupOneTime，然后单击运行

Now, click a heading in the table, to sort by that column.

现在，单击表中的标题以按该列排序。

Excel 2007形状问题 (Excel 2007 Shapes Problem)

When I was getting this blog post ready, I discovered that Dave's original code needed a tweak before it would work correctly in Excel 2007 and Excel 2010.

当我准备好这篇博客文章时，我发现Dave的原始代码需要进行调整，然后才能在Excel 2007和Excel 2010中正常运行。

In the original code, written for Excel 2003, there was one line of code that made the rectangular shape invisible:

在为Excel 2003编写的原始代码中，有一行代码使矩形不可见：

.Fill.Visible = False

In the newer versions of Excel, only the borders of the invisible shapes were clickable, so I had to change the code to these two lines:

在较新版本的Excel中，仅可单击不可见形状的边框，因此我不得不将代码更改为以下两行：

.Fill.Solid
.Fill.Transparency = 1#

The revised code worked for me in Excel 2003, 2007 and 2010, creating transparent shapes that were clickable.

修改后的代码在Excel 2003、2007和2010中为我工作，创建了可单击的透明形状。

下载样本工作簿 (Download the Sample Workbook)

To see the full code for the SetupOneTime and SortTable macros, and download the sample workbook, visit the Excel VBA: Sort Data With Invisible Rectangles page on the Contextures website.

若要查看SetupOneTime和SortTable宏的完整代码，并下载示例工作簿，请访问Contextures网站上的Excel VBA：“用不可见的矩形排序数据”页。

观看单击标题以对列进行排序视频 (Watch the Click Headings to Sort Columns Video)

To see the steps for editing the code, adding the rectangles, and clicking the hidden shapes, you can watch this short Excel tutorial video.

要查看编​​辑代码，添加矩形和单击隐藏的形状的步骤，您可以观看这段简短的Excel教程视频。

演示地址