cell entry change to start a macro

[Rob Mann]

please can someone help.
in cell A1 i have a drop down list with 4 peoples names in. when someone picks a name (cell changes from blank, to a name) i want to run a macro. how do i do it. ppppplllllleeeeeeaaaaassssssseeeeee help

 

[VoG]

First change A1 to =TODAY()

Then in B2, copied down

=IF(A2=A$1,"Yes","")

 

[Rob Mann]

thank you.

ive just created a macro, do you think it will work.

most of the macro ive used in another workbook but on this one i need excel to close after running the macro.

Sub WORKBOOK_OPEN()
Sheets("sheet1").Visible = True
Sheets("sheet1").Select
If Time <= TimeValue("05:00 am") Then
Call SendEMail3
End If
Application.Wait (Now + TimeValue("0:05:00"))
Application.ActiveWorkbook.Close
End Sub

 

[VoG]

That should work. If you actually want Excel to close you will need the Application.Quit line

Sub WORKBOOK_OPEN()
Sheets("sheet1").Visible = True
Sheets("sheet1").Select
If Time <= TimeValue("05:00 am") Then
    Call SendEMail3
End If
Application.Wait (Now + TimeValue("0:05:00"))
ActiveWorkbook.Close savechanges:=True
Application.Quit
End Sub

 

[Rob Mann]

thats perfect, thanks

is it possible to password protect a button that runs a macro?

 

[VoG]

Try like this

Private Sub CommandButton1_Click()
Dim pw As String
pw = InputBox("Enter password")
If pw <> "Rob" Then Exit Sub
'
'rest of code here
'
End Sub

 

[Rob Mann]

good morning

If I wanted to call "SendEmail4" if any of cell range "W2:W52" changed to "YES" how would I add it into the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G3:G52")) Is Nothing And Target.Value <> "" Then
Call SendEMail1
ElseIf Not Intersect(Target, Range("M3:M52")) Is Nothing And UCase(Target.Value) = "YES" Then
Call SendEMail2
End If
End Sub

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G3:G52")) Is Nothing And Target.Value <> "" Then
    Call SendEMail1
ElseIf Not Intersect(Target, Range("M3:M52")) Is Nothing And UCase(Target.Value) = "YES" Then
    Call SendEMail2
ElseIf Not Intersect(Target, Range("W3:W52")) Is Nothing And UCase(Target.Value) = "YES" Then
    Call SendEMail4
End If
End Sub

 

[Rob Mann]

in the code below is there a way of when "M3" is changed to "YES" that it prints the defined print area of "sheet2". And when "M4" is changed to "YES" it prints the defined print area of "sheet3" and so on and so on up until "M52"

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G3:G52")) Is Nothing And Target.Value <> "" Then
Call SendEMail1
ElseIf Not Intersect(Target, Range("M3:M52")) Is Nothing And UCase(Target.Value) = "YES" Then
Call SendEMail2
ElseIf Not Intersect(Target, Range("W3:W52")) Is Nothing And UCase(Target.Value) = "YES" Then
Call SendEMail4
End If
End Sub

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G3:G52")) Is Nothing And Target.Value <> "" Then
    Call SendEMail1
ElseIf Not Intersect(Target, Range("M3:M52")) Is Nothing And UCase(Target.Value) = "YES" Then
    Call SendEMail2
    Sheets("Sheet" & Target.Row - 1).PrintOut
ElseIf Not Intersect(Target, Range("W3:W52")) Is Nothing And UCase(Target.Value) = "YES" Then
    Call SendEMail4
End If
End Sub

 

[Rob Mann]

i guess the ("Sheet" & Target.Row -1). PrintOut mean look at the row number then subtract 1 and go to that sheet?

row 3 -1 = sheet 2.

i'm asking because i forgot that i have hidden sheet 2. so i need row 3 to print "sheet3"

how should I change the code?

i deleted the "- 1" part but i got an error.