n
选中B2->格式->条件格式->公式->=(LEFT($B2,1)="A")->设定字体及图案.添加公式->类推...用格式刷填充到所需单元格.条件格式最多可设定三个,多过请用VBA实现
在VBA的FRM窗口右上方的“X”如果去掉
Q:问题一:关于加密窗口的问题!上次请教了一个关闭EXCEL函数的问题!现在又发现一个问题:即在VBA的FRM窗口右上方的“X”如果去掉?因为它关闭了我的加密窗口就不起作用?或者如果按“X”的时候,自动关闭EXCEL就行,如何?多谢!
问题二,我每次经过加密窗口后进入工作表,总是被隐藏了,用什么函数把隐藏的自动打开?这样我的可以写一个宏!OK!
A: tof
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
UserForm1.Caption = "The Close box won't work! Clickcom!"
End Sub
dick
Sub dd()
For i = 1 To Worksheets.Count
Sheets(i).Visible = True
Next i
End Sub
vba for excel程序纠错
Q:我想对所有工作表进行将公式转为数值,录制宏后加上了几句但结果不对,请斧正!
sub aa()
dim sht as worksheet
For Each sht In Worksheets
ActiveSheet.Range("A1:C4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Next
End Sub
A: roof
试试在for...each...和activesheet...之间加上一句“sht.activate"。结果如下:
sub aa()
dim sht as worksheet
For Each sht In Worksheets
sht.activate
ActiveSheet.Range("A1:C4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Next
End Sub
excelhelp:其实录制得来的宏(macro)程序可以自行修改,以达到高效率、精简的目的。你的程序这样改会较容易看得懂,记着,Selection一般可以省去,使程序一气呵成,也可以避免现存储存格(activecell,浮标所在地)给移动。
Sub aa()
Dim Sht As Worksheet
For Each Sht In Worksheets
With Sht.Range("A1:C4")
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Next
End Sub
虽然以上的程序可以把A1:C4的公式删去,但它有两个缺点:
1)范围只能限定为A1:C4,要是多了一个不在此范围的储存格(cell)有公式,又或是每张工作表(worksheet)的范围都不一样,这程序便会有问题;
2)在宏执行时,画面会闪动,这是Copy和Paste的副作用。
故此,我建议用以下程序代替:
Sub aa2()
Dim wSht As Worksheet
Dim rCell As Range
For Each wSht In Worksheets
For Each rCell In wSht.UsedRange
rCell.Value = rCell.Value
Next
Next
End Sub
这样写较简洁,也没有上述的问题。
leaf :Sub aa()
Dim sht as WorkSheet
Dim rng as Range
For Each sht In Worksheets
Set rng = sht.Cells
With rng
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False
Next
End Sub
请问各位有什么方法可以使工作表从1至50自动排列
Sub paixu()
For i = 1 To Sheets.Count
For j = i To Sheets.Count
If Int(Sheets(i).Ncom) > Int(Sheets(j).Ncom) Then
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
End Sub
如何将自动将公式的值固定在单元格
我又有新问题了.怎么样在一定的条件下将公式转换成值固定在在单元格内?
解答:Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
With ThisWorkbook.ActiveSheet
If Target.Column = 3 And Target.Row > 1 Then
If VBA.Trim(.Cells(Target.Row, 4).Value) = "counting" Then
Target.Value = .Cells(Target.Row, 2) - .Cells(Target.Row, 1)
End If
End If
End With
End Sub
将这段内容加入到thisworkbook模块中,在你选中要计算的单元格时,会为你自动计算,当然你也可以修改一下使其完全自动化。
输入字母转换成设定的数值
比如,我设置第一个框的范围是(a=2.7 b=1.8 c=0.9 d=0)如果我输入c,那它能不能变成0.9或者最后相加减的时候转换?
解答:Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value = "a" Then
Target.Value = 2.7
ElseIf Target.Value = "b" Then
Target.Value = 1.8
ElseIf Target.Value = "c" Then
Target.Value = 0.9
ElseIf Target.Value = "d" Then
Target.Value = 0
End If
End Sub
或者:如果临时要用,在“工具-自动更正”里设置也可以。
打开一张工作表时系统提示要求输入用户名和密码
解答:
If Application.InputBox("请输入密码:") = "" Then
Sheets(".....").Visible = True
Sheets(".....").Select
Range("...").Select
Else: c = "对不起,密码不正确"
d = " 警告"
MsgBox prompt:=c, Title:=d
End
End If
工具菜单与视图中的工具栏不同
屏蔽工具菜单宏