prevent users from opening the excel vba window 2010

How do i allow certain users to access the excel vba window.The easiest approach would be hide/unhide the developer tab based on sign on.How do i accomplish this ?
Thanks
SvgmassiveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
Hide/ Unhide the tab will not stop the use of [ALT]+[F11] so not much use.

To restrict access to the macro, apply a Password.
This is done using Tools > Properties > Lock Project for Viewing.
1
Daniel Van Der WerkenIndependent ConsultantCommented:
If I were doing this, I would want to figure out if there is some registry setting that would work for keeping the user out of viewing the developer tab. A quick search results in;

<<Link to competitive Q&A site removed per site policy--byundt, Microsoft Excel Zone Advisor>>
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\General\DeveloperTools

Which, when set to zero, may keep the user from opening the tab.

If this works, you can set this on the user's system and then open regedit and lock the user from being able to modify this registry value.
0
SvgmassiveAuthor Commented:
i don't want to lock this can be undone with code.disable the developer tools might be the best approach
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Steven HarrisPresidentCommented:
As The_Barmen said, you can hide the Developer Tab and still have access to the VBA Console via Alt+F11

If you are wanting to protect a macro, you can use the following (from Mr. Excel) :

'// Run DisableGettingIntoVBE from a Event procedure
'// eg. Workbook_Open or Worksheet_Activate

'// THEN run EnableGettingIntoVBE from the Opp Event procedure
'// eg. Workbook_Close or Worksheet_Deactivate

Sub DisableVBE()
   Application.VBE.MainWindow.Visible = False '// Close ALL windows 1st!
   CmdControl 1695, False                     '// Visual basics Editor
   CmdControl 186, False                      '// Macros...
   CmdControl 184, False                      '// Record New Macro...
   CmdControl 1561, False                     '// View Code
   CmdControl 1605, False                     '// Design Mode
   Application.OnDoubleClick = "Dummy"
   CommandBars("ToolBar List").Enabled = False
   Application.OnKey "%{F11}", "Dummy"
End Sub

Sub EnableVBE()
   CmdControl 1695, True                    '// Visual basics Editor
   CmdControl 186, True                     '// Macros...
   CmdControl 184, True                     '// Record New Macro...
   CmdControl 1561, True                    '// View Code
   CmdControl 1605, True                    '// Design Mode
   Application.OnDoubleClick = ""
   CommandBars("ToolBar List").Enabled = True
   Application.OnKey "%{F11}", ""
End Sub

Sub CmdControl(Id As Integer, TF As Boolean)
  Dim CBar As CommandBar
  Dim C As CommandBarControl
  On Error Resume Next
  For Each CBar In Application.CommandBars
    Set C = CBar.FindControl(Id:=Id, recursive:=True)
    If Not C Is Nothing Then C.Enabled = TF
  Next
End Sub

Sub Dummy()
    '// NoGo, leave blank OR Display a message eg.
    'MsgBox "Sorry you this command is NOT available", vbCritical
End Sub

Open in new window

0
SvgmassiveAuthor Commented:
I need  a vba function to hide and unhide the developer tab.thinks space i have something like the code you have listed in 2010 they just click on the vb icon and the window opens.That's what i am trying to avoid.Thanks
0
Steven HarrisPresidentCommented:
The code provided above disables the option within the Developer Tab:  

CmdControl 1695, False                '// Visual basics Editor
CmdControl 186, False                   '// Macros...
CmdControl 184, False                  '// Record New Macro...
CmdControl 1561, False                 '// View Code
CmdControl 1605, False                '// Design Mode

Application.OnKey "%{F11}", "Dummy" disables the Alt+F11 key sequence.

Even though the tab may remain in place in some systems, you cannot do anything with the VB functions.  In that case, hide the Dev Tools like this:

Application.ShowDevTools = False

Open in new window


Turn it back on:

Application.ShowDevTools = True

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
DisableVBE prevents some, but not all, of the ways to view code in the VBA Editor. The following means still work in Excel 2013 after running that macro:
Developer...View Code
Developer...Visual Basic
Right-click a sheet tab and choose View...Code
Developer...Design Mode, followed by right-clicking an Active-X control and choosing View Code

Rather than trying to prevent all means of getting into the VBA Editor, a different approach would be to make its windows hidden and locked. I still need to figure out how to activate the code pane instead of the object browser.
'Put in a regular module sheet
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal ClassName As String, ByVal WindowName As String) As Long
    
Private Declare Function LockWindowUpdate Lib "user32" _
    (ByVal hWndLock As Long) As Long


Sub EliminateScreenFlicker(b As Boolean)
Dim VBEHwnd As Long

VBEHwnd = FindWindow("wndclass_desked_gsk", Application.VBE.MainWindow.Caption)
If b = True Then
    LockWindowUpdate 0&
Else
    Application.VBE.MainWindow.Visible = False
    If VBEHwnd Then
        LockWindowUpdate VBEHwnd
    End If
End If
End Sub
    
Sub ToggleVBA()
Static bVisibleVBA As Boolean
Dim VBAEditor As VBIDE.VBE
Dim i As Long, n As Long

If bVisibleVBA Then EliminateScreenFlicker True
Set VBAEditor = Application.VBE
n = VBAEditor.Windows.Count
For i = n To 1 Step -1
    VBAEditor.Windows(i).Visible = bVisibleVBA
Next
If bVisibleVBA = True Then
    VBAEditor.Windows("Locals").Visible = False     'Don't show Locals pane
    VBAEditor.Windows("Watches").Visible = False    'Don't show Watches pane
Else
    EliminateScreenFlicker False
    ThisWorkbook.Windows(1).Activate
End If

bVisibleVBA = Not bVisibleVBA
End Sub

Open in new window


Code to set a reference to VBA Extensibility:
'Goes in ThisWorkbook code pane
Private Sub Workbook_Open()
'Add a reference to VBA Extensibility when workbook opens
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3
On Error GoTo 0
End Sub

Open in new window

0
SvgmassiveAuthor Commented:
GREAT
0
byundtMechanical EngineerCommented:
ThinkSpaceSolutions,
I'm testing your DisableVBE macro in the attached workbook, and it is working in Excel 2003. In Excel 2007, 2010 and 2013, it kills ALT + F11 (as expected) but fails to stop the other means of seeing the VBA code :
Developer...View Code
Developer...Visual Basic
Right-click a sheet tab and choose View...Code
Developer...Design Mode, followed by right-clicking an Active-X control and choosing View Code

What am I doing wrong?

Brad
HideVBAwindowsQ28228629.xlsm
0
byundtMechanical EngineerCommented:
I've simplified my code that hides the VBA Editor windows. It was included in the sample workbook posted in my previous Comment, and is working in Excel 2003 through 2013. You do need to check the box for "Trust access to VBA project object model" in Tools...Options...Trust Center...Trust Center Settings...Macro Settings" (wording and means of getting to the setting varies with Excel version).

You also need to set a reference to Microsoft Visual Basic for Applications Extensibility in the VBA Editor Tools...References menu item. The Workbook_Open sub in my opening Comment does that for you when the workbook opens.

The following macro toggles the visibility of the VBA Editor menu & panes. Run it once, and the menu and code panes are hidden. Run it again and they are made visible. I could easily recast the code in two subs, one to enable and the other to disable, but thought the toggle approach would be better for testing. Note that the Windows API function references must appear before any subs or functions, i.e. at the top of a regular module sheet.
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal ClassName As String, ByVal WindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "user32" _
    (ByVal hWndLock As Long) As Long

Sub ToggleVBA()
'Toggles VBA visibility in Excel 2003, 2007, 2010 and 2013
Static bVisibleVBA As Boolean
Dim VBAEditor As VBIDE.VBE
Dim i As Long, n As Long, VBEHwnd As Long

VBEHwnd = FindWindow("wndclass_desked_gsk", Application.VBE.MainWindow.Caption)
Set VBAEditor = Application.VBE
n = VBAEditor.Windows.Count

For i = n To 1 Step -1
    VBAEditor.Windows(i).Visible = bVisibleVBA
Next
If bVisibleVBA Then
    LockWindowUpdate 0&
    VBAEditor.Windows("Locals").Visible = False     'Don't show Locals pane
    VBAEditor.Windows("Watches").Visible = False    'Don't show Watches pane
    VBAEditor.Windows("").Visible = False           'Don't show the General pane
    VBAEditor.Windows("Object Browser").Visible = False           'Don't show the Object Browser pane
Else
    Application.VBE.MainWindow.Visible = False
    VBEHwnd = FindWindow("wndclass_desked_gsk", Application.VBE.MainWindow.Caption)
    If VBEHwnd Then
        LockWindowUpdate VBEHwnd
    End If
End If

bVisibleVBA = Not bVisibleVBA
End Sub

Open in new window

0
Steven HarrisPresidentCommented:
This is an old code I dug up from our initial 2003 deployment.  We have redeployed using 2010 (which includes Add-Ins and COM objects).  Upon looking closer, I realized that our file  has a custom XML string which redirects the CommandBarControl method up to the newer 'Ribbon' interface.  Guess I should download the UI Editor and start looking around in this one...

More info on the XML route here.

That aside, I believe the OP went for the
Application.ShowDevTools = False

Open in new window

control to just hide the Tab itself, if that is part of your confusion.
0
byundtMechanical EngineerCommented:
I added Application.ShowDevTools = False to Ivan Moala's code from Mr. Excel. That does close most of the remaining methods of viewing the VBA code, but still fails to prevent the following methods:
Right-click a sheet tab, then choose View Code
Right-click a Forms control (or a Shape object), choose Assign Macro, then click the Edit button
ALT + F8, then click the Edit button

I agree that CustomXML can be used to edit the ribbon, but you'll need to build your own groups if you want to turn features on and off programmatically. It gets somewhat fiddly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.