取得最大的列号 Range("A1").End(xlToRight).Column 最后一个非空行号 [A65536].end(xlup).row 禁用点击右上角的关闭按钮 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode <> 1 Then Cancel = True End Sub 用*代替登录密码,使密码不可见 Private Sub TextBox2_Change() TextBox2.PasswordChar = "*" End Sub 删除空行 Range("A2:A" & [A65536].End(xlUp).Row).SpecialCells(4).EntireRow.Delete 按A列排序 Cells.sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess 限制对话框内字数 If Len(TextBox2.Value) = 4 Or Len(TextBox2.Value) = 0 Then Else TextBox2.SetFocus '将光标定位于录入窗体 MsgBox ("输入字符数不正确") End If 禁止数据重复输入 Dim k As Range, i% With Sheet1 For Each k In Sheet1.Range("B2:B" & Sheet1.[A65536].End(xlUp).Row) If k.Value = TextBox3.Text Then MsgBox ("员工编号不允许重复") TextBox3.SetFocus Else End If Next End With 窗口向上向下按多少行滚动。 Private Sub CommandButton1_Click() ActiveWindow.SmallScroll Down:=-26 End Sub Private Sub CommandButton2_Click() ActiveWindow.SmallScroll Down:=26 End Sub 金额大写的公式改成: SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(E14)),"[>0][dbnum2];[<0]负 [dbnum2];;")&TEXT(RIGHT(FIXED(E14),2),"元[dbnum2]0角0分;;"&IF(ABS(E14)>1%,"元整",)),"零角",IF(ABS(E14)<1,,"零")),"零分","整") 即可在角后加整。 A列末行向上第一个有值的行数 [A65536].End(xlUp).Row A列首行向下第一个有值之行数 [A1].End(xlDown).Row 第一行末列向左第一列有数值之列数 [IV1].End(xlToLeft).Column 第一行首列向右有连续值的末列之列数 [A1].End(xlToRight).Column 如果当前单元格中的字符数超过5个,执行下一代码 If Len(Target) > 5 Then 有空格即隐藏行 [A1:B10].SpecialCells(xlCellTypeBlanks).Rows.Hidden = True 增加.ListBox项目 Sheet1.ListBox1.List = Array("上海", "北京", "南京", "广州") 把B1到B12单元格的数据填入c1到c12 Sub aa() For i = 1 To 12 Range("C" & i) = Range("B" & i) Next i End Sub A1—E1最大的数值达到10时,自动弹出对话框,并冻结按钮 Worksheets(1).ScrollArea = "a1:f10" 12\if application.max([a1:e1])=10 then msgbox" " commandbutton1.enabled=false 将工作薄中的全部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
|