I am trying to workout if its possible to have a MsgBox pop up when the spreadsheet opens, I know that can be done, but I am trying to get it to tell me this...
Column A I have a list of Engineers
Column B I have an expiry date for his Works Certificate.
I want to try and have my MsgBox pop up on Open and tell me that Engineer X has an Out of Date Certificate. I am trying to get it to do this a month earlier than the date, and give the user the option to change it now or cancel for later, I then am trying to have the same MsgBox if the user cancels it everytime the user opens the spreadsheet till thedate is changed.
You have a few additional conditions that need to be considered to implement this. Is this a multi user accessed file or are you the only person accessing and maintaining the data? If you have multi users you will need to identify the user and check the data pertaining to the user.
I would not suggest using an on-open msg for this purpose. You could have a process to sort data by the user with the earliest dates at the top of the list. Then if there are dates that exceed a limit when the user closes the workbook, display a msgbox "You have certificates expired over 10 days ago".
Like Mike suggested You can use the Workbook Events Workbook_Open or Workbook_BeforeClose to activate code to display a user message.
Thanks for the repies,
There is going to be only 1 person using the report. I have tried the Workbook_Open and can get a MsgBox to appear in this code, but what I am unsure of how to do is to link whats written in the MsgBox to cells that are in the spreadsheet.
I have a column that gives each date in the Certificate column a code like A,B,C or D (these all represent a status - A is out of date, B is nearing due date and so on) What I want to write in the MsgBox is that when this column is Status B that on open the MsgBox will look for Status B and then pull all Contractor Names from another column into it and say something like "All Contractors listed have nearing Out of Date Cert"
Since you have one primary user that will check cert status it would work better to configure a process that checks the stats and can be activated on-demand by pressing a button. That would be less intrusive than having a message auto run.
To get to the actual question... the way you would interface cell values to a msgbox text or controls on a user form would be to concatonate the text using a variable then assign the variable to the msg or userform control. It is simple to assign the text to the message. The more complex question is what process will be used in evaluating the records. How is the UI configured. How is the code going to be organized.
Here is an example of using a variable to build a message:
dim strCellVal as string
dim msg as string
dim i as integer
i = 5
strCellVal = ActiveSheet.Cells(i, 2)
msg = "The value of the Range B" & i & " = " & strCellVal
In this simple example you do not have any logic to give you the data you need. A functional process may be outlined like this.
Toolbar created with button to activate worksheet eval proc.
Eval process loops through all records on the worksheet and gets count of records with Expired Certs, and Count of records within 10 days of Expiration.
Count of records is displayed to the user in a Yes/Know msgbox that offers user the option of showing the records expired and nearing expiration.
If user selects Yes to msg a process runs to set a filter on data to only show the records that are Expired or nearing Expiration.