Hi All

I need some help to extend some existing VBA.

To indicate when the content on the sheet was last updated. I use this
code :

..Range("A21").Value = "Last Updated : " & Format(Now, " dddd dd/mm/yy
at hh:mm:ss")


It reads , for example :

Last Updated : Thursday 30/06/11 at 19:45:27


I need to extend it to include reference to shop opening and closing
times.

The shop is open between 8 am and 4.30 pm , and closed outside these
hours.

So the output of the new code would read something like :

Last Updated : Thursday 30/06/11 at 19:45:27 , when the shop was
closed.

Or

Last Updated : Thursday 30/06/11 at 11:45:27 , when the shop was
open.


Can someone help to extend the coding?


Grateful for any advice.

Try...

.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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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"
.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

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

In article , GS writes
Try...

.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



HI Garry

OK thanks for getting but so expertly.

I'm getting an 'Expected End Sub' error just before the line

Function Get_ShopOpenStatus(CurrentTime As Variant) As String

I'm placing your code in a Private Sub context under the tab on the
sheet. I wonder if this is causing the issue.

Thanks again for your help.

Colin Hayes explained on 6/30/2011 :
In article , GS writes
Try...

.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



HI Garry

OK thanks for getting but so expertly.

I'm getting an 'Expected End Sub' error just before the line

Function Get_ShopOpenStatus(CurrentTime As Variant) As String

I'm placing your code in a Private Sub context under the tab on the sheet. I
wonder if this is causing the issue.

Thanks again for your help.

Colin,
The line of code is a revised version of the snippet of code you
provided in your original post. Just replace your original line with
mine...!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc