Hi Garry

Yes , that's what I'm doing , in precisely the way you indicate , but
I'm still getting this 'Expected End Sub' error. Just before the start
of the Function code. Very mysterious. I'll give it another go.

This is the code I'm using now , with your revision in place. Does it
look OK to you?


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:="password"

.Range("A21").Value = "Last Updated: " _
& Format(Now, " dddd dd/mm/yy at hh:mm:ss") _
& ", when the shop was " & Get_ShopOpenStatus(TimeValue(Now))

Function Get_ShopOpenStatus(CurrentTime As Variant) As String
Dim vShopOpens, vShopCloses
vShopOpens = TimeValue("8:00 AM")
vShopCloses = TimeValue("4:30 PM")
If TimeValue(Now) vShopOpens And TimeValue(Now) < vShopCloses Then _
Get_ShopOpenStatus = "open." Else Get_ShopOpenStatus = "closed."
End Function

stoppit:
Application.EnableEvents = True
.Protect Password:="password"
End With
End If
End Function


Thanks again Garry





In article , GS writes
Colin Hayes has brought this to us :
In article , GS writes
Sorry Colin, I forgot to copy/paste the revised function!

Function Get_ShopOpenStatus(CurrentTime As Variant) As String
Dim vShopOpens, vShopCloses
vShopOpens = TimeValue("8:00 AM")
vShopCloses = TimeValue("4:30 PM")
If CurrentTime vShopOpens And CurrentTime < vShopCloses Then _
Get_ShopOpenStatus = "open." Else Get_ShopOpenStatus = "closed."
End Function


Hi Garry

Sorry - here's the whole of the code I'm trying to fit yours into ;


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:="password"

Replace the following line with my revised version...
=======================================
.Range("A21").Value = "Last Updated : " & Format(Now, " dddd
dd/mm/yy at hh:mm:ss")
=======================================

stoppit:
Application.EnableEvents = True
.Protect Password:="password"
End With
End If
End Sub


I should have sent it before. It will make the picture clearer.

Best Wishes