Macro that will only run if certain criteria are met needs a password [SOLVED]

[Cammandk]

I have a MONTH END Button on a worksheet. I have recorded a macro MONTHEND to do what I want to happen when the button is clicked.


I only want this to be able to run if a range T1:T3 all equal TRUE


If the button is clicked I need a password CPL to be entered for it to run.


What code is needed for this and then where is it actually put?


Thanks

 

[Luke M]

This should get you started

[pre]

Sub TestAndPass()
'Define the password
Const myPass = "CPL"

Dim myRange As Range
'Criteria range
Set myRange = ActiveSheet.Range("T1:T3")

If WorksheetFunction.CountIf(myRange, True) = myRange.Cells.Count And _
InputBox("What is the password?", "PASSWORD") = myPass Then

'Do something
Else
MsgBox "Criteria not met"
End If

End Sub

[/pre]

 

[Cammandk]

Thanks.

Where do I actually copy this into?


Sorry newbie to this.

 

[Cammandk]

I have managed to get this working.

What would be really useful is if the 1st Message was "Condition Not True" if all the criteria are not meet and then the 2nd Message was asking for the password.

 

[Luke M]

Sorry about that, should have stated that this needs to go in a VBE module. How about this one?

[pre]

Sub TestAndPass()
'Define the password
Const myPass = "CPL"

Dim myRange As Range
'Criteria range
Set myRange = ActiveSheet.Range("T1:T3")

If WorksheetFunction.CountIf(myRange, True) = myRange.Cells.Count Then
If InputBox("What is the password?", "PASSWORD") = myPass Then

'Do something
Else
MsgBox "Password incorrect"
End If
Else
MsgBox "Condition Not True"
End If

End Sub

[/pre]

 

[Cammandk]

Not able to try this yet but feels like order not quite right still:


1. Try to run macro - click button

2. If criteria not true - message Criteria not True - stop macro

3. If criteria true - ask for password

4. If password not correct - message Password not correct - stop macro

5. If criteria true and password correct - run macro

 

[DaveTurton]

Hi Cammandk


Luke's code looks fine to me.


The first If condition is checking if the range equals True, if it does it moves to the password, if not it displays a message saying condition not true and the sub ends.


If condition met then a Input box opens asking for a password to be entered, if correct the "'do something" macro will run, if incorrect a message will be displayed saying Password incorrect and once again the sub will end.


I think you are reading the code top to bottom and expecting everything to be in the order as per your post. However with the code, it is checking a condition and then doing something if correct, what happens if not correct comes afterwards.


I suppose you could put a If Not condition in, which would switch it around but the end result will still be the same.

 

[Cammandk]

Hi Dave / Luke

Just got to try this and yes it works - I will try and understand the logic better.

Many thanks all.


SOLVED.