Viewing Names
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 |
Viewing Names
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 |
Viewing Names
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 |
Viewing Names
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 |
All times are GMT +1. The time now is 04:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com