http://www.officefans.net/cdb/viewthread.php tid=25823&fpage=1&highlight=???+?к?
以下是最近从不同地方收集的一些学习材料。我希望它能从新手那里学习。同时，感谢您的支持和帮助。先发60个小点，代码可能不完全正确。请纠正它.
全部显示
1,Application.CommandBars("Worksheet Menu Bar").Enabled = false
2,cells(activecell.row,"b").value B列单元格中活动单元格的值
3,Sub CheckSheet()如果目前的工作薄中没有名称kk在工作表中，添加一个名称kk工作表从左到右排列在最左边，即第一位
Dim shtSheet As Worksheet
For Each shtSheet In Sheets
If shtSheet.Name = "KK" Then Exit Sub
Next shtSheet
Set shtSheet = Sheets.Add(Before:=Sheets(1))
shtSheet.Name = "KK"
End Sub
4,Sheet1.ListBox1.List = Array("一月", "二月", "三月", "四月")一次性增加项目
5,Sheet2.Rows(1).Value = Sheet1.Rows(1).Value将一个表中的一行全部复制到另一个表中
6,Sub pro_cell()放入此代码sheet1,则me=sheet主要是认识me
Me.Unprotect
Cells.Locked = False
Range("D11:E11").Locked = True
Me.Protect
End Sub
7,Application.CommandBars("Ply").Enabled = False'工作表标签上快捷菜单失效
8,Sub aa()'把B1到B12单元格的数据填入c1到c12
For i = 1 To 12
Range("C" & i) = Range("B" & i)
Next i
End Sub
9,ActiveCell.AddComment
Selection.Font.Size = 12'在点选的单元格插入批注,字体为12号
10,Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub
11,ScrollArea 属性
参阅应用于示例特性以 A1 样式的区域引用形式返回或设置允许滚动的区域.用户不能选定滚动区域之外的单元格.String 类型,可读写.
说明
可将本属性设置为空字符串 ("") 以允许对整张工作表内所有单元格的选定.
示例
本示例设置第一张工作表的滚动区域.
Worksheets(1).ScrollArea = "a1:f10"
12\if application.max([a1:e1])=10 then
msgbox""
commandbutton1.enabled=false
'A1—E1最大的数值达到10时,自动弹出对话框,并冻结按钮
12,本示例将更改的单元格的颜色设为蓝色.
Private Sub Worksheet_Change(ByVal Target as Range)
Target.Font.ColorIndex = 5
End Sub
13,Sub test()'求和
Dim rng As Range, rng2 As Range
For Each rng In ActiveSheet.UsedRange.Columns
Set rng2 = Range(Cells(1, rng.Column), Cells(Cells(65536, rng.Column).End(xlUp).Row, rng.Column))
rng2.Cells(rng2.Cells.Count).Offset(1, 0) = WorksheetFunction.Sum(rng2)
Next rng
End Sub
14,将工作薄中的全部n张工作表都在sheet1中建上链接
Sub test2()
Dim Pt As Range
Dim i As Integer
With Sheet1
Set Pt = .Range("a1")
For i = 2 To ThisWorkbook.Worksheets.Count
.Hyperlinks.Add Anchor:=Pt, Address:="", SubAddress:=Worksheets(i).Name & "!A1"
Set Pt = Pt.Offset(1, 0)
Next i
End With
End Sub
15,保存所有打开的工作簿,然后退出 Microsoft Excel.
For Each w In Application.Workbooks
w.Save
Next w
Application.Quit
16,让form标题栏上的关闭按钮失效
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode 1 Then Cancel = True
End Sub
17,Sub countsh()'获得工作表的总数
MsgBox Sheets.Count
End Sub
18,Sub IE()'打开个人网页
ActiveWorkbook.FollowHyperlink "about:blank"
SendKeys "{F4}ykk1976.anyp.cn{ENTER}", True
End Sub
19,Sub delback()'一次性删除工作簿中所有工作表的背景
For Each shtSheet In Sheets
shtSheet.SetBackgroundPicture Filename:=""
Next shtSheet
End Sub
20,[a1].formula="=b1+c1"'A1中设定公式为=B1+C1
21,Private Sub CommandButton1_Click()'将A1到C6中大于=3的数依次放入E列
Dim i As Long
r = 1
For Each i In Range("a1:c6")
If i > =3 Then Cells(r, 5) = i: r = r + 1
Next
End Sub
22,Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)'显示带数字的表名
b = Split(Sh.Name, "(")
On Error GoTo ss
num = CInt(Left(b(1), Len(b(1)) - 1))
If num >= 1 And num < 20 Then
MsgBox Sh.Name
End If
Exit Sub
ss:
MsgBox "error", 16, ""
End Sub
23,Sub Test()'选择所有工作表名以"业报"开头的工作表或头两个字是业报的报表名引用
Set Sh = ActiveSheet
If Left(Sh.Name, 2) = "业报" Then ' 或if sh.name like"业报*"then
MsgBox "你成功了", 64, ""
End If
End Sub
24,1.建立文件夹的方法
MkDir "D:\Music"
2.打开文件夹的方法
ActiveWorkbook.FollowHyperlink Address:="D:\Music", NewWindow:=True
25,在当前工作表翻页
Application.SendKeys "{PGUP}", True
Application.SendKeys "{PGDN}", True
或者
ActiveWindow.LargeScroll Down:=1
ActiveWindow.LargeScroll Down:=-1
26,当Target = "*小计"时如何写,*代表任何字符.
if instr(target.value,"小计")0 then
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value Like "*小计" Then MsgBox "OK"
End Sub
27,ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[14]C,R[59]C:R[78]C)"
这是相对引用的写法:根据推算你的函数是放在"AD6"单元格
你的函数:=SUM(R[1]C:R[14]C 中的 "R"表示行 "C"表示列.
R[1]表示"AD6+1行",C表示"列没有变化,就是同列"那么:R[1]C就表示AD7
同理,R[14]表示AD6+14行,表示:AD20.以此类推.
28,Private Sub CommandButton1_Click()'将A1到C6中大于=3的数依次放入E列
Dim i As Long
Dim iRng As Range
For Each iRng In Sheets(1).Range("a1:c6")
If iRng.Value >= 3 Then
i = i + 1
Sheets(1).Range("E" & i).Value = iRng.Value
End If
Next
End Sub
29,工作表中的窗体按钮禁用后,按钮形状不变,字体不变,从外表上无法看出其已禁用,如何设置属性使其像控件按纽那样明显的禁用
With ActiveSheet.Buttons(1)
.Enabled = False
ActiveSheet.Shapes(.Caption).DrawingObject.Font.ColorIndex = 15
End With
复原的方法
With ActiveSheet.Buttons(1)
.Enabled = True
ActiveSheet.Shapes(.Caption).DrawingObject.Font.ColorIndex = xlAutomatic
End With
30,Private Sub Worksheet_SelectionChange(ByVal Target As Range'选定A1时要输入密码
If Target.Address = "$A$1" Then
A = InputBox("请输入密码", "officefans")
If A = 1 Then [A1].Select Else [A2].Select
End If
End Sub
31,如何将工作薄中的命名单元格成批删除!
Dim Item As Name
For Each Item In ActiveWorkbook.Names
Item.Delete
Next Item
32,平时只能看到表1,如要看表2和表3,只能通过表1的链接打开,且表2和表3回到表1后,又不可见.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$3" Then '当点击"$A$3"单元格时...
Sheet2.Visible = 1 '取消隐藏
Sheet2.Activate '激活
ActiveSheet.Range("A1").Select
End If
If Target.Address = "$A$6" Then
Sheet3.Visible = 1 '取消隐藏
Sheet3.Activate
ActiveSheet.Range("A1").Select
End If
End Sub
33,将a2单元格内容替换为a1内容
ActiveCell.Replace What:=[a2], Replacement:=[a1]
34,如果是要填入名称,则:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Value = ComboBox1.column(1)
End Sub
如果是要填入代码和名称的组合 :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Value = cstr(ComboBox1.column(0))+" "+combobox1.column(1)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Value = ComboBox1.Value
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'target.row 代表行号
'target.column 代表列号
i=target.row '获取行号
j=target.column '获取列号
End Sub
35,当激活工作表时,本示例对 A1:A10 区域进行排序.
Private Sub Worksheet_Activate()
Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending
End Sub
36,BeforePrint 事件
参阅应用于示例特性在打印指定工作簿(或者其中的任何内容)之前,产生此事件.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel 当事件产生时为 False.如果该事件过程将本参数设为 True,则当该过程运行结束之后不打印工作簿.
示例
本示例在打印之前对当前活动工作簿的所有工作表重新计算.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk in Worksheets
wk.Calculate
Next
End Sub
37,Open 事件
每次打开工作簿时,本示例都最大化 Microsoft Excel 窗口.
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub
38,ActiveSheet 属性
参阅应用于示例特性返回一对象,该对象代表活动工作簿中的,或者指定的窗口或工作簿中的活动工作表(最上面的工作表).只读.如果没有活动的工作表,则返回 Nothing.
说明
如果未给出对象识别符,本属性返回活动工作簿中的活动工作表.
<