Macro assistance

[Rover]

I created a macro and it works. How can I make sure only a few people can
run the macro? I have a file that goes out to 30 people for budgets inputs.
I only want my group to be able to run that particular macro, not people
outside my group.

 

[Hansueli]

Hi Rover,

You can check whether the username is in the group you want to allow to run
the macro, according to this example:

Sub YourMacro()
Const arrAllowedUsers = "Rover;Peter;Sue;Mary"
If InStr(arrAllowedUsers, Environ("username")) = 0 Then
MsgBox "You are not allowed to run this macro!", vbOKOnly + vbCritical
Exit Sub
End If
...continue with your code
End Sub

Note: the environ("username") returns the WINDOWS username from the
Win-Logon.

 

[Dave Peterson]

At the start of each macro:

Sub Mac1()

dim pwd as string
pwd = inputbox(prompt:="what's the password, Kenny?")
if pwd <> "somethingyouonlysharedwithtrustedpeople" then
msgbox "You can't run this"
exit sub
end if

'real code here
end sub

And protect your code in the VBE so people can't just open the VBE and see the
password.
Select the project
Tools|vba project properties|Protection tab

And don't share the password with people who may share it with others.

====
This kind of thing never works. The password will escape your control very
quickly.

 

[Dave Peterson]

But Pete could run this, too.

Maybe surrounding the names with semicolons would help

Const arrAllowedUsers = ";Rover;Peter;Sue;Mary;"
If InStr(arrAllowedUsers, ";" & Environ("username") & ";") = 0 Then

But it isn't unusual for two people to share the same name.
Like: Joe Smith

 

[Shane Devenshire]

Hi,

Just to be cute and instructive:

If you make the macro a Private Sub. Let's suppose called Test, then you
special users can run the macro with Alt+F8 and type the name of the macro
into the box and press enter. Other users can't see the macro so they are
unlikely to try to type its name.

 

[Rover]

Here are my two macros: They basically protect and unprotect many sheets in
a workbook.

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="plan"
Next ws
End Sub


Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.UnProtect Password:="plan"
Next ws
End Sub

What do I need so only Rover, Peter, Sue and Mary can only run the macro.

I am new to macros, and I appreciate your assistance.

 

[Hansueli]

Hi Dave,
I don't see the advantage of the semicolons.
In a windows network no 2 users can have the same username! and it is the
windows logon-name that is passed back by the environ("username") function!

Rgds. Hansueli

 

[Hansueli]

Hi Rover,

'This code into the ThisWorkbook:

Private Sub Workbook_Open()
Const arrAllowedUsers = "Rover;Peter;Sue;Mary"
If InStr(arrAllowedUsers, Environ("username")) > 0 Then _
bMacroAllowed = True
End Sub


'This code into a module:

Option Explicit
bMacroAllowed As Boolean

Sub Protect_Selected_Sheets()
If Not (bMacroAllowed) Then Exit Sub
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Protect Password:="plan"
Next ws
End Sub


Sub UnProtect_Selected_Sheets()
If Not (bMacroAllowed) Then Exit Sub
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Unprotect Password:="plan"
Next ws
End Sub

Rgds. Hansueli

 

[Rover]

where in the workbook do I type the code?

Also, do I enter
Option Explicit
bMacroAllowed As Boolean
in both of the macros? where in the macro module do i enter it? before
each macro or after each macro.

thanks again...

 

[Hansueli]

Hi Dave,

Now I see the point with the semicolons. Peter = Pet, Pete, Peter....
You are right so:
@Rover, correct the CONST string and the IF in my code according to dave's
suggestion.

Rgds. Hansueli

 

[Dave Peterson]

How about this instead...

Don't put the macro in the workbook with the data.

Put the macro in a separate workbook (maybe save it as an addin).

Share the data workbook with everyone.

Share the macro workbook with only the people you trust -- and tell them not to
share with anyone else!

Then give the user someway to run the macros--especially if you saved the file
as an addin:


For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

 

[Hansueli]

'In then VBEditor-Project-Explorer doubleclick ThisWorkbook
'and copy this code into the empty window:

Private Sub Workbook_Open()
Const arrAllowedUsers = "Rover;Peter;Sue;Mary"
If InStr(arrAllowedUsers, Environ("username")) > 0 Then _
bMacroAllowed = True
End Sub

'In then VBEditor-Project-Explorer rightClick Module - Insert - Module
'and copy this code into the empty window:

Option Explicit
Public bMacroAllowed As Boolean

Sub Protect_Selected_Sheets()
Dim ws As Worksheet
If Not (bMacroAllowed) Then Exit Sub
For Each ws In ActiveWindow.SelectedSheets
ws.Protect Password:="plan"
Next ws
End Sub

Sub UnProtect_Selected_Sheets()
Dim ws As Worksheet
If Not (bMacroAllowed) Then Exit Sub
For Each ws In ActiveWindow.SelectedSheets
ws.Unprotect Password:="plan"
Next ws
End Sub


Remove your 'old' Module by rightclick and remove from the context-menu

Rgds. Hansueli