vba 复印纸无闪烁
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/1394701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy sheet without flicker
提问by graham.reeds
I would like to copy a template sheet to the left of itself to be populated by a report generator. This all works fine.
我想将模板表复制到其左侧以由报告生成器填充。这一切正常。
However when it comes to the sheet copying line (shown below) their is a flash as excel appears and then disappears - though occasionally I am left with a blue, partially filled in excel window.
但是,当涉及到工作表复制线(如下所示)时,它们会在 excel 出现然后消失时闪烁 - 尽管偶尔我会留下一个蓝色的,部分填充在 excel 窗口中。
I already have Application.ScreenUpdating
set to false and .Visible
is also set to false, and I reset them everytime to minimise the disruption. This flash is really annoying. Is there anyway of preventing it?
我已经Application.ScreenUpdating
设置为 false 并且.Visible
也设置为 false,我每次都重置它们以最大程度地减少中断。这个闪光灯真的很烦人。有没有办法阻止它?
' create new sheet from template sheet
shtDeliveryVariance.Copy Before:=shtDeliveryVariance
Set shtVariance = Sheets(shtDeliveryVariance.Index - 1)
shtVariance.Name = "Delivery Variance " & Format(nSheetNumber, "000")
' minimise the flashes
Application.Interactive = False
Application.ScreenUpdating = False
Application.Visible = False
Update: If I use Set shtVariance = Sheets.Add
I don't get the flash, but I lose all the pretty formatting.
更新:如果我使用,Set shtVariance = Sheets.Add
我不会得到 Flash,但我会丢失所有漂亮的格式。
回答by andyb
maybe I've misunderstood but shouldn't you have application.screenupdating set to false before copying?
也许我误解了,但您不应该在复制之前将 application.screenupdating 设置为 false 吗?
UpdateStill not entirely clear on what is causing the problem but the screen flicker could be down to the copied worksheet being activated. I did get some screen flicker with a worksheet containing a large image using code like yours. You could try to disable the activation by setting Application.EnableEvents = False Maybe something like this:
更新仍然不完全清楚导致问题的原因,但屏幕闪烁可能归结为复制的工作表被激活。我确实使用像您这样的代码使用包含大图像的工作表出现了一些屏幕闪烁。您可以尝试通过设置 Application.EnableEvents = False 来禁用激活可能是这样的:
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim active As Worksheet
Set active = ThisWorkbook.ActiveSheet 'or somesuch
'your code here
active.Activate
Application.EnableEvents = True
Application.ScreenUpdating = true
回答by Robert Mearns
I could only get a single 'flash' when running your code.
运行您的代码时,我只能得到一个“闪光”。
This is when the code in this line is run
这是运行这一行代码的时间
Application.Visible = False
This happens because Excel is hidden, the desktop is displayed momentarily and then Excel is displayed again. I would remove that line of code.
发生这种情况是因为 Excel 已隐藏,桌面会暂时显示,然后再次显示 Excel。我会删除那行代码。
I would also check to see that the sheet that was active when the code was called is selected again before turning the screen updating back on.
在重新打开屏幕更新之前,我还会检查是否再次选择了调用代码时处于活动状态的工作表。
Sub Test_Flash()
Dim shtDeliveryVariance As Worksheet
Dim i As Integer
Application.Interactive = False
Application.ScreenUpdating = False
Set shtDeliveryVariance = ActiveWorkbook.Worksheets("Sheet1")
nSheetNumber = 1
For i = 1 To 100
shtDeliveryVariance.Copy Before:=shtDeliveryVariance
Set shtVariance = Sheets(shtDeliveryVariance.Index - 1)
shtVariance.Name = "Delivery Variance " & Format(nSheetNumber, "000")
nSheetNumber = nSheetNumber + i
Next i
ActiveWorkbook.Worksheets("Sheet1").Select
Application.Interactive = True
Application.ScreenUpdating = True
End Sub