Disable running macros from the "View Macro" screen until VBA project password is entered?

So first, when one clicks on the "View Macro" button, this pops up:

What I want to know is, is there some code that I can run on workbook open (and then "unrun" on workbook close) that grays out that run button (like the others underneath it are) ONLY until the password is entered in the VBA project (using Alt+F11 to open the editor)?

I don't want the users to be able to run any of these subs manually.

Answers


If you declare the sub so that it needs input, even optional input it will not show in the list either.

sub Test(optional a as string)

Declare the subs as private and they won't show up in the Alt+F8 dialog box.

Declare them as public (the default) and they will.


You can use vba to edit the vba code of another module.

Is it possible in Excel VBA to change the source code of Module in another Module

You can change one line or search through the lines and comment/uncomment whole blocks of code. Capturing the event when vba is unlocked may be the hard part. You may have to run a sub that does this after unlocking vba.


I think you have the wrong approach to this and it would be better to structure your code more properly.

The first two on that sheet are called from other macros that are run with buttons on my main worksheet.

OK. So attach these to a form control/button, and use Bigtree's suggestion to include an optional argument in these subs. They will not display in the Macros menu. YOU can run them at least three different ways:

  • either from the VBE by finding the procedure and pressing F5, or
  • by entering the name of the procedure in the Immediate window in the VBE, or
  • by pressing the buttons you have provided.

The middle two are called when the sheet opens and closes

Sounds like this should be a private subroutine (or, use the method above from Bigtree) and CALL these from the one or more of the appropriate event handlers (at the worksheet level perhaps: Worksheet_Activate, Worksheet_Deactivate; or at the workbook level SheetActivate and SheetDeactivate depending on your needs)

You can always run the procedure manually from the Immediate window in VBE, or by manually invoking the event procedure, etc.

and the last two I manually call when I want to edit my main sheet

Again, call from the Immediate window or manually from the VBE. You only have 6 subroutines here, it can't be that difficult to locate the ones you frequently need. Put them in a new module and organize your modules so you know where these are.

Alternatively, put some boolean in the subroutine like:

Sub SheetLock()

If Not Environ("username") = "YOUR_USERNAME" Then Exit Sub

'other code to do stuff is below...

End Sub

UPDATE FROM COMMENTS

The Immediate Window is like a console/command line, it is where the result of your Debug.Print statements would appear, etc. It is very handy for debugging and evaluating expressions.

There are several ways you could invoke the subroutine depending on whether it is public/private, including

  • Application.Run "MacroName" or simply MacroName if public
  • Application.Run "ModuleName.MacroName" or Call ModuleName.MacroName if private

I did not want to use a private sub, I used the shape name to determine if from a button

On Error GoTo noshapeselected shapeis = ActiveSheet.Shapes(Application.Caller).Name

' I manually set the shape name in the page layout tab selection pane ' below I test for the desired button If shapeis = "your button name" then goto oktogo else goto noshapeselected endif

noshapeselected: msgbox ("must run from a button") goto theendsub

oktogo: 'continue if desired shape was selected

theendsub: 'a button was not pushed

For those macros without buttons, but called by another macro, set a variable to 'OK' to run a macro, then the called macro tests for 'OK' ('OK' means initiated by another macro 'NOK' means not initiated by another macro)


Need Your Help

Code is not incrementing locations needed

ibm-midrange db2-400 rpgle

I have an sqlrpg program which is supposed to generate warehouse locations, 8 positions, in a loop so that we will have rows, aisles,bins, as: but code is only producing the first row below, and is...

Split a String to a String[] so that each element is maximum 100 characters and ends in a space

java arrays string split character

How do i do this? I tried something like this but I do not seem to be able to get any further.