Viewing Names

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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
.

Relevant Pages

  • Re: Viewing Names
    ... I have two macros, one creates a Name called 'Expiration Date', and the ... MsgBox "Your trial period will expire in 3 days. ... to extend. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Viewing Names
    ... How are you going to keep someone from opening with the macros disabled? ... I have two macros, one creates a Name called 'Expiration Date', and the ... to extend. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Viewing Names
    ... someone elses workbook to bypass their protection? ... I have two macros, one creates a Name called 'Expiration Date', and the ... to extend. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How to time-limit an Excel file installation
    ... In my file, I've called the main macro TB, and the foo macro TB_see ... I create a new workbook, paste the TB macro code into a general module exactly ... 'Private Sub Workbook_Open ... ' expiration date. ...
    (microsoft.public.excel.worksheet.functions)
  • Help needed with the following macro
    ... The following macro i borrowed from somewhere else. ... ' Clear out any old data on Report ... Sheets("Contract Expiration Report").Select ... Sheets("CST Distribution List").Select ...
    (microsoft.public.excel.programming)