When to use screenupdating?

[Guest]

hi,

I'm having some difficulty in getting the screenupdating to work.
Firstly, I have 1 small module, module1 (reside in sheet1), which call
another moduel, module2, and this also call another 4 mdoules, module3 to
module6 say.

With module3 (work on sheet3), and module5 (work on sheet4), both is
required to insert columns or rows to copy and add data, and it seem that I
must activate or select the sheet under either modules before I can add or
copy. Please let me know if there is another way? 'Cause I notice that the
moment a sheet, is Selected, the screenupdating stop working. I have declared
ScreenUpdating=Flase inside Module1, Module3 & Module5. But They don't work
in module 3 and 5.

Could someone pleaser let me the rule in using ScreenUpdating? Ie, if like
me, who has about 4 to 6 modules interrelated?

 

[Sharad]

Once screenupdating is set to false, it is not automatically turned on
after code is executed. You have to set it to true in your code, or you
have to quit excel and restart it.

When screenupdating is set to false, if you are selecting any ranges on
the sheet which was active just before setting screenupdating to false,
then you can see the on the screen that different ranges are being
selected.

Well to add rows / columns or data it is not neccessary to activate or
select the sheet.

See bellow example, run it with Sheet1 being active, it will add column,
row and data in to sheet2 without selecting or activating it.

With Worksheets("Sheet2")
With .Range("A5")
.EntireColumn.Insert
.Value = "xyz"
End With
With .Range("D5")
.EntireRow.Insert
.Value = "abc"
End With
End With
End Sub

Sharad

 

[Guest]

Hi Sharad,

Is it possible to insert or hide a column without selecting a sheet first?
Ie, could I insert a column in sheet3, while sheet 1 is activated?

thanks
Augustus

 

[Guest]

I worked this one out:
With Worksheets("Sheet2")
With .Range("A5")
.EntireColumn.hidden=true
End With

But could someone tell me, is it possible to select a cell without Select /
Activate a sheet? Ie, I'm seeing sheet1, execute some VBA based on a command
button on sheet1, which does something to SHeet2 & sheet3, and I wan
Range("A10") on sheet2 & range("E20") on sheet3 to be selected, so when user
switch to sheet2 or 3, the first selected cell is those two range.

Thanks

 

[Dave Peterson]

You can remember where you are, go off and select that other sheet/cell, then
come back to where you were.

Kind of like:

dim PrevSelection as range
dim PrevActiveCell as range

application.screenupdating = false

set prevselection = selection
set prevactivecell = activecell

application.goto worksheets("sheet2").range("a10")
application.goto worksheets("sheet3"").range("e20")

application.goto prevselection
prevactivecell.select

application.screenupdating = true