Code lags when run

[ipbr21054]

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.

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

 

[Norie]

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.

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]

Hi

try

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]

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]

Do you have any other code in the workbook/worksheet?

 

[ipbr21054]

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]

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