VBA Workbook_Open only run if individual user has turned this off from a msgbox.

[Lewzerrrr]

Hi,

So at the moment, I have a code that runs some information on a msgbox as the workbook opens.

What I need the full code to do is wrap a "Would you like to turn this message off today?" around the msgbox. If vbYes then for that user, the code won't run again for today.

Unsure of how to go about this, would I need to store a number and username at the end of the code if vbyes has been clicked and then at the beginning of the code, test if username and number runs true then exit sub, else run code? But I'd also need it test if the date is different from the one stored? As I want it to automatically run each day unless turned off?

Thanks,

 

[Logit]

.

https://stackoverflow.com/questions/13600270/vba-run-macro-once-a-day

[TABLE]
<tbody>[TR]
[TD="class: votecell"][/TD]
[TD="class: answercell"][FONT=inherit][FONT=inherit]You can use Windows Task Scheduler to automatically open the file once a day. There's a really good [URL="http://krgreenlee.blogspot.co.uk/2006/04/excel-running-excel-on-windows-task.html"]step-by-step tutorial here[/URL] with the required VB Script code included.[/FONT]
[FONT=inherit]If the user may also be opening the file manually, you will want a state variable which records whether the macro has already run that day. The best bet is probably to have a sheet dedicated to recording this. Perhaps call it <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">RunTimes</code>. Then you can add the following line to your <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Workbook_Open</code> event:[/FONT]
[CODE]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#101094][FONT=inherit]

If[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Date[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Max[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"RunRecords"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A:A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]))[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Call[/FONT][/COLOR][COLOR=#303336][FONT=inherit] YourMacroName
    Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"RunRecords"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Rows[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlUp[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Offset[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Date[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If


[/FONT][/COLOR]</code>

[/FONT][/TD]
[/TR]
</tbody>[/TABLE]
[/CODE]

To obtain User Name :

https://community.spiceworks.com/topic/361258-using-vba-to-report-user-s-full-name-maybe-from-ad

Or perhaps consider having each User login with an ID or password and track that.


You could store all the information on a hidden worksheet for the macro to refer to.