24

I need to close an Excel userform using VBA when a user has clicked a submit button and operations have been carried out.

How can I close a Userform from itself?

I have tried this but it returns a 361 error.

Unload Me
TylerH
  • 20,799
  • 66
  • 75
  • 101
Kian
  • 1,260
  • 1
  • 13
  • 32
  • Where do you call `Unload Me` from? – Cutter Feb 28 '12 at 22:53
  • 2
    Need more information. DoCmd is an MS-Access Command and not MS-Excel Command. Are you trying to close an Excel userform from Access? – Siddharth Rout Feb 28 '12 at 22:57
  • @SiddharthRout I thought so when I was doing research - just it was suggested on a number of forums. – Kian Feb 28 '12 at 23:02
  • @Cutter The end of a btnSubmit_Click handler – Kian Feb 28 '12 at 23:03
  • 1
    Can you explain a little more in detail on what exactly you are trying to do? – Siddharth Rout Feb 28 '12 at 23:04
  • After the user submits data in a form (clicks the submit button) I want to form to close – Kian Feb 28 '12 at 23:24
  • 1
    If you are doing this from with Excel Userform then "Unload Me" is the right way to do it. There must be some other reason. Can you share the complete code for the btnSubmit_Click? – Siddharth Rout Feb 28 '12 at 23:46
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/8325/discussion-between-kian-mayne-and-siddharth-rout) – Kian Feb 29 '12 at 00:14
  • 1
    Please do not edit an answer in to the question. Instead, post it as a separate answer below and accept it. – Matt Nov 09 '15 at 18:16

4 Answers4

24

As specified by the top answer, I used the following in the code behind the button control.

Private Sub btnClose_Click()
    Unload Me
End Sub

In doing so, it will not attempt to unload a control, but rather will unload the user form where the button control resides. The "Me" keyword refers to the user form object even when called from a control on the user form. If you are getting errors with this technique, there are a couple of possible reasons.

  1. You could be entering the code in the wrong place (such as a separate module)

  2. You might be using an older version of Office. I'm using Office 2013. I've noticed that VBA changes over time.

From my experience, the use of the the DoCmd.... method is more specific to the macro features in MS Access, but not commonly used in Excel VBA.

Under normal (out of the box) conditions, the code above should work just fine.

user4059073
  • 256
  • 3
  • 2
  • Recommend to read the article [UserForm1.Show?](https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/) explaining why it's *not* the best idea to use `Unload Me` (as well as the UserForm's default instance :-). – T.M. Nov 20 '19 at 19:45
11

Without seeing your full code, this is impossible to answer with any certainty. The error usually occurs when you are trying to unload a control rather than the form.

Make sure that you don't have the "me" in brackets.

Also if you can post the full code for the userform it would help massively.

SWa
  • 4,343
  • 23
  • 40
  • Apologies, the full code was posted [here](http://chat.stackoverflow.com/transcript/message/2784564#2784564) I'll update the question to include it – Kian Mar 04 '12 at 18:30
  • But also I did post that the code `Unload Me` returned the 361 error – Kian Mar 04 '12 at 18:35
  • 1
    Hi Kian, That isn't the full code for the userform, it is the code for the submit button - you have class level declarations etc... Can you post the full code for the userform - including the Unload me that doesn't work? – SWa Mar 04 '12 at 18:42
  • No class level declarations or anything; but it's suddenly started working so I have no idea what the problem was. – Kian Mar 04 '12 at 18:52
  • hmmm mdlRegex looks suspiciously like a class to me... Glad it's working though :) - btw your regexp check doesn't work for emails – SWa Mar 04 '12 at 18:54
  • Well it definitely didn't cause any errors, and when you said *class **level** declarations* I assumed you meant variables that had been declared at class level, not just class definitions – Kian Mar 04 '12 at 18:56
  • It isn't declared in the sub, so must be declared at the top of the form class? – SWa Mar 04 '12 at 18:57
  • No, it's just in the excel project – Kian Mar 04 '12 at 18:57
  • 1
    Ah right ok, its best not to declare global variables unless you really need to, in my experience they can cause more problems than they cause and usually aren't neccessary :) – SWa Mar 04 '12 at 18:59
  • 1
    I know this is an old answer but I had Me in brackets and it never worked. I read this and took it out and it works. Why is this? I thought functions/procedures could be entered with or without the brackets, so why the difference with this? – Mike Dec 07 '16 at 08:27
4

Unload Me only works when its called from userform self. If you want to close a form from another module code (or userform), you need to use the Unload function + userformtoclose name.

I hope its helps

Tomamais
  • 95
  • 3
  • 9
-1

It should also be noted that if you have buttons grouped together on your user form that it can link it to a different button in the group despite the one you intended being clicked.

SamG
  • 1