PDA

View Full Version : Solved: compile error with simple macro



sunilmulay
10-11-2008, 11:55 PM
Hi there
I'm trying to create a simple macro that will check in a hidden sheet if the current date (cell J6) is greater than the set Expiration date (cell K6). (See code below). If this condition is met, I want the workbook to close otherwise to proceed, with the other commands, which are to hide all the hidden sheets and activate the first sheet. The latter commands work fine, but the initial bit of checking the dates and closing the workbook, returns a Compile Error...

What am I doing wrong????

Thanks
Sunil
p.s I am new to VBA, so I'm probably doing something very obviously wrong here.....

Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False

With ActiveWorkbook.Worksheets("H00-Reference Data")
If Range("J6").Value > Range("K6").Value Then
ActiveWorkbook.Close False
End If
End With

Else
'Hide confidential sheet at startup
Call HideSheets

With Worksheets("Project-Nav")
.Activate
.Range("D5").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub

senthil.sak7
10-12-2008, 12:26 AM
Try out with this...


Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False

With ActiveWorkbook.Worksheets("H00-Reference Data")
If Range("J6").Value > Range("K6").Value Then
ActiveWorkbook.Close False
Else

'Hide confidential sheet at startup
Call HideSheets

With Worksheets("Project-Nav")
.Activate
.Range("D5").Select
End With
End If
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Bob Phillips
10-12-2008, 02:53 AM
You should also dot qualify theranges



Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False

With ActiveWorkbook.Worksheets("H00-Reference Data")
If .Range("J6").Value > .Range("K6").Value Then
ActiveWorkbook.Close False
End If
End With

'Hide confidential sheet at startup
Call HideSheets

With Worksheets("Project-Nav")
.Activate
.Range("D5").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub