>When I refresh with the code in place , the numbers do refresh as they
>should , but nothing is showing in B16.
ShareSheet is protected so changes to B16 cannot be made.
Because I error-trapped the code, you don't see an error message informing you
of that fact.
Also, the data refresh is actually done on LookUpData sheet and ShareSheet D4 is
only a reference to D4 on that sheet,
We could change the event type to Calculate but that would also update B16 when
VirtualLookUpData is refreshed..............not desirable, I would say.
Delete the code you have in ShareSheet
The following amended code goes into module of LookUpData sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Me.Range("D4").Value <> "" Then
With Sheets("ShareSheet")
.Unprotect Password:="justme"
.Range("B16").Value = Format(Now, "dd-mm-yy hh:mm:ss")
stoppit:
Application.EnableEvents = True
.Protect Password:="justme"
End With
End If
End Sub
The code unprotects ShareSheet so the date/time can be added to B16
I used a cracker to unprotect ShareSheet for testing the code..
Works fine for me when I refresh data on LookUpData sheet.
Edit password "justme" to your actual password.
Gord
On Sat, 20 Nov 2010 22:47:03 +0000, Colin Hayes <(E-Mail Removed)>
wrote:
>In article <(E-Mail Removed)>, Gord Dibben
><(E-Mail Removed)> writes
>>The code will not produce the question marks and value errors when you refresh
>>data.
>>
>>What happens when you refresh data on your original sheet without the code?
>>
>>Simply editing "A1" to another cell should not produce any errors within the code.
>>
>>Make sure "B16" is out of refresh range.
>>
>>Did you for sure add the code to the sheet module?
>>
>>What type of errors does the code throw up?
>>
>>Can you post your attempt at amended code?
>>
>>
>>Gord
>
>
>Hi Gord
>
>Thanks for getting back.
>
>I've since found that the question marks were caused by a server issue
>at
>the site I'm updating from , so that's not from the code.
>
>When I refresh the data without the code , it updates the numbers it
>fetches from the internet as it should.
>
>I'm changing A1 in the code to D4 , which is one of the cells that will
>be
>updated on refresh.
>
>B16 is a blank cell on the sheet where I want the date / to be placed.
>
>I'm putting the code under the tab on the sheet I'm refreshing.
>
>When I refresh with the code in place , the numbers do refresh as they
>should , but nothing is showing in B16.
>
>Here's the code I'm putting in :
>
>Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo stoppit
> Application.EnableEvents = False
> With Me.Range("D4")
> 'edit to suit. chosen cell must be within refreshed data range
> If .Value <> "" Then
> Me.Range("B16").Value = Format(Now, "dd-mm-yy hh:mm:ss")
> 'edit "D1" to suit where you want the date/time entered
> End If
> End With
>stoppit:
> Application.EnableEvents = True
>End Sub
>
>
>For your interest , I've put the file itself here :
>
>http://www.chayes.demon.co.uk/ShareSample.xls
>
>
>
>Best Wishes
>
Similar Threads
