不懂VBA也编程

山菊花

用过的人都知道，VBA可以轻松地解决许多问题。
如果你不懂VBA，而且不想去学习它。我可以教给你一些方法，你只需按部就班地操作，无需理解它们是什么意思，最终目标，能用就行。
1、在Excel中玩转邮件合并 (2楼)
2、高亮显示当前行 (5楼)
3、自动输入当前日期 (16楼)
4、自动录入一个“√” (32楼)
5、自动保护录入的数据 (34楼)
6、属性框，从今以后喜欢你。(58楼)
7、保护所有工作表(86楼)
8、为工作簿创建目录(127楼)
9、插入工作表(141楼)
10、Excel2007中如何设置批注的形状?(431楼)
11、滚动条乖乖
12、自动打开“自定义筛选”对话框
13、测试工作表中公式运算的时间
14、用单元格组代替选项按钮组
15、单元格中插入系列图片
16、打印表头和选定区域
17、隐藏/取消隐藏自定义名称
18、给工作表中插入的图片改名字
19、突出显示被引用的单元格
20、如何高亮显示本周输入数据？
21、复制工作簿链接源信息
22、批量插入滚动条
23、高亮显示引用单元格
24、自动打开记录单
25、输入数据时进行信息检查
26、编辑数据时弹出一个提示
27、怎样实现自动筛选
  
隐藏跟帖

   　　

山菊花

打印准考证、成绩报告单、商品标签、入场券等，常常会使用Word的邮件合并工具。在Excel中批量打印的功能也不错，方法见附件：
xiabaoyuan_在线扳手、枪校验记录表单.zip (86.71 KB, 下载次数: 8311)

2008-11-18 12:52 上传

点击文件名下载附件

附件来自帖子：http://club.excelhome.net/viewthread.php?tid=369570

练习：

附件：下载
帖子：http://club.excelhome.net/thread-371082-1-1.html

1、选择N6单元格输入“当前行号”。
2、编写公式：
    J3=INDEX(Sheet2!B:B,N6)
    B4=INDEX(Sheet2!A:A,N6)
    F4=INDEX(Sheet2!C:C,N6)
    ……
3、从控件工具箱中添加一按钮到工作表，编辑标签文字，双击按钮，将前面文件中的代码复制到此：

1. Private Sub CommandButton1_Click()
   
2.     For i = [N7] To [N8]
   
3.     [N6] = i
   
4.     Me.PrintOut
   
5.     Next
   
6. End Sub

复制代码

将3个单元格名称修改为[N6]、[N7]、[N8]。
其中N7单元格输入“开始行号”、N8单元格输入“结束行号”。
4、添加窗体工具箱中的微调按钮到工作表，将链接单元格设为N6。
5、点击控件工具箱中的“退出设计模式”按钮，测试。

山菊花

用条件格式设置高亮显示当前行，难的是如何确定当前行。用VBA就很简单，鼠标右击工作表标签，选择“查看代码”，将下面的代码粘贴到VBE窗口中：

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2. ThisWorkbook.Names.Add "XM", Target
   
3. End Sub

复制代码

这几行代码的作用是，当选择新的单元格时，将这个单元格(或者区域)定义为名称“XM”，在条件格式设置中可以引用这个名称。

如下面的附件，将代码粘贴完成后，返回工作表中，选择A4:I15，点击菜单“格式—条件格式”，设置：
公式1：=(A4<>"")*(A4=XM)
(如果A4不为空，并且A4等于XM)
公式2：=ROW()=ROW(XM)
(如果当前的行号等于XM的行号)
考场安排.zip (8.93 KB, 下载次数: 3641)

2008-11-18 15:42 上传

点击文件名下载附件

练习：
试为附件中的考勤表设置条件格式，高亮显示当前行和列。
考勤表.zip (10.86 KB, 下载次数: 2611)

2008-11-18 16:03 上传

点击文件名下载附件

原帖由 xpm130 于 2008-11-19 21:02 发表 (23楼)
山版好,请您讲讲2楼高亮显示单元行中利用下面代码
ThisWorkbook.Names.Add "XM", Target定义名称的方法与结果与在工作表单元区域进行定义有什么不同之处好吗.我查看起来有点迷糊.
还有,当选中B6时出现如下情形,好像 ...

用代码定义名称，我们需要它的即时更新，鼠标点到什么，这个名称“XM”就更新什么。
例如，当鼠标点击F7单元格，XM便等于F7，ROW(XM)=7，设置条件格式的区域中，凡是Row()等于7(说白了就是第7行)的单元格便显示背景色。
用手工定义也可得到相同的效果，可以试试。然后比较一下，用代码的好处是什么。

当选中B6单元格时有不一样的情况出现，是因为区域中设置了两个条件格式，它符合第一个条件便显示第一种格式，符合第二个条件便显示第二个格式。
点击菜单“格式—条件格式”可看清楚。

原帖由 pxjxdsj 于 2008-12-1 14:53 发表 (98楼)


初学有一个问题？能否把 高亮显示单元格 做成 自动起动的宏，让用户的鼠标单击工作表的某一个单元格后，相应的行和列都显示一种 或者 两种背景色！

直接用VBA设置背景色，做法如下：
鼠标右键点击工作表标签，选择“查看代码”，将下面代码粘贴到光标处：

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2. Dim Rng As Range
   
3. Set Rng = Target.Range("a1")
   
4. Cells.Interior.ColorIndex = 0  '清除所有背景色
   
5. Rng.EntireColumn.Interior.ColorIndex = 40  '设置当前列颜色
   
6. Rng.EntireRow.Interior.ColorIndex = 36  '设置当前行颜色
   
7. End Sub

复制代码

其中40、36为颜色索引号，不同的数值代表不同的颜色，对应索引如下：

示例： 高亮显示当前行列.zip (8.29 KB, 下载次数: 1926)

2008-12-6 09:46 上传

点击文件名下载附件

原帖由 zjyhljx 于 2009-2-16 17:39 发表 (259楼)
如果工作簿中的每张工作表都有相同效果，应该怎么做？

按Alt+F11打开VBE编辑器，在工程管理器中双击模块“ThisWorkbook”，将下面的代码粘贴到光标处：

1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
2. Dim Rng As Range
   
3. Set Rng = Target.Range("a1")
   
4. Cells.Interior.ColorIndex = 0  '清除所有背景色
   
5. Rng.EntireColumn.Interior.ColorIndex = 40  '设置当前列颜色
   
6. Rng.EntireRow.Interior.ColorIndex = 36  '设置当前行颜色
   
7. End Sub
   

复制代码

如下图所示：

为所有工作表设置高亮背景色.zip (6.64 KB, 下载次数: 1085)

2009-2-17 08:47 上传

点击文件名下载附件

原帖由 Magic_sh 于 2009-2-20 12:58 发表 (274楼)
山菊花楼主，你好！
关于之前有人问到的关于高亮显示行和列的问题，你提供的VBA代码如下，但我现在碰到点问题，发现使用了这个代码后，表中的“复制”和“拷贝”功能就被禁止了，不知有无办法可以解决？谢谢！

可在代码第二行(清除颜色之前就行)插入一行代码：
If Application.CutCopyMode Then Exit Sub ’如果处于选取状态则退出程序

1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
2. If Application.CutCopyMode Then Exit Sub '如果处于选取状态则退出程序
   
3. Dim Rng As Range
   
4. Set Rng = Target.Range("a1")
   
5. Cells.Interior.ColorIndex = 0  '清除所有背景色
   
6. Rng.EntireColumn.Interior.ColorIndex = 40  '设置当前列颜色
   
7. Rng.EntireRow.Interior.ColorIndex = 36  '设置当前行颜色
   
8. End Sub

复制代码

山菊花

日期在记录单中是一个很重要的信息。为了减轻录入的负担，我们有时会希望Excel能自动录入当前日期。

右键点击工作表标签，选择“查看代码”，将下面的代码粘贴到光标处：

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Column = 1 Then Target.Offset(0, 2) = Date
   
3. End Sub

复制代码

中间一行是核心代码，意思是：当第1列(即A列)录入数据时，右边第2列(即C列)输入一个日期。
如果你的要求是“B列(第2列)输入数据后C列(B列的右边第1列)自动输入一个日期”，则中间一行代码应改为：
If Target.Column = 2 Then Target.Offset(0, 1) = Date

如果你要求“D列(第4列)输入数据后，A列(D列的左边3列)自动输入日期”，则中间一行代码改为：
If Target.Column = 4 Then Target.Offset(0, -3) = Date

如果要输入当前时间，怎么办？
代码中，Date 表示当前日期，你可以像吃自助餐一样，把 Date 换成合你口味的东西：
Time ：当前时间
Now ：当前日期时间


练习： 自动填写日期.zip (7.85 KB, 下载次数: 2001)

2008-11-19 15:29 上传

点击文件名下载附件

答案： 自动填写日期答案.zip (10.26 KB, 下载次数: 2334)

2008-11-19 15:29 上传

点击文件名下载附件

山菊花

录入过程中，为了反复录入符号“√”，让不少人心烦。
解决的办法可以使用数据有效性，也可以使用VBA，方法很简单。

假如I列要输入“是否三好学生”，如果是，录入一个“√”，因为第1、2行为标题行，从第3行开始，点击鼠标，显示一个“√”。
鼠标右键点击工作表标签，选择“查看代码”，将如下代码粘贴到光标处：

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     Dim Rng As Range
   
3.     Application.EnableEvents = False
   
4.    
   
5.     For Each Rng In Target
   
6.         If Rng.Row > 2 And Rng.Column = 9 Then '如果单元格行号大于2 并且 列号等于9 (I列)
   
7.             Rng = IIf(Rng = "√", "", "√") '如果这个单元格原来是"√"，改为空，否则，输入"√"
   
8.         End If
   
9.     Next
   
10.    
    
11.     Application.EnableEvents = True
    
12. End Sub

复制代码

返回工作表，即可使用该功能。
练习：
自动录入一个“√”.zip (35.74 KB, 下载次数: 1296)

2008-11-20 09:58 上传

点击文件名下载附件

答案： 自动录入一个“√”答案.zip (37.81 KB, 下载次数: 1746)

2008-11-20 09:58 上传

点击文件名下载附件

练习2： 一行中只能录入一个“√”.zip (8.6 KB, 下载次数: 909)

2008-11-20 12:19 上传

点击文件名下载附件

原帖由 ychh_wy 于 2008-11-24 13:16 发表 (77楼)
Ctrl+A容易引起工作表假死

不是“容易”，是“一定”。
如果要避免这种情况发生，可在程序的开头加入一个判断，如果选择整行或整列，退出程序：

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.    
   
3.     '如果选择的区域是整行或者整列，不运行下面的程序，直接退出。
   
4.     If Target.Rows.Count = Me.Cells.Rows.Count Or _
   
5.         Target.Columns.Count = Me.Cells.Columns.Count Then Exit Sub
   
6.         
   
7.         
   
8.     Dim Rng As Range
   
9.     Application.EnableEvents = False
   
10.    
    
11.     For Each Rng In Target
    
12.         If Rng.Row > 2 And Rng.Column = 9 Then
    
13.             Rng = IIf(Rng = "√", "", "√")
    
14.         End If
    
15.     Next
    
16.    
    
17.     Application.EnableEvents = True
    
18. End Sub

复制代码

实例：http://club.excelhome.net/viewthread.php?tid=378366

山菊花

如果希望录入的数据能够被保护，不被删改。可用程序自动将单元格锁定。
首先，选择单元格区域，按 Ctrl+1 ，打开单元格格式对话框，取消单元格的“锁定”。
然后右键点击工作表标签，选择“查看代码”，将代码粘贴在光标处：

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim Rng As Range
   
3.     Me.Unprotect ("123") '撤销工作表保护
   
4.    
   
5.     For Each Rng In Target
   
6.         Rng.Locked = True '锁定单元格
   
7.     Next
   
8.    
   
9.     Me.Protect ("123") '保护工作表
   
10. End Sub

复制代码

代码中的"123“是工作表保护密码，可自由修改。

答案：
自动保护录入的数据.zip (39.97 KB, 下载次数: 2198)

2008-11-20 15:06 上传

点击文件名下载附件

原帖由 eric0712 于 2008-12-5 15:32 发表 (114楼)

能否保护的同时取消“选择锁定单元格”？

可以在“Me.Protect ("123") '保护工作表”前面加入一行：
Me.EnableSelection = 1

也可先手工设置一次工作表保护，取消该选项，这样代码更简洁。

原帖由 kw116 于 2009-2-4 17:11 发表 (242楼)


山版。问下这个保护录入的数据可以在保护的选项中添加准许设置单元格格式这个选项吗？
我的希望就是表中数据即使被保护也可以更改字体颜色
谢谢

取消单元格锁定以后，可手工设置一次保护工作表，注意选择“设置单元格格式”选项。
或者修改以上代码，将：
“Me.Protect ("123") '保护工作表”一句修改为：
“Me.Protect Password:="123", AllowFormattingCells:=True '保护工作表”
在VBE编辑窗口中，将光标定位到“Protect”中间，按F1，可查看帮助。

山菊花

也许您从不知道Excel有个“属性框”，也许您正在像阿拉丁一样，正在考虑如何擦亮这盏神灯。
如果您已熟悉这一切，请回避，别让我脸红。
我们先来认识一下“属性框”，点击菜单“视图—工具栏—控件工具箱”，点击“属性”按钮，如下图所示。


对“工具—选项”不陌生吧？“选项”是中文版的“属性框”，“属性框”是英文版的“选项”，通过“选项”与“属性框”都用于取得工作表的某方面信息或对工作表的某方面进行控制。
属性框分左右两栏，左边是属性名，右边是对应的值。
如下面的Name是属性名，“通讯录”是它的值。即这个工作表的名字(标签)为“通讯录”，同时也可以在这里修改它。


上面见到的是工作表的“属性”，不同的对象有不同的属性，从控件工具箱中添加一按钮到工作表，选择它，打开属性框，显示如下：


这些属性对我们有什么作用呢？下面拣几个让大家认识认识。
StandardWidth是工作表中所有列的标准列宽，我们可以试一试，修改它，光标点击StandardWidth右侧的文本框，输入一个值，如16，回车，立即可看到结果。

山菊花

ScrollArea属性：返回或设置允许滚动的区域。
工作表属性_编辑区域.zip (9.8 KB, 下载次数: 1599)

2008-11-21 23:50 上传

点击文件名下载附件

EnableCalculation属性：是否重新计算工作表。
工作表属性_重算模式.zip (191.33 KB, 下载次数: 1550)

2008-11-23 22:51 上传

点击文件名下载附件

利用Name属性用单元格的内容命名工作表：
工作表属性_用单元格内容更改工作表标签.zip (6.7 KB, 下载次数: 1429)

2008-11-21 23:57 上传

点击文件名下载附件

使用控件制作多级下拉菜单(控件主要属性介绍)
见79楼。

山菊花

制作多级下拉菜单，数据有效性应该是最好的选择。
为了配合界面的整体效果，使用控件也是可行的，它的制作方法也很简单。


控件是放置于工作表或窗体上的一些图形对象，能起到较好的装饰作用，当然，显示数据和输入数据是控件的主要用途。
控件有两类，分别从“窗体”和“控件工具箱”中添加。
点击菜单“视图—工具栏”，分别打开“窗体”和“控件工具箱”，比较两类控件，真有点分不清谁是谁的感觉。
简言之，“窗体”控件功能单一，但占用资源较小，“控件工具箱”控件则与之相反。实际中，应根据需要选择使用。
两者的比较，可参考帖子：
羊和披着羊皮的狼—闲话控件与图形

言归正传，利用控件制作多级下拉菜单，步骤如下：

一、使用窗体控件制作下拉菜单
1、准备基本数据(“省市”、“市县”两表)               
               
2、规划三个单元格(S1:U1)，用于保存三个控件选择结果。               
   窗体控件(组合框、列表框)的返回值是一个数值，表示选取的项在列表中的序号。               
               
3、设置两个辅助列，根据选择的省份，生成对应的市名列表(T2:T22)。               
  另一个是根据选择的市名，生成对应的县名列表(U2:U27)。               
               
4、按 Ctrl+F3 打开定义名称对话框，将S、T、U三列数据分别定义为名称“省”、“市”、“县”。               
               
5、添加和设置窗体控件。               
               
        ①、从窗体工具栏中选择“组合框”和“列表框”，分别添加到工作表中。       
        ②、右键点击控件，选择菜单项“设置控件格式”。       
        ③、根据不同用途的控件，设置其“数据源区域”和“单元格链接”。


二、使用控件工具箱控件制作下拉菜单

前面的数据准备、辅助列制作、定义名称都与前面介绍的方法相同。
不同的是插入控件后，不是使用“设置控件格式”，而是使用“属性”框。
控件的“ListFillRange”属性用于设置“数据源区域”。
控件的“LinkedCell”属性用于设置“单元格链接”。
(详细见附件)

山菊花

不少人尝试过给工作组设定工作表保护，但是，没能成功，而且，总不甘心。
好在，用VBA处理起来不算麻烦。
右键点击任一工作表标签，将下面的代码粘贴到光标处：

1. Sub 保护()
   
2. Dim Sh As Worksheet
   
3. For Each Sh In Worksheets
   
4. Sh.Protect
   
5. Next
   
6. MsgBox "OK"
   
7. End Sub

复制代码

将光标定位到该代码的任意位置，按F5运行该代码。

如果要给工作表保护设置密码，可在 Protect 后面写入密码，用空格分开，密码前后加上半角双引号，假如密码为123：
Sh.Protect "123"

如果要批量撤销工作表保护，可将命令Protect改为Unprotect，如果有密码，同样在后面写入密码，如：

1. Sub 撤销保护()
   
2. Dim Sh As Worksheet
   
3. For Each Sh In Worksheets
   
4. Sh.Unprotect "123"
   
5. Next
   
6. MsgBox "OK"
   
7. End Sub

复制代码

我们会注意到，Excel基本操作中，用同一个菜单项或命令按钮执行“保护”和“撤销保护”两个操作。我们也可以把上面两段代码合并为一段代码：

1. Sub 保护全部工作表()
   
2.     Dim pc As Boolean, cMM$, Sh As Worksheet
   
3.     pc = Me.ProtectContents
   
4.     On Error Resume Next
   
5.     cMM = Application.InputBox("请输入" & IIf(pc, "撤销", "") & "工作表保护密码：", "密码", Type:=2)
   
6.     If cMM <> "123" Then Exit Sub '输入的密码是123
   
7.     Application.ScreenUpdating = False
   
8.     For Each Sh In Worksheets
   
9.         If pc Then
   
10.             Sh.Unprotect ("xx") '实际工作表保护密码是xx
    
11.         Else
    
12.             Sh.Protect ("xx")
    
13.         End If
    
14.     Next
    
15.     Application.ScreenUpdating = True
    
16. End Sub

复制代码

参考附件：点击下载