CODEWITHSUNDEEP
vbaexcel
Ren Alejandro
Ren Alejandro

Reputation: 1

How to show userform 1 time only

In VBA for excel, I have a userform then I want this to show only for 1 instance. Even if the user re-open it, it won't open again. Is there any code for it? well, I'm also using this code for my login:

Private Sub UserForm_Initialize()

User.Caption = Environ("Username")

End Sub

I'm thinking if i can use this code in my problem. Hoping for a quick response. thanks guys, you're awesome!

Upvotes: 0

Views: 426

Answers (2)

Comintern
Comintern

Reputation: 22185

If you don't want to add an extra sheet just to store one bool, you can set a custom document property like this:

Private Sub Workbook_Open()    
    On Error Resume Next
    Dim test As Boolean
    test = Me.CustomDocumentProperties("UserFormShown").Value
    If Err.Number = 0 Then Exit Sub
    UserForm1.Show
    Me.CustomDocumentProperties.Add "UserFormShown", False, msoPropertyTypeBoolean, True
End Sub

If the property hasn't been set yet it will throw an error, so trapping an error lets you know if you've set the property (and shown the form).

Upvotes: 0

Maciej Los
Maciej Los

Reputation: 8591

Yes, it's possible.

You have to add new sheet. In a cell A1 type 0 (zero), then hide it. In a code which calls UserForm, use this:

Sub ShowMyForm()
    If ThisWorkbook.Worksheets("HiddenSheet").Range("A1")=0 then MyUserForm.Show
End Sub

In a form:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ThisWorkbook.Worksheets("HiddenSheet").Range("A1")=1
    ThisWorkbook.Save()
    DoEvents
End Sub

Upvotes: 3

Related Questions