|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
All
Using formula to check date in a cell when opening workbook. Nb: A1 =
today()
if prior to 01/11/2006 ok no changes if equal to or after the 01/11/2006
then not ok and hide all sheets bar one called contact.
It only works if exact match i.e. if date on sheet is 01/11/2006
If Sheets("Menu").Range("A1") = "01/11/2006" Then hide sheets and reveal
contact The equal sign = being the key
When I try >= is grater than or equal to regardless of date (I.e.
31/10/2006 ) still hides sheets!!!
HELP!!!
Private Sub Workbook_Open()
If Sheets("Menu").Range("A1") >= "01/11/2006" Then
Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT
WW", "Summary") _
).Select
Sheets("Health Dec").Activate
ActiveWindow.SelectedSheets.Visible = False
Sheets("Contact").Visible = True
Sheets("Long stay").Select
ActiveWindow.SelectedSheets.Visible = False
End If
End Sub
|
|
I used variables to store today's date, as well as the date you want to
check. Included message boxes to show the results. Works for me! Suggest
you adapt your code accordingly
Option Explicit
Dim vTest As Variant, dDate As Date, dDate2 As Date
Sub Checking()
dDate = Range("A1").Value
dDate2 = Now()
If dDate <= dDate2 Then
vTest = MsgBox("Date is before today's date")
Else
vTest = MsgBox("Date is after today's date")
End If
End Sub
"PJ" wrote:
[Quoted Text] > All
>
> Using formula to check date in a cell when opening workbook. Nb: A1 =
> today()
> if prior to 01/11/2006 ok no changes if equal to or after the 01/11/2006
> then not ok and hide all sheets bar one called contact.
>
> It only works if exact match i.e. if date on sheet is 01/11/2006
>
> If Sheets("Menu").Range("A1") = "01/11/2006" Then hide sheets and reveal
> contact The equal sign = being the key
>
> When I try >= is grater than or equal to regardless of date (I.e.
> 31/10/2006 ) still hides sheets!!!
>
> HELP!!!
>
> Private Sub Workbook_Open()
> If Sheets("Menu").Range("A1") >= "01/11/2006" Then
> Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT
> WW", "Summary") _
> ).Select
> Sheets("Health Dec").Activate
> ActiveWindow.SelectedSheets.Visible = False
> Sheets("Contact").Visible = True
> Sheets("Long stay").Select
> ActiveWindow.SelectedSheets.Visible = False
> End If
> End Sub
>
>
>
|
|
PJ,
Try replacing the line:
[Quoted Text] > If Sheets("Menu").Range("A1") >= "01/11/2006" Then
with:
If Sheets("Menu").Range("A1").Value2 = DateValue("2/11/2006") Then
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
sandymann2[ at ]mailinator.com
Replace[ at ]mailinator.com with [ at ]tiscali.co.uk
"PJ" <paul.thomas5[ at ]ntlworld.com> wrote in message
news:Xf8Rg.36459$SH2.17225[ at ]newsfe4-gui.ntli.net...
> All
>
> Using formula to check date in a cell when opening workbook. Nb: A1 =
> today()
> if prior to 01/11/2006 ok no changes if equal to or after the 01/11/2006
> then not ok and hide all sheets bar one called contact.
>
> It only works if exact match i.e. if date on sheet is 01/11/2006
>
> If Sheets("Menu").Range("A1") = "01/11/2006" Then hide sheets and reveal
> contact The equal sign = being the key
>
> When I try >= is grater than or equal to regardless of date (I.e.
> 31/10/2006 ) still hides sheets!!!
>
> HELP!!!
>
> Private Sub Workbook_Open()
> If Sheets("Menu").Range("A1") >= "01/11/2006" Then
> Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT
> WW", "Summary") _
> ).Select
> Sheets("Health Dec").Activate
> ActiveWindow.SelectedSheets.Visible = False
> Sheets("Contact").Visible = True
> Sheets("Long stay").Select
> ActiveWindow.SelectedSheets.Visible = False
> End If
> End Sub
>
|
|
Use "date literals" instead of strings.
From Excel VBA help:
date literal:
Any sequence of characters with a valid format that is surrounded by number
signs (#). Valid formats include the date format specified by the locale
settings for your code or the universal date format.
For example, #12/31/92# is the date literal that represents December 31,
1992, where English-U.S. is the locale setting for your application. Use date
literals to maximize portability across national languages.
--
Festina Lente
"PJ" wrote:
[Quoted Text] > All
>
> Using formula to check date in a cell when opening workbook. Nb: A1 =
> today()
> if prior to 01/11/2006 ok no changes if equal to or after the 01/11/2006
> then not ok and hide all sheets bar one called contact.
>
> It only works if exact match i.e. if date on sheet is 01/11/2006
>
> If Sheets("Menu").Range("A1") = "01/11/2006" Then hide sheets and reveal
> contact The equal sign = being the key
>
> When I try >= is grater than or equal to regardless of date (I.e.
> 31/10/2006 ) still hides sheets!!!
>
> HELP!!!
>
> Private Sub Workbook_Open()
> If Sheets("Menu").Range("A1") >= "01/11/2006" Then
> Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT
> WW", "Summary") _
> ).Select
> Sheets("Health Dec").Activate
> ActiveWindow.SelectedSheets.Visible = False
> Sheets("Contact").Visible = True
> Sheets("Long stay").Select
> ActiveWindow.SelectedSheets.Visible = False
> End If
> End Sub
>
>
>
|
|
I like:
If Sheets("Menu").Range("A1").Value >= dateserial(2006,1,11) Then
(01/11/2006 meant January 11th, 2006???)
PJ wrote:
[Quoted Text] >
> All
>
> Using formula to check date in a cell when opening workbook. Nb: A1 =
> today()
> if prior to 01/11/2006 ok no changes if equal to or after the 01/11/2006
> then not ok and hide all sheets bar one called contact.
>
> It only works if exact match i.e. if date on sheet is 01/11/2006
>
> If Sheets("Menu").Range("A1") = "01/11/2006" Then hide sheets and reveal
> contact The equal sign = being the key
>
> When I try >= is grater than or equal to regardless of date (I.e.
> 31/10/2006 ) still hides sheets!!!
>
> HELP!!!
>
> Private Sub Workbook_Open()
> If Sheets("Menu").Range("A1") >= "01/11/2006" Then
> Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT
> WW", "Summary") _
> ).Select
> Sheets("Health Dec").Activate
> ActiveWindow.SelectedSheets.Visible = False
> Sheets("Contact").Visible = True
> Sheets("Long stay").Select
> ActiveWindow.SelectedSheets.Visible = False
> End If
> End Sub
--
Dave Peterson
|
|
All
Thank you all for your suggestions What I did in the end to get it to work
was in a seperate cells was:
i.e. A2 =Today() A3 01/11/2006 in A4 if(A2>=A3,1,0) cell A1= A4
Private Sub Workbook_Open()
If Sheets("Menu").Range("A1") = "1" Then
Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT
WW", "Summary")).Select
Sheets("Health Dec").Activate
ActiveWindow.SelectedSheets.Visible = False
Sheets("Contact").Visible = True
Sheets("Long stay").Select
ActiveWindow.SelectedSheets.Visible = False
End If
End Sub
Many thanks
Paul
"Dave Peterson" <petersod[ at ]verizonXSPAM.net> wrote in message
news:4515516A.D4A71220[ at ]verizonXSPAM.net...
[Quoted Text] >I like:
>
> If Sheets("Menu").Range("A1").Value >= dateserial(2006,1,11) Then
>
> (01/11/2006 meant January 11th, 2006???)
>
>
>
> PJ wrote:
>>
>> All
>>
>> Using formula to check date in a cell when opening workbook. Nb: A1 =
>> today()
>> if prior to 01/11/2006 ok no changes if equal to or after the 01/11/2006
>> then not ok and hide all sheets bar one called contact.
>>
>> It only works if exact match i.e. if date on sheet is 01/11/2006
>>
>> If Sheets("Menu").Range("A1") = "01/11/2006" Then hide sheets and reveal
>> contact The equal sign = being the key
>>
>> When I try >= is grater than or equal to regardless of date (I.e.
>> 31/10/2006 ) still hides sheets!!!
>>
>> HELP!!!
>>
>> Private Sub Workbook_Open()
>> If Sheets("Menu").Range("A1") >= "01/11/2006" Then
>> Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU",
>> "AT
>> WW", "Summary") _
>> ).Select
>> Sheets("Health Dec").Activate
>> ActiveWindow.SelectedSheets.Visible = False
>> Sheets("Contact").Visible = True
>> Sheets("Long stay").Select
>> ActiveWindow.SelectedSheets.Visible = False
>> End If
>> End Sub
>
> --
>
> Dave Peterson
|
|
|