| View previous topic :: View next topic |
| Author |
Message |
geoff_f
OOo Enthusiast
Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia
|
 Posted: Tue Feb 10, 2004 2:52 am Post subject: Found Calc equivalent to Excel's ScreenUpdating = False |
 |
|
I've seen people asking for a Calc equivalent to Excel's ScreenUpdating = FALSE statement, and have not been able to find one - until now.
This works for me: | Code: | Sub TestLockControllers
Dim oDocument As Object, oSheets As Object, oSheet As Object
Dim oCells As Object, oCell As Object
oDocument = ThisComponent 'Get the document model
oSheets = oDocument.Sheets 'Get all sheets
oSheet = oSheets.getByIndex(0) 'Get sheet 0
'This is what does it!
oDocument.LockControllers 'Lock document update activity - stops screen updating
oCells = oSheet.getCellRangeByName("MyRange") 'Get the range of cells 'MyRange' in sheet 0
oCell = oCells.getCellByPosition(0, 0) 'Get the top left cell in MyRange
REM Do whatever you need to do with oCell - Change contents, CellColor, CellBackColor, etc.
REM Do other cells in MyRange, as needed. No screen updating will have yet occurred.
oDocument.UnlockControllers 'Unlock document update activity - re-enables screen updating
End Sub |
After all the trouble I've had finding out how to do this, it can't have been this easy, surely! |
|
| Back to top |
|
 |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
Jollibee
Guest
|
| Posted: Wed Feb 11, 2004 12:03 am Post subject: How About Locking/Unlocking DisplayAlerts? |
|
|
Your discovery is useful to speed macro... but How about equvalent Application.DisplayAlerts=False/True?
I have annoying popup message when merging two cells with existing values...I want to disable this... |
|
| Back to top |
|
|
geoff_f
OOo Enthusiast
Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia
|
| Posted: Wed Feb 11, 2004 3:04 am Post subject: A demonstration of lockControllers()/unlockControllers() |
|
|
For those who may not appreciate what using lockControllers() and unlockControllers() has on display refresh speed, you can use the code below to see a demonstration.
Open a new file in Calc and save it as 'LockControllers Test' (File->SaveAs). Create a new macro (Tools->Macros->Macro, click on Standard under LockControllers Test, type 'TestLockControllers' in the Macro name box and click on New. In the New Module dialog, insert 'LockControllersTest' as a module name. In the OOo Basic window then opened, delete all existing text and insert the following code: | Code: | Option Explicit
Global bClearCells as Boolean 'Clears cells (True) with data or fills them (False)
Global bActivateLock As Boolean 'Toggles whether lockControllers/unlockControllers is activated (True turns them on)
Dim oDocument As Object, oSheets As Object, oSheet As Object 'Document pointers
Sub TestLockControllers
Dim oCells As Object
oDocument = ThisComponent 'Get the document model
oSheets = oDocument.Sheets 'Get all sheets
oSheet = oSheets.getByIndex(0) 'Get Sheet1
If bActivateLock Then
oDocument.LockControllers 'Lock document update activity - stops screen updating
End If
oCells = oSheet.getCellRangeByName("A1:F30") 'Get the range of cells A1 to F30 in Sheet1
Select Case bClearCells 'Check whether we need to clear or fill cells
Case True: 'We need to clear cells this time
bClearCells = False 'Toggle flag to fill cells next time
ClearRange(oCells) 'Clear the cells now
Case False: 'We need to fill the cells this time
bClearCells = True 'Toggle flag to clear cells next time
FillRange(oCells) 'Fill the cells now
End Select
If bActivateLock Then
oDocument.UnlockControllers 'Unlock document update activity - re-enables screen updating
End If
If Not bClearCells And bActivateLock Then
bActivateLock = False
ElseIf Not bClearCells And Not bActivateLock Then
bActivateLock = True
End If
End Sub
Sub FillRange(oMyRange As Object)
'Fills oMyRange with characters, and changes the background color of each cell to Light Blue
Dim oCell As Object 'Temp cell object to work with oMyRange
Dim I As Integer, J As Integer
For I = 0 to 5 'Columns A to F
For J = 0 to 29 'Rows 1 to 30
oCell = oMyRange.getCellByPosition(I, J) 'Get each cell in MyRange
oCell.setString("XXX") 'Insert some characters
oCell.CellBackColor = RGB(160, 208, 255) 'Change the cell background color to Light Blue
Next J
Next I
End Sub
Sub ClearRange(oMyRange As Object)
'Clears MyRange of characters, and changes the background color of each cell to White
Dim oCell As Object 'Temp cell object to work with oMyRange
Dim I As Integer, J As Integer
For I = 0 to 5 'Columns A to F
For J = 0 to 29 'Rows 1 to 30
oCell = oMyRange.getCellByPosition(I, J) 'Get each cell in MyRange
oCell.setString("") 'Clear characters from Cell
oCell.CellBackColor = RGB(255, 255, 255) 'Change the cell background color to White
Next J
Next I
End Sub |
Save the macro (File->Save in the OOo Basic window) and close the OOo Basic window.
In the Calc window, long-click on the Forms button in the Main (left side) toolbar and select the Push Button button (you may have to activate Design Mode On/Off first). Draw a button in Sheet1 to cover the cells from G10 to H11. Right-click on this button, select Control... and change the Label property to 'Toggle Fill/Clear'. Click on the Events tab and select the action button ([...]) next to Mouse button pressed. Click on the 'plus' sign next to 'LockControllers Test.sxc', ditto to 'Standard', then click on 'LockControllersTest'. Under 'Existing macros in:', select 'TestLockControllers' and click on Assign, then OK. Back in Sheet1, close the Properties: Button dialog, click on Design Mode On/Off on the Forms toolbar to turn off Design Mode. Close the Forms toolbar and save the file.
Now you're ready to activate the demo. Clicking on the Toggle Fill/Clear button will alternate between filling Sheet1 from A1 to F30 with 'XXX' characters with a light blue background, and clearing the same area. The first time it does that, it will fill and clear without lockControllers()/unlockControllers() being used, thereafter it will alternate between using them and not using them. You will be able to see the speed difference between writing to individual cells as the fill/clear routines progress, versus waiting for all the cells to be filled/cleared then writing the display update once. |
|
| Back to top |
|
|
geoff_f
OOo Enthusiast
Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia
|
| Posted: Wed Feb 11, 2004 4:09 am Post subject: Calc Equivalent for Application.DisplayAlerts |
|
|
Jollibee wrote: | Quote: | | Your discovery is useful to speed macro... but How about equvalent Application.DisplayAlerts=False/True? |
Sorry, I don't know anything about an equivalent in Calc for Application.DisplayAlerts. Maybe someone else can help (?). |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
arthurkhachikyan
Newbie
Joined: 04 Feb 2007
Posts: 3
Location: Armenia
|
| Posted: Sun Feb 18, 2007 2:23 am Post subject: Re: Found Calc equivalent to Excel's ScreenUpdating = False |
|
|
This did not work for me in OO 2.0.4  . Does anybody know why?[/quote] |
|
| Back to top |
|
|
geoff_f
OOo Enthusiast
Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia
|
| Posted: Sat Feb 24, 2007 1:34 am Post subject: |
|
|
| It works for me in OOo 2.0.4. The only difference if you want to run the demo macro above is the method of accessing the Forms Controls toolbar, which has changed since the macro was posted. Use View-> Toolbars-> Form Controls, then the procedure stated thereafter should get the demo to run. |
|
| Back to top |
|
|
rufus
General User
Joined: 18 May 2007
Posts: 20
Location: Bangkok, Thailand
|
| Posted: Fri Jun 08, 2007 10:05 pm Post subject: |
|
|
| Quote: |
Henrik's post...
Code:
Sub ScreenUpdatingOn
ThisComponent.UnlockControllers
ThisComponent.removeActionLock
End Sub
Sub ScreenUpdatingOff
ThisComponent.addActionLock
ThisComponent.LockControllers
End Sub
|
This code does not work for me too... I use OO 2.2.0 rus...
Is there another way to disable system alerts? |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3318
|
|
| Back to top |
|
|
rufus
General User
Joined: 18 May 2007
Posts: 20
Location: Bangkok, Thailand
|
| Posted: Sat Jun 09, 2007 1:11 am Post subject: |
|
|
Unfortunatly, it did not help..
Adding this sentence:
ThisComponent.CurrentController.Frame.ContainerWindow.Enable = False
to
ThisComponent.addActionLock
ThisComponent.LockControllers
did not solve the problem. System alerts continue to appear.
By the way, in OO 2.2.0 original version (eng) this works. So it seems to be localisation problem. |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3318
|
| Posted: Sat Jun 09, 2007 3:27 am Post subject: |
|
|
I think you are better off reviving that other thread. You may still not get a solution but it will be read by more people than a thread in Code Snippets.
_________________
search forum by month |
|
| Back to top |
|
|
|
