Hi,

I encounter a flickering problem with my program despite using
screenupdating. i suspect it is because I use select/activate, copy and paste
from different other worksheet which is seen as a LIBRARY. A sample of my
code is attached.

I wonder if it is possible to save the library in XLA and then call the
objects from XLA file using EXCEL VBA. Is it a better way of dealing the
flickering screen. If possible, i appreciate someone can tell me how to
accomplish it using Xla.

Look forward to your reply.
Best wishes, Loy



SAMPLE CODE

Public Sub OnshoreTrfr(ByVal myValue As Integer, myType As Integer)

Application.ScreenUpdating = False

Select Case myType
Case 1
If myValue = 1 Then
Sheets("LIB").Select
ActiveSheet.Shapes("Libon1x2wdgtrfr").Copy
Sheets("SLD").Select
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg1"
Selection.Left = 904
Selection.Top = 299
End If

If myValue = 2 Then
Sheets("LIB").Select
ActiveSheet.Shapes("Libon1x2wdgtrfr").Copy
Sheets("SLD").Select
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg1"
Selection.Left = 780
Selection.Top = 299
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg2"
Selection.Left = 1030
Selection.Top = 299
End If

Case 2
....

End Select
Sheet1.Select
Application.ScreenUpdating = True

End Sub

Loy,

..ScreenUpdating should not cause your screen to "flicker." Are you seeing
the screen move back and forth between worksheets as your various
Worksheet(index).Select statments execute? Are you stepping through your
code and noticing the screen move back and forth between worksheets (because
stepping through your code basically ignores the ScreenUpdating setting)?

Creating an xla file is not necessary and really depends on what you are
looking to do with this code. (Remember, your have a procedure and not a
function, and the way your code is written, it implies that you are calling
this code within VBA). You don't need ANY .Select statements in your code.
Excel VBA is object oriented. So, create worksheet and shape objects (and
whatever other objects you may need) and simply reference the object rather
than selecting the object.

For example, the code below will create two separate worksheet objects, and
change the name of each of the worksheets (without having to select the sheet
before applying the name change). I've also shown how to create a shape
object and how to change properties of the shape object via a With statement.
(The code is NOT tested and may not be the "most" efficient way to do
things, but it at least illustrates the point).

Dim wksOne As Worksheet
Dim wksTwo As Worksheet
Dim Shp As Shape

Set wksOne = Worksheets("LIB")
Set wksTwo = Worksheets("SLD")
Set Shp = wksOne.Shapes("Libon1x2wdgtrfr")

Shp.Copy
wksTwo.Paste

With Shp
.Name = "onstrfr1x2wdg1"
.Left = 904
.Top = 299
End With

wksOne.Name = "Worksheet 1"
wksTwo.Name = "Worksheet 2"

I hope this is helpful to you.

Best,

Matthew Herbert

"newbie" wrote:

Hi,

I encounter a flickering problem with my program despite using
screenupdating. i suspect it is because I use select/activate, copy and paste
from different other worksheet which is seen as a LIBRARY. A sample of my
code is attached.

I wonder if it is possible to save the library in XLA and then call the
objects from XLA file using EXCEL VBA. Is it a better way of dealing the
flickering screen. If possible, i appreciate someone can tell me how to
accomplish it using Xla.

Look forward to your reply.
Best wishes, Loy



SAMPLE CODE

Public Sub OnshoreTrfr(ByVal myValue As Integer, myType As Integer)

Application.ScreenUpdating = False

Select Case myType
Case 1
If myValue = 1 Then
Sheets("LIB").Select
ActiveSheet.Shapes("Libon1x2wdgtrfr").Copy
Sheets("SLD").Select
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg1"
Selection.Left = 904
Selection.Top = 299
End If

If myValue = 2 Then
Sheets("LIB").Select
ActiveSheet.Shapes("Libon1x2wdgtrfr").Copy
Sheets("SLD").Select
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg1"
Selection.Left = 780
Selection.Top = 299
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg2"
Selection.Left = 1030
Selection.Top = 299
End If

Case 2
....

End Select
Sheet1.Select
Application.ScreenUpdating = True

End Sub

Dear Matthew Herbert

Many thanks. It seems to work better this time.

Best wishes,
Loy

"Matthew Herbert" wrote:

Loy,

.ScreenUpdating should not cause your screen to "flicker." Are you seeing
the screen move back and forth between worksheets as your various
Worksheet(index).Select statments execute? Are you stepping through your
code and noticing the screen move back and forth between worksheets (because
stepping through your code basically ignores the ScreenUpdating setting)?

Creating an xla file is not necessary and really depends on what you are
looking to do with this code. (Remember, your have a procedure and not a
function, and the way your code is written, it implies that you are calling
this code within VBA). You don't need ANY .Select statements in your code.
Excel VBA is object oriented. So, create worksheet and shape objects (and
whatever other objects you may need) and simply reference the object rather
than selecting the object.

For example, the code below will create two separate worksheet objects, and
change the name of each of the worksheets (without having to select the sheet
before applying the name change). I've also shown how to create a shape
object and how to change properties of the shape object via a With statement.
(The code is NOT tested and may not be the "most" efficient way to do
things, but it at least illustrates the point).

Dim wksOne As Worksheet
Dim wksTwo As Worksheet
Dim Shp As Shape

Set wksOne = Worksheets("LIB")
Set wksTwo = Worksheets("SLD")
Set Shp = wksOne.Shapes("Libon1x2wdgtrfr")

Shp.Copy
wksTwo.Paste

With Shp
.Name = "onstrfr1x2wdg1"
.Left = 904
.Top = 299
End With

wksOne.Name = "Worksheet 1"
wksTwo.Name = "Worksheet 2"

I hope this is helpful to you.

Best,

Matthew Herbert

"newbie" wrote:

Hi,

I encounter a flickering problem with my program despite using
screenupdating. i suspect it is because I use select/activate, copy and paste
from different other worksheet which is seen as a LIBRARY. A sample of my
code is attached.

I wonder if it is possible to save the library in XLA and then call the
objects from XLA file using EXCEL VBA. Is it a better way of dealing the
flickering screen. If possible, i appreciate someone can tell me how to
accomplish it using Xla.

Look forward to your reply.
Best wishes, Loy



SAMPLE CODE

Public Sub OnshoreTrfr(ByVal myValue As Integer, myType As Integer)

Application.ScreenUpdating = False

Select Case myType
Case 1
If myValue = 1 Then
Sheets("LIB").Select
ActiveSheet.Shapes("Libon1x2wdgtrfr").Copy
Sheets("SLD").Select
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg1"
Selection.Left = 904
Selection.Top = 299
End If

If myValue = 2 Then
Sheets("LIB").Select
ActiveSheet.Shapes("Libon1x2wdgtrfr").Copy
Sheets("SLD").Select
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg1"
Selection.Left = 780
Selection.Top = 299
ActiveSheet.Paste
Selection.Name = "onstrfr1x2wdg2"
Selection.Left = 1030
Selection.Top = 299
End If

Case 2
....

End Select
Sheet1.Select
Application.ScreenUpdating = True

End Sub