Code lags when run

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Hi,
On my worksheet called G EXPENSES i have the following code in use but when i leave the cell i dont see the name appear straight away but more like 2 seconds later.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D5:D35")) Is Nothing Then


If UCase(Target.Value) = "1" Then Target.Value = "BANWELL NEWS"
If UCase(Target.Value) = "2" Then Target.Value = "CHURCHILL POST OFFICE"
If UCase(Target.Value) = "3" Then Target.Value = "HUTTON STORES"
If UCase(Target.Value) = "4" Then Target.Value = "OLD MIXON MCCOLLS"
If UCase(Target.Value) = "5" Then Target.Value = "THE CAXTON LIBRARY"
If UCase(Target.Value) = "6" Then Target.Value = "HAYWOOD VILLAGE CO-OP"




End If
End Sub
My range on the sheet is A5:D35

Column A is DATE 14/08/2019 etc
Columb B is COST £9.99 etc
Column C is TEXT
Column D is TEXT where the code will input the name for me

So if i type in cell D30 the number 1 i then leave that cell and expect to see BANWELL NEWS appear, i do but seconds later,why the lag ???

Also i would like "but couldnt work it out" the rest of the range to have the UCase code applied.

Many thanks
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,654
Office Version
365
Platform
Windows
Since the code is in a sheet change event and it changes values on the sheets it's going to call itself, try disabling events.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D5:D35")) Is Nothing Then

        Application.EnableEvents = False

        If UCase(Target.Value) = "1" Then Target.Value = "BANWELL NEWS"
        If UCase(Target.Value) = "2" Then Target.Value = "CHURCHILL POST OFFICE"
        If UCase(Target.Value) = "3" Then Target.Value = "HUTTON STORES"
        If UCase(Target.Value) = "4" Then Target.Value = "OLD MIXON MCCOLLS"
        If UCase(Target.Value) = "5" Then Target.Value = "THE CAXTON LIBRARY"
        If UCase(Target.Value) = "6" Then Target.Value = "HAYWOOD VILLAGE CO-OP"

    End If

    Application.EnableEvents = True

End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,922
Office Version
2019
Platform
Windows
Hi

try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo myerror
    If Not Intersect(Target, Range("D5:D35")) Is Nothing Then
    Application.EnableEvents = False
     With Target
       If IsNumeric(.Value) Then .Value = Choose(.Value, "BANWELL NEWS", "CHURCHILL POST OFFICE", _
                                                         "HUTTON STORES", "OLD MIXON MCCOLLS", _
                                                         "THE CAXTON LIBRARY", "HAYWOOD VILLAGE CO-OP")
     End With
    End If
myerror:
    Application.EnableEvents = True
End Sub
Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Hi,
Just above my pc clock whilst i am waiting for excel to do its job i see some text flashing like,
Calculating 2 processors ?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,654
Office Version
365
Platform
Windows
Do you have any other code in the workbook/worksheet?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Hi,
Yes i do.
There are other worksheets in this workbook with code etc.

What i have just done for the last hour etc is to move say 5 worksheets from this workbook & have them in there own workbook.

Now ive cleared up most issues but can i ask a question about the name manager please.

Originally i had a workbook called DR
In the workbook just say i had worksheet 1-10
I then removed 5,6,7,8,9,10 to there workbook

Now in a new workbook called GRASS
I have worksheets 5,6,7,8,9,10

Now the question,
On the DR workbook in name magaer i can still see references to worksheets 5,6,7,8,9,10
LIKEWISE
On the GRASS workbook in name manager i still references to worksheets 1,2,3,4

So my question is the name manager is global across all workbooks OR should in the name manager only be references to the worksheets with in.

I dont wish to delete the wrong thing
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Hi,

Anybody have a few minutes spare to check this workbook or see if they can replicate my lag issue.
Its a very small workbook but i seem to have the issue on the worksheet called G INCOME

When you visit that worksheet G INCOME in colomn B select a name from the drop down list & as opposed to the cells in column C & D being filled straight away there are a few seconds lag time,looking down by the clock i see calculating 2 processors.

To try & fix this i have delete code then put it back again with no real progress.
I have also started with a blank worksheet & started to start from scratch but without any real design or code added i hit the issue with the G INCOME page.
So maybe the VLLOKUP code is at fault ???

Ive been looking for hours now & not got anywhere.

Many thanks.

Here is a copy of my file.

http://www.mediafire.com/file/enuuar4hn7j50rp/GRASS.zip/file
 

Watch MrExcel Video

Forum statistics

Threads
1,102,448
Messages
5,486,958
Members
407,574
Latest member
Greso

This Week's Hot Topics

Top