Userform/MsgBox with a Do Not Show Again CheckBox

[Bench]

Hi,

I was wondering if there was a way to do a Userform with a Do Not Show again checkbox.

This is obviously cosmetically possible but what would the VBA be to make sure that the Userform didn't come up next time the user opens the file.

And would this work on a document that is accessed by a number of users? eg. one persons selects DO NOT SHOW AGAIN, when another user accesses the spreadsheet will the userform show until they tick the checkbox?

Thanks

 

[sijpie]

For a single user the solution is simple: if the box is checked a 1 is written to a 'hidden' cell somewhere, and on calling the userform first the value of this cell is checked.

For multi users things get more interesting. You need to know who is looking at the sheet, and then keep a tab on who has checked and who not.

Probably create an extra worksheet where you have a table of users and a 1 or 0 behind each.

You can find the username with

dim CurUser as string
 
CurUser = ActiveWorkbook.UserStatus(1, 1)

then you need to check if the user is in the list. if not add to the list, if so check for 1 or 0 behind the name

 

[MichaelNotMike]

I was searching for a way to do something similar, and thought I'd post what I found:

I have a series of excel sheets that are in the process of being upgraded, and I needed a reminder for the users of one of the upgrades to display the first time the workbook was opened, and give them the option to prevent it from displaying again.

I ended up creating a small form containing my message, a check box asking if the user would like the notice again, and an OK button to close the form. Additionally, I set up a registry key to track if the user had checked the box for this specific sheet.

In the ThisWorkbook object I added a workbook name property so I can reuse all the code, just changing it's assignment for each workbook, a quick if statement to launch the form when the workbook was activated if the registry key value was either "Y" or did not exist:

 Private workshtName As String

Private Sub Workbook_Activate()
    If workshtName = vbNullString Then
        workshtName = "book1"
    End If
    If GetSetting("workbookGroup", workshtName, "showWarningValue", "Y") = "Y" Then
        noSemiColons.chkShowAgain = False
        noSemiColons.Show
    End If
    AddMenus
End Sub

Public Property Get myName() As String
    myName = workshtName
End Property

Public Property Let myName(ByVal inName As String)
    workshtName = inName
End Property

The form consisted of a text box containing my message, a check box asking if the user wanted to see this warning in the future and an OK button. On the back end it only required a hide function and check box evaluator for the OK button:

Private Sub btnOK_Click()
'Update the registry key based on the check box
    If chkShowAgain.Value = True Then
        SaveSetting "workbookGroup", ThisWorkbook.myName, "showWarningValue", "N"
    Else
        SaveSetting "workbookGroup", ThisWorkbook.myName, "showWarningValue", "Y"
    End If
'close the form
    Me.Hide
End Sub

I've tested it extensively and it works wonderfully! Each user will now be able to define if they want to view the warnings on each workbook, and will be forced to view it at least once. The really cool thing about this is it stores the registry key in the current user settings, and has two layers of grouping.

You can find more detailed info on the get and set setting methods here:
http://msdn.microsoft.com/en-us/library/3kz7fyks(v=VS.71).aspx
http://msdn.microsoft.com/en-us/library/kb0c3wb9(v=VS.71).aspx

There is a great example of the methods being used here:
http://j-walk.com/ss/excel/tips/tip60.htm