Code that ask user for a password before it runs..

[KimberlyC]

Hi,

I'm using this code below to delete some unused worksheets in the active
workbook.
I only want certain users to be able to use this feature..
so..
Is there a way to add code to this code below...that would ask the user for
a password....maybe in a userform ...and then if they type in the correct
password, the code below runs..and if they type in an incorrect
password..this code does not run.


Sub DeleteBlankSheets()
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
If sht.Range("A3") = "Delete" Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If

Next

End Sub

Any help is greatly appreciated!!
Thanks in advance,
Kimberly

 

[Paul B]

Kimberly, try this, you must lock VBA project so you can't see the password
in it


Sub DeleteBlankSheets()

Dim sht As Worksheet
Dim MyStr1 As String, MyStr2 As String
MyStr2 = ("123") '*****This is the password
MyStr1 = InputBox("Password Is Required To Run this Macro ")
If MyStr1 = MyStr2 Then

For Each sht In ActiveWorkbook.Worksheets
If sht.Range("A3") = "Delete" Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If

Next
Else
MsgBox ("Access Denied")
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[N10]

Sub DeleteBlankSheets()
Dim sht As Worksheet
dim ans as string
dim password as string

password = "pumpkin123"

ins = inputbox ( "PLEASE ENTER PASSWORD")
if ans is <> " pumpkin123" then end


For Each sht In ActiveWorkbook.Worksheets
If sht.Range("A3") = "Delete" Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If

Next

End Sub

 

[STEVE BELL]

And you can do something similar with a user form with a text box.

Except you can make the fore (font) color and back color both white, or
black, or ....
so that no one can see what is being typed.

Used to know how to make the font appear as *****, but can't find my
reference
maybe some one else can remember how to do that...

 

[Alan]

And you can do something similar with a user form with a text box.

Except you can make the fore (font) color and back color both white,
or black, or ....
so that no one can see what is being typed.

Used to know how to make the font appear as *****, but can't find my
reference
maybe some one else can remember how to do that...

One way would be to dynamically replace the password as it is typed
with actual * symbols.

You'd have to store the password in a variable I guess.

I suspect the screen would flicker annoyingly too.

Just an idea.

Alan.

 

[Gareth]

To add to this (and answer Steve's ponderence):

If you set the PasswordChar property of the TextBox to "*", for example,
it will mask out the text they type - just like a grownup program!

e.g. TextBox1.Passwordchar = "*"

 

[STEVE BELL]

Gareth,

Thank you very much! Knew it was there (someplace) but didn't think of
looking at Properties.... (duh, me)

 

[Alan]

One way would be to dynamically replace the password as it is typed
with actual * symbols.

You'd have to store the password in a variable I guess.

I suspect the screen would flicker annoyingly too.

Just an idea.

Alan.

Scrap that - use Gareth's way!

See here:


Alan.

 

[KimberlyC]

This worked well...
Thank you!!!

Kimberly, try this, you must lock VBA project so you can't see the password
in it


Sub DeleteBlankSheets()

Dim sht As Worksheet
Dim MyStr1 As String, MyStr2 As String
MyStr2 = ("123") '*****This is the password
MyStr1 = InputBox("Password Is Required To Run this Macro ")
If MyStr1 = MyStr2 Then

For Each sht In ActiveWorkbook.Worksheets
If sht.Range("A3") = "Delete" Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If

Next
Else
MsgBox ("Access Denied")
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[KimberlyC]

Thank you all for your help!!
I will try the userform method as well..
I like the ****

 

[Paul B]

Your welcome

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003