Code lags when run
Results 1 to 7 of 7

Thread: Code lags when run
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Code lags when run

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,121
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Code lags when run

    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
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,972
    Post Thanks / Like
    Mentioned
    22 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Code lags when run

    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

  4. #4
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code lags when run

    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 ?
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,121
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Code lags when run

    Do you have any other code in the workbook/worksheet?
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code lags when run

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code lags when run

    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/enuuar...GRASS.zip/file
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •