ThisWorkbook vs ActiveWOrkbook

[lezawang]

Hi I am following this video

https://www.youtube.com/watch?v=K0VqyXLJBOw&index=8&list=PLw8O1w0Hv2ztGjIkrW7suD6oNDaOk3vbR

and he used "ThisWorkbook" but whenever I use "ThisWorkbook" I gets compile error but if I used ActiveWorkbook instead I wont get any error message. What is wrong? Why the author can use "thisWorkbook" but I can not?
Thanks

 

[MARK858]

ActiveWorkbook is as it says the workbook that is active and ThisWorkbook is the workbook the code resides in.

Post the code you are using that fails if you can't see the issue.

Please note that I am not hone to see the video.

 

[Norie]

lezawang

Can you post the code that's causing the problem?

Mark

I was going to watch the video, kind of bored - don't ask, but it's more than 10 minutes long.

 

[MARK858]

but it's more than 10 minutes long.

Too long to borher watching on my phone then although I speculate that it probably isn't a major problem with the code, just that the workbook with the code isn't active when it is run... but we will see.

 

[lezawang]

I see your point of not watching the video. I will not post video link, really not good idea. I am attaching the code here. The code is reside inside a Module (Personal-->Module) could that be the problem? If I change everything to ActiveWork, I wont get any error msg

Sub test()
ThisWorkbook.Sheets("sheet2").Visible = False
ThisWorkbook.Sheets("sheet2").Visible = True
End Sub

 

[MARK858]

As I have stated ThisWorkbook is the workbook that that holds the code and so you are asking the macro to run on the Personal workbook and not the workbook that is active.
You are probably getting the error message because you don't have a Sheet2 in the Personal workbook.

If I change everything to ActiveWork, I wont get any error msg

Rather than ask why not test it?

 

[lezawang]

Thank you, yes you are right. I do not have sheet2 inside Personal project. Please see my project
screen shot below

Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

Now I want to know how can I create Sheet2 under Personal so I can run that code?
Thank you once again

As I have stated ThisWorkbook is the workbook that that holds the code and so you are asking the macro to run on the Personal workbook and not the workbook that is active.
You are probably getting the error message because you don't have a Sheet2 in the Personal workbook.



Rather than ask why not test it?

 

[MARK858]

You don't run the code on the personal workbook, you put the code in the personal workbook but it runs on either the active workbook or a specific workbook and you change the code to suit.

I think you either don't understand the purpose of the personal workbook or don't understand how you use it.

 

[MARK858]

Going back to your question in your first post...

Why the author can use "thisWorkbook" but I can not?

Because he hasn't got the code in the personal workbook, he has it in a regular workbook called "ExcelVBAIsFun lesson 8.xlsm"

If you had told us from the start that you had the code in the personal workbook I wouldn't have suggested ThisWorkbook I would have suggested ActiveWorkbook as it is most common to run the code on the ActiveWorkbook as below...

Sub test()
  ActiveWorkbook.Sheets("sheet2").Visible = False
  ActiveWorkbook.Sheets("sheet2").Visible = True
End Sub

The personal workbook is purely there to hold macros to run on other workbooks and so you wouldn't use ThisWorkbook.

Hopefully it is clearer now.

 

[lezawang]

Thanks a lot for your help. You are right. I started using Personal workbook because I recorded Marcos and then started editing them and delete the whole code and start over. You are right I am not fully understanding Personal workbook. I will read your reply again and again to get better understating. thanks once again.

Going back to your question in your first post...



Because he hasn't got the code in the personal workbook, he has it in a regular workbook called "ExcelVBAIsFun lesson 8.xlsm"

If you had told us from the start that you had the code in the personal workbook I wouldn't have suggested ThisWorkbook I would have suggested ActiveWorkbook as it is most common to run the code on the ActiveWorkbook as below...

Sub test()
  ActiveWorkbook.Sheets("sheet2").Visible = False
  ActiveWorkbook.Sheets("sheet2").Visible = True
End Sub

The personal workbook is purely there to hold macros to run on other workbooks and so you wouldn't use ThisWorkbook.

Hopefully it is clearer now.