locked
Need help to disable a userform RRS feed

  • Question

  • Hello all!

    I use a userform in excel 2007 and would like the option to tick a box so that the userform never is displayed afterwards.

    I have tried UserForm3.Enable = False but still the userform is enabled after I've saved and opened the workbook again.

    I guess I'm doing something wrong, but I don't know what.

    Very thankful for any help!

    Wednesday, November 19, 2014 9:42 AM

Answers

  • You'll have to store the value in the workbook.

    One option is to store it in a cell. For example, if you set Sheet2!A1 to TRUE or FALSE, you can check Worksheets("Sheet2").Range("A1").Value before showing the userform.

    Another option is to store it in a custom document property. Select File > Info > Properties > Advanced Properties, then activate the Custom tab.
    Enter a name, e.g. DisableUserform, select 'Yes or No' as type, and set its value to No, then click Add.

    You can now inspect and set ActiveWorkbook.CustomDocumentProperties("DisableUserform") in VBA. The value will be stored with the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ollall Wednesday, November 19, 2014 1:09 PM
    Wednesday, November 19, 2014 12:06 PM

All replies

  • Hello,

    Are you refering to some kind of message a user reads the first time, and afterwards does not want to see anymore?

    In that case I think you should use some kind of global variabel (as boolean) wich indicates wether you want to see the box.

    eg.

    declare following in the header of a moduel (not you form): public SHOW_POPUP_MESSAGE as boolean. than, before you show the form, first test the boolean. on the other hand, when the user "ticks" the option, set the boolean to true.

    hope this is what you are looking for.

    Wednesday, November 19, 2014 10:51 AM
  • I've just tried it but after I save and close the workbook and reopen it the SHOW_POPUP_MESSAGE is set to false again. This means that the userform is displayed again.

    How do I do?

    Very thankful for your help! 

    Wednesday, November 19, 2014 11:14 AM
  • You'll have to store the value in the workbook.

    One option is to store it in a cell. For example, if you set Sheet2!A1 to TRUE or FALSE, you can check Worksheets("Sheet2").Range("A1").Value before showing the userform.

    Another option is to store it in a custom document property. Select File > Info > Properties > Advanced Properties, then activate the Custom tab.
    Enter a name, e.g. DisableUserform, select 'Yes or No' as type, and set its value to No, then click Add.

    You can now inspect and set ActiveWorkbook.CustomDocumentProperties("DisableUserform") in VBA. The value will be stored with the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ollall Wednesday, November 19, 2014 1:09 PM
    Wednesday, November 19, 2014 12:06 PM
  • Thanks Hans! The second option is awesome!
    Wednesday, November 19, 2014 1:09 PM
  • The idea of storing in the workbook is the best solution. Another alternative is to save in a defined name:

    Sub pMain()
      'Procedure that opens an UserForm:
      
      Dim oName As Excel.Name
      
      On Error Resume Next
      Set oName = Names(mcsName)
      On Error GoTo 0
      
      If oName Is Nothing Then
        Names.Add mcsName, "=1"
        UserForm3.Show
        'Maybe you'll want to save the workbook to save the setting
        'ThisWorkbook.Save
      End If
    End Sub
    
    Sub pReset()
      'Use this code to reset/clean your application registry:
      On Error Resume Next
      Names(mcsName).Delete
    End Sub
    

    If for any reason the workbook is a template or read-only, you could save the settings in the registry:

    Const mcsApplicationName As String = "My Application"
    
    Sub pMain()
      'Procedure that opens an UserForm:
      Const csKey As String = "First Time"
      
      If CBool(GetSetting(mcsApplicationName, UserForm3.Name, csKey, True)) Then
        SaveSetting mcsApplicationName, UserForm3.Name, csKey, False
        UserForm3.Show
      End If
    End Sub
    
    Sub pReset()
      'Use this code to reset/clean your application registry:
      DeleteSetting mcsApplicationName
    End Sub
    



    Felipe Costa Gualberto - http://www.ambienteoffice.com.br


    Wednesday, November 19, 2014 8:34 PM