ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,194
Office Version
  1. 2007
Platform
  1. 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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
Do you have any other code in the workbook/worksheet?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top