How to show userform 1 time only

Question

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!

Siddharth Rout · Accepted Answer · 2015-05-18 09:18:59Z

3

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

edited May 18, 2015 at 9:18

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered May 18, 2015 at 8:33

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Siddharth Rout Over a year ago

The way that I do it is, I create a Name and check if the name exists :) That way no hidden sheet is required... And this works even when the user directly tries to launch the form from VBA :)

Maciej Los Over a year ago

@SiddharthRout, Thank you. A way you achieved that is very interesting. Cheers, Maciej

Tragamor Over a year ago

A similar question asked here: stackoverflow.com/questions/29940996/…

Maciej Los Over a year ago

@Flephal, i'd suggest to notify questioner, not me.

Comintern · Accepted Answer · 2015-05-18 23:53:49Z

0

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).

answered May 18, 2015 at 23:53

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Collectives™ on Stack Overflow

How to show userform 1 time only

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related