Userform to appear on startup but only until set date

RockEd

Board Regular
Joined
Aug 13, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have an excel workbook with a number of users (a lot that I don't know who they are and the people might change). In order so they are kept up to date of what changes have happened to the workbook though, I would like to input a 'changelog' that automatically opens when they open the excel file. However, I am not planning on making many changes in the future, and so I would like the userform to only appear for say 40 days, and then disappear.

Is this possible?

The icing on the cake would be a count down in days until the message disappears...!
The basic code I've got to open the userform is:

VBA Code:
Option Explicit
Private Sub Workbook_open()
Changelog.Show
End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Clearly not thinking straight this morning... it's quite simple actually!

VBA Code:
Option Explicit
Private Sub Workbook_open()
If 15 / 12 / 2021 < Date Then
Changelog.Show
Else
Debug.Print "Date is in the past"
End If


End Sub
 
Upvote 0
I don't think the answer you posted will work correctly but will keep showing the form, because 15/12/2021 = 0.00061850569025235 and it will be always smaller than the result of the Date function.

I believe what you need is the following.

VBA Code:
Private Sub Workbook_open()
  If DateValue("12/15/2021") < Date Then
    Changelog.Show
  Else
    Debug.Print "Date is in the past"
  End If
End Sub

Or if I am wrong, then could you please explain how your answer works? Because I'll have to remove the solution mark from that post if it doesn't work.

Additional note: I am still not sure if it is the best idea to hardcode the date string. As an idea, perhaps you might want to use the workbook creation date to decide 40 days in the future. Something like below maybe:
VBA Code:
If DateValue(Activeworkbook.BuiltinDocumentProperties("Creation Date")) + 40 < Date Then
 
Upvote 0
I don't think the answer you posted will work correctly but will keep showing the form, because 15/12/2021 = 0.00061850569025235 and it will be always smaller than the result of the Date function.

I believe what you need is the following.

VBA Code:
Private Sub Workbook_open()
  If DateValue("12/15/2021") < Date Then
    Changelog.Show
  Else
    Debug.Print "Date is in the past"
  End If
End Sub

Or if I am wrong, then could you please explain how your answer works? Because I'll have to remove the solution mark from that post if it doesn't work.

Additional note: I am still not sure if it is the best idea to hardcode the date string. As an idea, perhaps you might want to use the workbook creation date to decide 40 days in the future. Something like below maybe:
VBA Code:
If DateValue(Activeworkbook.BuiltinDocumentProperties("Creation Date")) + 40 < Date Then
It seems to work for me - I am on a UK date format (DD/MM/YY) - is that where the difference lies?

I'm only assuming it works because the code brings up the userform, and if I flip the smaller than sign around, then the userform doesn't load upon loading the spreadsheet.

i.e. this doesn't load it:

VBA Code:
Private Sub Workbook_open()
  If DateValue("12/15/2021") > Date Then
    Changelog.Show
  Else
    Debug.Print "Date is in the past"
  End If
End Sub

However, yes I like your idea about the creation date; I've just got to remember not to update it in the meantime with some small trivial change ;)
 
Upvote 0
You could also use a literal date as follows :
VBA Code:
  If #15/12/2021# < Date Then
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top