D
Dan Wood
I have two macros to run when somebody changes my excel sheet. The first one
is assigned to a button, and just checks the spreadsheet for field "A/L" :-
Sub RunIf()
For Each c In Range("C3:F14")
If c.Value = "A/L" Then
Call Add_Appointment
End If
Next
End Sub
Question 1) Will this script look at every field from c3 to f14 eg c3, c4,
c5, c6 etc.
Question 2) Is there a way to update this script to only pick up amendments,
or will this need to be in my second macro?
My second macro creates an appointment within outlook calender:-
Sub Add_Appointment()
Dim myOlapp As Object
Dim myitem As Object
Set myOlapp = CreateObject("Outlook.Application")
Set myitem = myOlapp.createitem(1)
With myitem
.Body = "Annual Leave"
'.Duration = dur'
.AllDayEvent = True
.Subject = Range("C1").Value & " - A/L"
.Save
End With
Set myitem = Nothing
Set myOlapp = Nothing
End Sub
Question 3) How can i get the subject to look at a range of c1 if a cell in
column c has been changed, eg range d1 if d7 has been changed, range g1 if
g13 has been changed?
Question 4) How can i specifiy which calender needs updating?
Any answers to the questions are much apprecitated.
is assigned to a button, and just checks the spreadsheet for field "A/L" :-
Sub RunIf()
For Each c In Range("C3:F14")
If c.Value = "A/L" Then
Call Add_Appointment
End If
Next
End Sub
Question 1) Will this script look at every field from c3 to f14 eg c3, c4,
c5, c6 etc.
Question 2) Is there a way to update this script to only pick up amendments,
or will this need to be in my second macro?
My second macro creates an appointment within outlook calender:-
Sub Add_Appointment()
Dim myOlapp As Object
Dim myitem As Object
Set myOlapp = CreateObject("Outlook.Application")
Set myitem = myOlapp.createitem(1)
With myitem
.Body = "Annual Leave"
'.Duration = dur'
.AllDayEvent = True
.Subject = Range("C1").Value & " - A/L"
.Save
End With
Set myitem = Nothing
Set myOlapp = Nothing
End Sub
Question 3) How can i get the subject to look at a range of c1 if a cell in
column c has been changed, eg range d1 if d7 has been changed, range g1 if
g13 has been changed?
Question 4) How can i specifiy which calender needs updating?
Any answers to the questions are much apprecitated.