vba problem

  • Thread starter Thread starter PJ
  • Start date Start date
P

PJ

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,

Try replacing the line:
If Sheets("Menu").Range("A1") >= "01/11/2006" Then
Click to expand...

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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.
 
I like:

If Sheets("Menu").Range("A1").Value >= dateserial(2006,1,11) Then

(01/11/2006 meant January 11th, 2006???)
 
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
 
Back
Top