Egg Timer/Cursor Flashes...THIS TOPIC IS NOW CLOSED

[Jak]

When I run a macro and add the line:

Application.ScreenUpdating = False

it disables the flicker and is therefore desirable. What I have noticed however is that the egg timer/arrow cursor flashes instead until the macro finishes. Is there a line of code to stop the flashing?

heres a macro that I use which demonstrates the point in question.

Sub Macro1()
'
' If A1 is blank then pop up Msg message
If Range("A1").Value = "" Then
MsgBox "What are you doing?" & Chr$(13) & _
"There is No data to sort out!"
Exit Sub
End If

Application.ScreenUpdating = False

' No need to set default values of TextToColumns input parameters
Columns(1).TextToColumns _
Destination:=Range("A1"), _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1))

Rows(1).Insert Shift:=xlDown
Columns(1).Insert Shift:=xlToRight
Columns(1).Insert Shift:=xlToRight
Columns(1).Insert Shift:=xlToRight
Range("A1") = "Processor"
Range("A2") = "JJ"
Range("B1") = "Scheme No"
Range("C1") = "batch No"
Range("D1") = "First Name"
Range("E1") = "Initials"
Range("F1") = "Surname"
Range("G1") = "Previous Candidate"
Range("H1") = "Previous Candidate No"
Range("I1") = "Date of Birth"
Columns(10).Insert Shift:=xlToRight
Range("J1") = "Year Of Birth"
Range("J2") = Right(Range("I2"), 2)
Range("K1") = "Ethnic Origin"
Range("L1") = "Gender"

Dim c As Range
Set c = Range("D2")
While Not IsEmpty(c.Value)
c.Offset(0, -3).Value = "JJ"
Set c = c.Offset(1, 0)
Wend
Set c = Nothing

Dim n As Long
For n = 2 To Cells(500, 9).End(xlUp).Row
Cells(n, 10).Value = Right(Cells(n, 9).Value, 2)
Next n

LastRow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
ColumnNumber = 7

Range(Cells(1, ColumnNumber), Cells(LastRow, ColumnNumber)).Replace _
What:="", Replacement:="N"

ColumnNumber = 11

Range(Cells(1, ColumnNumber), Cells(LastRow, ColumnNumber)).Replace _
What:="", Replacement:="J"

Range("B2").Select
schemenumber = InputBox("Enter the Scheme Number:")
batchnumber = InputBox("Enter the Batch Number:")

Dim z As Range
Set z = Range("A2")
While Not IsEmpty(z.Value)
z.Offset(0, 1).Value = schemenumber
z.Offset(0, 2).Value = batchnumber
Set z = z.Offset(1, 0)
Wend
Set z = Nothing

Dim r As Range
For Each r In Range("A2:G500,K2:L500")
r.Value = UCase(r.Value)
Next

i = 0

Do While i< 500
i = i + 1
Cells(i, 7).Select
CellValue = ActiveCell.Value
If IsNumeric(CellValue) = True And Not CellValue = "" Then
CellValue = CStr(CellValue)
TheLenght = Len(CellValue)
Do While TheLenght< 6
CellValue = "0" + CellValue
TheLenght = Len(CellValue)
Loop
ActiveCell.Value = "'" + CellValue
End If

Loop

Columns(6).Select
Selection.Replace What:="MC", Replacement:="Mc", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True
Columns(2).NumberFormat = "@"
Columns("M:X").dELETE Shift:=xlToLeft
With Range("A:L")
.ClearFormats
.Font.Name = "Arial"
.Font.Size = 10
.HorizontalAlignment = xlLeft
Columns(2).EntireColumn.AutoFit
Columns(3).EntireColumn.AutoFit
Columns(4).EntireColumn.AutoFit
Columns(6).EntireColumn.AutoFit
Columns(9).EntireColumn.AutoFit
Columns(10).EntireColumn.AutoFit
Columns(12).EntireColumn.AutoFit
Columns("J:J").Select
Selection.NumberFormat = "00"

Range("A1").Select
Application.ScreenUpdating = True
End With
End Sub
This message was edited by Jak on 2002-10-20 10:18

 

[VoG]

Application.Cursor = xlWait

Will set the cursor as an egg timer. To set it back (before your code completes

Application.Cursor = xlDefault

 

[Jak]

Hi Vog

Added the line of code and it works great, no more flashing cursor. Many thanks.