application screenupdating

methody

Well-known Member
Joined
Jun 17, 2002
Messages
843
Hello all
I am activating the code below from a command button on a userform. The background to the code is that the user selects a cell then right clicks and a 'Move cells' option appears. On clicking it a userform appears and the user selects where the cells can be moved to. There is a series of restrictions on whether the cells can be moved and these are built into the code.

My problems is simply the the screen flickers. I have been inserting application.screenupdating= true and application.screenupdating = true in every combination but can get it. Is there a rule about where to inut them?

Can anyone help. thank you

Code:
Dim x, y As Double, c As Range
Dim w As Range
Dim mycheck As String
Dim mycheck1 As String
Dim mycheck2 As String
 
Set c = Selection
 
 
x = UserForm2.ComboBox1.Value
If x = False Then Exit Sub
 
Set w = ActiveSheet.Columns(9).Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole)
v = w.Offset(0, 1).Value
 
If UserForm2.ComboBox1.Value = "" Then
MsgBox ("x")
ActiveSheet.Protect ("jweir")
Exit Sub
Else
If c.Offset(0, 2).Value = "" Then
MsgBox ("x")
UserForm2.ComboBox1 = ""
 
Exit Sub
Else
If w.Offset(0, 2).Value <> "" Then
MsgBox ("x")
UserForm2.ComboBox1 = ""
 
Exit Sub
Else
If w.Offset(0, -6) <> c.Offset(0, -6) Then
MsgBox ("x")
UserForm2.ComboBox1 = ""
ActiveSheet.Protect ("jweir")
Exit Sub
Else
If w.Offset(0, 2) = "" And c.Offset(0, 1) <> w.Offset(0, 1) Then
mycheck1 = MsgBox("x?", vbYesNo)
If mycheck1 = vbNo Then
UserForm2.ComboBox1 = ""
 
Exit Sub
 
Else
 
w.Offset(0, 2).Value = c.Offset(0, 2).Value
w.Offset(0, 3).Value = c.Offset(0, 3).Value
w.Offset(0, 7).Value = c.Offset(0, 7).Value
c.Offset(0, 2).Value = ""
c.Offset(0, 3).Value = ""
c.Offset(0, 7).Value = ""
UserForm2.ComboBox1 = ""
UserForm2.Hide
 
End If
Else
 
If w.Offset(0, 12).Value = "x" Then
mycheck = MsgBox("x?", vbYesNo)
If mycheck = vbNo Then
UserForm2.ComboBox1 = ""
 
Exit Sub
 
Else
 
w.Offset(0, 2).Value = c.Offset(0, 2).Value
w.Offset(0, 3).Value = c.Offset(0, 3).Value
w.Offset(0, 7).Value = c.Offset(0, 7).Value
c.Offset(0, 2).Value = ""
c.Offset(0, 3).Value = ""
c.Offset(0, 7).Value = ""
UserForm2.ComboBox1 = ""
UserForm2.Hide
 
End If
Else
 
If w.Offset(0, -8) <> c.Offset(0, -8) Then
mycheck2 = MsgBox("x?", vbYesNo)
If mycheck2 = vbNo Then
UserForm2.ComboBox1 = ""
 
Exit Sub
 
Else
 
w.Offset(0, 2).Value = c.Offset(0, 2).Value
w.Offset(0, 3).Value = c.Offset(0, 3).Value
w.Offset(0, 7).Value = c.Offset(0, 7).Value
c.Offset(0, 2).Value = ""
c.Offset(0, 3).Value = ""
c.Offset(0, 7).Value = ""
UserForm2.ComboBox1 = ""
UserForm2.Hide
 
End If
Else
w.Offset(0, 2).Value = c.Offset(0, 2).Value
w.Offset(0, 3).Value = c.Offset(0, 3).Value
w.Offset(0, 7).Value = c.Offset(0, 7).Value
c.Offset(0, 2).Value = ""
c.Offset(0, 3).Value = ""
c.Offset(0, 7).Value = ""
UserForm2.ComboBox1 = ""
UserForm2.Hide
 
End If
End If
End If
End If
End If
End If
End If
 
End Sub
 
Private Sub CommandButton2_Click()
UserForm2.Hide
 
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

detlion1643

Board Regular
Joined
Nov 25, 2009
Messages
164
you actually want to insert

Application.ScreenUpdating = False
Application.DisplayAlerts = False

if i read right in your post, you tried to turn application.screenupdating=true, which will cause the flicker. set it to false to not see the flicker.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
I have been inserting application.screenupdating= true and application.screenupdating = true in every combination but can get it.

True is where your problem is. You suppress ScreenUpdating by turning it Off (False), not On (True).

Generally put application calls at the beginning of your code and reset them at the end. E.G.

Code:
With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
 
     ' Your code here
 
  .DisplayAlerts = True
  .ScreenUpdating = True
End With

HTH,
 

methody

Well-known Member
Joined
Jun 17, 2002
Messages
843
Sorry that was a mistake I was putting in false first then true.

I also have these bits of code in the background. would this be part of the problem

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.DisplayAlerts = False


If Not Application.Intersect(Target, Range("t10")) Is Nothing Then

filterfix
End If

If Not Application.Intersect(Target, Range("t12")) Is Nothing Then

filterm
End If

If Not Application.Intersect(Target, Range("t8")) Is Nothing Then

filterl
End If

If Not Application.Intersect(Target, Range("t6")) Is Nothing Then

filterd
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim nm As Name, rng As Range
On Error Resume Next
Set nm = ThisWorkbook.Names("CellAddress")
On Error GoTo 0
Set rng = Intersect(Target, Range("I15:I588"))

If Not nm Is Nothing Then nm.RefersToRange.Interior.Color = vbDarkBlue

If Not rng Is Nothing Then

If rng.Count = 1 Then

On Error Resume Next
nm.Delete
On Error GoTo 0
ThisWorkbook.Names.Add "CellAddress", rng
rng.Interior.Color = vbRed
End If
End If


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top