Hidden VBA Code

  • Thread starter Thread starter Brad E.
  • Start date Start date
B

Brad E.

Hi there,

BACKGROUND - A while back, I was trying to change my "Comma Style" button to
format cell(s) in the #,##0 format rather than the built-in #,##0.00_)
format. I was trying to change this button via a Macro in my Book.xltm file,
so when Excel opened, the button would act as I want.

As a test to see if I was putting the Macro in the correct place and to see
if it was working when I opened Excel, I put a Msgbox in the code to give me
the "Hi" message. It didn't end up working correctly and I left it alone.
There was a Comma button on my Add-Ins menu, and it would display the "Hi"
message when pushed, but the cell format wouldn't change. I have since
right-clicked on that Comma button and deleted it. (I have also gotten the
comma thing to work the way I want, so I don't need help with that.)

CURRENT - I wrote some code last week and was testing it before sending the
spreadsheet out for my contact to work on it. When I push a button that I
have entered on the spreadsheet (HOME tab), I want to record the user's
entered data on a second tab (HOME2). If HOME2 is not hidden, the code works
as intended. However, when I hide HOME2, my code doesn't run past the
Worksheets("HOME2").Select
line, but, instead, I get the "Hi" message from that old code I had. I have
realized that I need to change that line to
With Worksheets("Home2").

My problem is that I cannot find that old code ANYwhere to be able to delete
it. I had my co-worker test the spreadsheet on his computer and he also got
the "Hi" message, which means when I e-mail it to my contact for user input,
they will get the message also. Obviously, I can't have that. Can anyone
help me find that old code?
 
If you open the VB editor and look in the Project window, the small window
at top left of the screen, you will see all of the items in that workbook's
project(s). Do not include Funcres if it appears there because that is for
the basic system. On all the others you can right click and then select
view code. This will display the code module for the item clicked. You
should have, as a minimum, Module1, Sheet1 and ThisWorkbook. Standard
workbooks will also have Sheet2 and Sheet3. If you have added UserForms,
they will also appear there and they will have their own code modules which
can be checked the same way. If you have checked all of these and still
can't find the code, it is not in that workbook.
 
Thanks, JLGWhiz,

The left window of my Visual Basic page is titled "Project - VBAProject".
It displays:
- VBAProject (Review.xlsm)
- Microsoft Excel Objects
Sheet1 (AUTO)
Sheet2 (HOME)
Sheet3 (AUTO2)
Sheet4 (HOME2)
ThisWorkbook

None of these have the Msgbox code which I am concerned about. There is no
Module1, as you indicated. Also, there are no other spreadsheets currently
open.

Alternatively, is there a way to stop execution when the "Hi" Msgbox
displays (OK and Help buttons on the Msgbox) in a way in which the code which
I break would display? This would show me the code and where it is at.
 
When the message box appears, press <Ctrl + Break>. That will set the debug
trigger so that if you click OK or the Close icon, it should take you to the
code that is causing the message to display. It is possible that it is being
produced from an autostart macro in a Personal.xls file that is normally
stored in the Excel StartUp folder. Using the Ctrl + Break should find it
for you.
 
Ctrl + Break does not take me to any code, it just closes the Msgbox.

Personal.xls rings a bell. I remember playing with the Personal.xls file
regarding that Comma Style change. I believe I ended up deleting
Personal.xls after I found an easier way to set up the comma thing. I have
searched and searched for Personal.x and it doesn't show up anywhere
(possibly because I am using Excel 2007??). That code which is producing the
"Hi" message is still hidden somewhere though!! Any other ideas?
 
I am curious about the Ctrl + Break not taking you to the active code. Try
it with Ctrl + Alt + Break and see if you get the same results. If you do,
then the code is not VBA, but probably a compiled code. If your system is a
company workstation, it is possible that someone else has loaded a file. If
it is a home system, I don't know what to tell you.
 
JLGWhiz - I am on a Company machine. Ctrl-Alt-Break does nothing, either.
Is there a way to check if it is compiled code? If so, would you have any
input for de-compiling? If not, I appreciate all the help today.

Could you do me a favor? Close and restart Excel. With at least 2 tabs
(i.e. "Sheet1" and "Sheet2") right-click on Sheet1 and View Code. Simply
insert the following:
Sub Test1()
Worksheets("Sheet2").Select
End Sub
Of course this just puts the focus to Sheet2 when run from any sheet. But
what happens on your computer when you hide Sheet2 and run this code from one
of the other sheet(s)? It is at this point we are getting the Msgbox coming
up.

If I e-mail this file out, will that code pass to other's computers?
 
Sub Test1()
Worksheets("Sheet2").Select
End Sub

This code, as written does nothing unless activated manually. With sheet2
hidden you get an error message telling you that the select method for
sheet2 failed. Excel will not allow you to select a hidden sheet.

If you attach an Excel file to an Email, the entire file is transmitted,
including any code contained in that file. However, if the file is linked
to an external source, the external source is not transmitted and the user
will get error messages for any activity that requires the external link to
complete an operation.
 
One last shot. You mentioned that you were working with a template at the
time you created the message box. Check your template folder for the old
file. That could be where the code is coming from for some unknown reason.
I would have to see how the code is written to believe it, but it is the
only other place I can think of to look.
 
Back
Top