Hi All

I have a mystery.

I have two macros , one creates a Name called 'Expiration Date', and the
other reveals it.

This is the first macro :

Sub TB()
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''

Dim ExpirationDate As String
Dim NameExists As Boolean

On Error Resume Next
ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
If Err.Number < 0 Then
'''''''''''''''''''''''''''''''''''''''''''
' Name doesn't exist. Create it.
' If the defined name didn't exist,
' Save the workbook to establish the newly created name.
'''''''''''''''''''''''''''''''''''''''''''

If NameExists = False Then
ExpirationDate = CStr(DateSerial(Year(Now), _
Month(Now), Day(Date) + C_NUM_DAYS_UNTIL_EXPIRATION))
ThisWorkbook.Names.Add Name:="ExpirationDate", _
RefersToLocal:=Format(ExpirationDate, "short date"), _
Visible:=False 'False for final edition.
ThisWorkbook.Save 'saves on first open but not subsequent
openings
End If
Else
NameExists = True
End If


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''
' If today is past the expiration date, close the
' workbook. Give a countdown from 3 days to closure.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''

If CDate(Date) = (CDate(ExpirationDate) - 3) Then
MsgBox "Your trial period will expire in 3 days. Please contact us
to extend. ", vbExclamation
End If

If CDate(Date) = (CDate(ExpirationDate) - 2) Then
MsgBox "Your trial period will expire in 2 days. Please contact us
to extend. ", vbExclamation
End If

If CDate(Date) = (CDate(ExpirationDate) - 1) Then
MsgBox "Your trial period will expire in 1 day. Please contact GP
PrackPack to extend. ", vbExclamation
End If

If CDate(Now) CDate(ExpirationDate) Then
MsgBox "Your trial period has now expired. Please contact us to
extend. ", vbExclamation
ThisWorkbook.Close savechanges:=False
End If


End Sub




I have been using this macro to reveal the Names in the workbook , so I
can delete the Expiration Date if need be :


Sub TB_See()

For Each Name In ThisWorkbook.Names
Name.Visible = True
Next
End Sub


I run it then view , or delete via Insert - Name - Define.

However , just recently it's not working , and the Define command is
greyed out. It usually just works and I can see the values by clicking
Define. Presently it won't let me in at all

Can someone advise?



Best Wishes

How are you going to keep someone from opening with the macros disabled?

Barb Reinhardt

"Colin Hayes" wrote:


Hi All

I have a mystery.

I have two macros , one creates a Name called 'Expiration Date', and the
other reveals it.

This is the first macro :

Sub TB()
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''

Dim ExpirationDate As String
Dim NameExists As Boolean

On Error Resume Next
ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
If Err.Number < 0 Then
'''''''''''''''''''''''''''''''''''''''''''
' Name doesn't exist. Create it.
' If the defined name didn't exist,
' Save the workbook to establish the newly created name.
'''''''''''''''''''''''''''''''''''''''''''

If NameExists = False Then
ExpirationDate = CStr(DateSerial(Year(Now), _
Month(Now), Day(Date) + C_NUM_DAYS_UNTIL_EXPIRATION))
ThisWorkbook.Names.Add Name:="ExpirationDate", _
RefersToLocal:=Format(ExpirationDate, "short date"), _
Visible:=False 'False for final edition.
ThisWorkbook.Save 'saves on first open but not subsequent
openings
End If
Else
NameExists = True
End If


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''
' If today is past the expiration date, close the
' workbook. Give a countdown from 3 days to closure.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''

If CDate(Date) = (CDate(ExpirationDate) - 3) Then
MsgBox "Your trial period will expire in 3 days. Please contact us
to extend. ", vbExclamation
End If

If CDate(Date) = (CDate(ExpirationDate) - 2) Then
MsgBox "Your trial period will expire in 2 days. Please contact us
to extend. ", vbExclamation
End If

If CDate(Date) = (CDate(ExpirationDate) - 1) Then
MsgBox "Your trial period will expire in 1 day. Please contact GP
PrackPack to extend. ", vbExclamation
End If

If CDate(Now) CDate(ExpirationDate) Then
MsgBox "Your trial period has now expired. Please contact us to
extend. ", vbExclamation
ThisWorkbook.Close savechanges:=False
End If


End Sub




I have been using this macro to reveal the Names in the workbook , so I
can delete the Expiration Date if need be :


Sub TB_See()

For Each Name In ThisWorkbook.Names
Name.Visible = True
Next
End Sub


I run it then view , or delete via Insert - Name - Define.

However , just recently it's not working , and the Define command is
greyed out. It usually just works and I can see the values by clicking
Define. Presently it won't let me in at all

Can someone advise?



Best Wishes

Hi,

Did you create this workbook or are you extending the expiration date of
someone elses workbook to bypass their protection?

Just thought I'd ask.

--
Thanks,
Shane Devenshire


"Colin Hayes" wrote:


Hi All

I have a mystery.

I have two macros , one creates a Name called 'Expiration Date', and the
other reveals it.

This is the first macro :

Sub TB()
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''

Dim ExpirationDate As String
Dim NameExists As Boolean

On Error Resume Next
ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
If Err.Number < 0 Then
'''''''''''''''''''''''''''''''''''''''''''
' Name doesn't exist. Create it.
' If the defined name didn't exist,
' Save the workbook to establish the newly created name.
'''''''''''''''''''''''''''''''''''''''''''

If NameExists = False Then
ExpirationDate = CStr(DateSerial(Year(Now), _
Month(Now), Day(Date) + C_NUM_DAYS_UNTIL_EXPIRATION))
ThisWorkbook.Names.Add Name:="ExpirationDate", _
RefersToLocal:=Format(ExpirationDate, "short date"), _
Visible:=False 'False for final edition.
ThisWorkbook.Save 'saves on first open but not subsequent
openings
End If
Else
NameExists = True
End If


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''
' If today is past the expiration date, close the
' workbook. Give a countdown from 3 days to closure.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''

If CDate(Date) = (CDate(ExpirationDate) - 3) Then
MsgBox "Your trial period will expire in 3 days. Please contact us
to extend. ", vbExclamation
End If

If CDate(Date) = (CDate(ExpirationDate) - 2) Then
MsgBox "Your trial period will expire in 2 days. Please contact us
to extend. ", vbExclamation
End If

If CDate(Date) = (CDate(ExpirationDate) - 1) Then
MsgBox "Your trial period will expire in 1 day. Please contact GP
PrackPack to extend. ", vbExclamation
End If

If CDate(Now) CDate(ExpirationDate) Then
MsgBox "Your trial period has now expired. Please contact us to
extend. ", vbExclamation
ThisWorkbook.Close savechanges:=False
End If


End Sub




I have been using this macro to reveal the Names in the workbook , so I
can delete the Expiration Date if need be :


Sub TB_See()

For Each Name In ThisWorkbook.Names
Name.Visible = True
Next
End Sub


I run it then view , or delete via Insert - Name - Define.

However , just recently it's not working , and the Define command is
greyed out. It usually just works and I can see the values by clicking
Define. Presently it won't let me in at all

Can someone advise?



Best Wishes

Hi all

OK I've fixed it now. Thanks for all your input.

Best wishes




In article , Barb
Reinhardt writes
How are you going to keep someone from opening with the macros disabled?

Barb Reinhardt

"Colin Hayes" wrote:


Hi All

I have a mystery.

I have two macros , one creates a Name called 'Expiration Date', and the
other reveals it.

This is the first macro :

Sub TB()
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''

Dim ExpirationDate As String
Dim NameExists As Boolean

On Error Resume Next
ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
If Err.Number < 0 Then
'''''''''''''''''''''''''''''''''''''''''''
' Name doesn't exist. Create it.
' If the defined name didn't exist,
' Save the workbook to establish the newly created name.
'''''''''''''''''''''''''''''''''''''''''''

If NameExists = False Then
ExpirationDate = CStr(DateSerial(Year(Now), _
Month(Now), Day(Date) + C_NUM_DAYS_UNTIL_EXPIRATION))
ThisWorkbook.Names.Add Name:="ExpirationDate", _
RefersToLocal:=Format(ExpirationDate, "short date"), _
Visible:=False 'False for final edition.
ThisWorkbook.Save 'saves on first open but not subsequent
openings
End If
Else
NameExists = True
End If


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''
' If today is past the expiration date, close the
' workbook. Give a countdown from 3 days to closure.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''

If CDate(Date) = (CDate(ExpirationDate) - 3) Then
MsgBox "Your trial period will expire in 3 days. Please contact us
to extend. ", vbExclamation
End If

If CDate(Date) = (CDate(ExpirationDate) - 2) Then
MsgBox "Your trial period will expire in 2 days. Please contact us
to extend. ", vbExclamation
End If

If CDate(Date) = (CDate(ExpirationDate) - 1) Then
MsgBox "Your trial period will expire in 1 day. Please contact GP
PrackPack to extend. ", vbExclamation
End If

If CDate(Now) CDate(ExpirationDate) Then
MsgBox "Your trial period has now expired. Please contact us to
extend. ", vbExclamation
ThisWorkbook.Close savechanges:=False
End If


End Sub




I have been using this macro to reveal the Names in the workbook , so I
can delete the Expiration Date if need be :


Sub TB_See()

For Each Name In ThisWorkbook.Names
Name.Visible = True
Next
End Sub


I run it then view , or delete via Insert - Name - Define.

However , just recently it's not working , and the Define command is
greyed out. It usually just works and I can see the values by clicking
Define. Presently it won't let me in at all

Can someone advise?



Best Wishes