VBA is working on everybody's PC but mine, all of a sudden.

[shamsul]

Hello,

I am cross-posting from other thread I posted : http://www.ozgrid.com/forum/showthread.php?t=201925

Basically I have found the solutions, but it is still happening intermittently. Today alone I had to redo the steps in here:http://exceleratorbi.com.au/excel-ke...your-vba-code/ FOUR TIMES!

1. I only have VBA code in my Workbook (WB), not in PERSONAL.XLSB. From my observation, every time my WB crashed, Excel asked me to repair a file named WB (version 1).XLSB. I clicked yes, then I go the error message:

"the workbook WB(version 1).XLSB cannot be opened or repaired by microsoft excel because it is corrupted"

I also noticed that all of a sudden Excel created "WB (created 1).XLSB" file in the folder C:\Users\"USERNAME"\AppData\Roaming\Microsoft\Excel. How did this happen?

2. What can I do to trace back which one of my code is corrupted?

3. This thing only happened to me, and not my colleague. We are using the same version of Microsoft Excel.

p/s" I noticed that every time I open Microsoft Excel, my PERSONAL.XLSB also will automatically opens. I do not know if it helps me to find a solution here.

[kev_]

To stop personal.xlsb from opening

Activate personal.xlsb
Click on VIEW tab
Then HIDE and select personal.xlsb
Then save the file and exit Excel

Now open the file with the VBA problem (hopefully personal.xlsb is not visible) and run your macro a few times.

If this makes your problem go away, please come back and tell us.

If this does not make your problem go away, please post your code and attach your workbook

[shamsul]

To stop personal.xlsb from opening

Activate personal.xlsb
Click on VIEW tab
Then HIDE and select personal.xlsb
Then save the file and exit Excel

Now open the file with the VBA problem (hopefully personal.xlsb is not visible) and run your macro a few times.

If this makes your problem go away, please come back and tell us.

If this does not make your problem go away, please post your code and attach your workbook

I will try this as soon as the problem comes back.

Unfortunately, it is a propriety company spreadsheet and I am not allowed to share it here. But I can tell you that:

1. I used Stephen Bullen's code to make photos appear on my userform.
2. Most of my code lines are something along the line of TextBoxXXX.Value = Sheets("SheetXX").Range("XX").Value.
3. I have a line of codes that call up the user's login ID. If the login ID is not in the list, the user will get MsgBox saying "User Not Authorized"

Please Login or Register  to view this content.

Now that I mentioned it, I think the problem occurred more frequently after I added this code.

[kev_]

I have had exactly this problem with User Forms
- vba crash, a totally meaningless error message, followed later by discovery of file corruption.
When it happened I did not realise that the file had been corrupted until days later.
I kept getting niggly little problems which appeared insignificant and a bit random in occurrence
Once I realised that the file had a corruption there was no choice but to start again with a "sterile" workbook and build from scratch.

I suspect that your problem lies somewhere in your User Form code - perhaps Debug.Print (see below) can help you identify what the problem is.


It is unfortunate that you are unable to share much with us.
It is difficult to help without more detailed information.
But perhaps, with a bit of help, you can puzzle it out yourself.

If you cannot see the "Immediate Window" in VBA, click on VIEW and then select Immediate Window.
This allows VBA to print helpful information to screen.
ImmediateWindow.jpg

The command Debug.Print is very useful. It is used like MsgBox, but it does not interrupt the program
If a line of code fails, it may be because it is not getting the type of value it expects.
What can be useful is to check variables and cells for their value (or type of data) at various points in the VBA.
See code below amended to include 2 such statements
Have a go, see what you discover.

Please Login or Register  to view this content.

Report back if you find something and someone may be able to point you in the right direction.

Kev

[shamsul]

To stop personal.xlsb from opening

Activate personal.xlsb
Click on VIEW tab
Then HIDE and select personal.xlsb
Then save the file and exit Excel

Now open the file with the VBA problem (hopefully personal.xlsb is not visible) and run your macro a few times.

If this makes your problem go away, please come back and tell us.

If this does not make your problem go away, please post your code and attach your workbook

It happened again yesterday I did this. It WORKED. I can't thank you enough! You saved my life.