Proper use of "Me" in VBA

[dariusd7]

`I was hoping someone could help me to understand when I can use "Me" in VBA. I've seen many code where this is used, but when I try ot use it. I get an error.
"Invalid use of the "Me" command.

Here is the current code i tried to use it in.

Private Sub MySub()
     
     ' from other excel file
     Run "UserformExample.xlsm!ShowDataSheetForm"

    Unload Me
    ActiveWorkbook.Close
End Sub

what I am trying to do is close out the workbook, and open the form. I tried using the Hide command which works, but when I open another workbook through the menu, It says workbook already open. So I need to close the workbook not just hide it. I also tried just using
"Workbook.close"
The problem I had with this command is that is does close the workbook, but it also closes the form. when the user click the button on the workbook to access the User menuform. I need the form to open but the workbook to close.

 

[Luke M]

Two parts to your post, a) What is Me?, b) How to close form?

What is Me?
Me refers to the parent object from which the code is "sitting" in. For a UserForm, when you are writing the code within the UserForm module, Me will refer to that UserForm. Similarily, if you are writing in a Sheet module, the Me will refer to that specific sheet.

Using Me is handy because we don't have to worry about the sheet name changing, and it also makes it a little easy for future code readers as they don't have to remember that "Main UserForm" is the UserForm we are currently working on. You can apply the same methods to the Me that you would be able to apply to the object if you gave the full name.

Within the Sheet1 module, the following lines are identical in purpose
Worksheets("Sheet1").Range("A1").Select
Me.Range("A1").Select

A similar object is ThisWorkbook. ThisWorkbook always refers to the workbook from which the code is residing. Again, this is very handy when the file name may get changed, depending on the user.

How to close workbook and show form?
For ease of writing, I will us WB to refer to the UserFormExample.xlsm workbook and UF to refer to the UserForm. As you have found out, you can not show the UF w/o the WB being open. You can have the WB be hidden, and the user won't see anything on their screen, and have access to all of the code within that workbook. Again, you already know this. You can hide the WB and then save and close it as well. This way, the next time you open the WB (either normally or through a VB command) you don't have to hide it again.

When you are completely done with the UF, you should have the UF close code also close the WB, or you can have the code that tries to open the UF check if it can just be made visible again.

In short, you can not have the WB be completely closed and have the UF be visible.

I hope this helps explain things.

 

[dariusd7]

OK. Let me clear this up a little bit. The workbook that gets open is a different workbook from which the userform exist it. So the Userform allows the user to pull a workbook/file through means of a drop down list. one drop down is the month, which correlates to the folder and the second drop down is for the actual workbook/file.. so if they want to open workbook 1 in folder December, then they would select December and then workbook1..and it will Open that workbook. Once it is open I have a button on the worksheet, that when click should close out the current workbook and reopen the user Menu.

So what I want is for the workbook that gets open to close and then reopen the Menu which resides in a different workbook. the user Menu resides in the Userformexample.xlsm. While the workbook that gets open from teh userform Menu is a totally different workbook. So How do I do this?

 

[dariusd7]

As for the proper use of "Me" command. the code above exist in the workbook that gets opened. ie December/workbook1. So when I tried to use the "me" command to close out the workbook like this

me.close

but I got the error "invalid use of the me command."

 

[Luke M]

WB_A = Workbook with UserForm
WB_B = Workbook that gets opened from UserForm

WB_A code should activate the UserForm. UserForm code then will open the other workbook and hide it's own workbook. UserForm can remain visible while user does stuff in WB_B, or hide UserForm. If the latter, need someway to show WB_A again.

Code examples
In Regular module:

Sub ShowForm()
Windows(ThisWorkbook.Name).Visible = False
UserForm1.Show
End Sub

UserForm code, example shown by pushing a button:

Private Sub CommandButton1_Click()
Windows(ThisWorkbook.Name).Visible = True 'Hides this workbook
Me.Hide 'Hides the UserForm
End Sub

Then, to close WB_B and show WB_A (this code is in WB_B)

Sub CloseAndShow()
Windows("Workbook A.xlsm").Visible = True
'IMPORTANT
'this must be the last line you want to
'execute, as the code will no longer be available
'after closing the workbook
ThisWorkbook.Close

End Sub

 

[NARAYANK991]

Hi Darius ,

Can you say in which procedure you are trying out the Me.Close command ?

I opened a new workbook , and put in the following :

Private Sub Workbook_NewSheet(ByVal Sh As Object)
            Me.Close
End Sub

When I added a new sheet , immediately the workbook closed ( of course with the usual Excel display alert , since I had not turned alerts off ).

The point is that when you use the Me keyword , Excel should be able to deduce the context from the procedure in which you have used it ; if that is not possible , then an error will be generated.

Narayan

 

[NARAYANK991]

Hi Darius ,

Another point in favour of using the Me keyword is that Intellisense will be available ; so , if you use the Me keyword , press the period "." , and do not see the drop-down of available properties / methods , then it means the usage of Me is invalid.

Narayan

 

[dariusd7]

Thanks Narayan. That last point was helpful.

 

[dan_l]

also!! in a class you can go me.property to do stuff---- so like me.price * me.qty which is pretty much the cat's pajamas.