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.

I think you'll have to keep track of which cells have been processed.

You could insert a new column between each column and check the
..offset(0,1).value to keep track the

if c.value = "A/L" then
if c.offset(0,1).value = "done" then
'skip it
else
'do it
call add_appointment
c.offset(0,1).value = "done"
end if
end if

Or you could use column G:J and use .offset(0,4)...

Dan Wood wrote:

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.

--

Dave Peterson

ps. Yes, it will look at all the cells in that range. But the order will be
across the rows, then down the column.

You could change the loop if you wanted, though.

Dan Wood wrote:

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.

--

Dave Peterson

Thanks for your help with this.

I have set the code as follows:-

Sub RunIf()
For Each c In Range("C3:C14")
If c.Value = "A/L" Then
If c.Offset(0, 1).Value = "done" Then
'skip it'
Else
'do it'
Call Add_Appointment
c.Offset(0, 1).Value = "done"
Call Add_Appointment
End If
End If
Next
End Sub

This seems to work, but does put duplicates on the calender.Is there a way
to stop this? Also how can i set the code so it doesn't set reminders?

You call the Add_Appointment twice in this portion:

Call Add_Appointment
c.Offset(0, 1).Value = "done"
Call Add_Appointment



Dan Wood wrote:

Thanks for your help with this.

I have set the code as follows:-

Sub RunIf()
For Each c In Range("C3:C14")
If c.Value = "A/L" Then
If c.Offset(0, 1).Value = "done" Then
'skip it'
Else
'do it'
Call Add_Appointment
c.Offset(0, 1).Value = "done"
Call Add_Appointment
End If
End If
Next
End Sub

This seems to work, but does put duplicates on the calender.Is there a way
to stop this? Also how can i set the code so it doesn't set reminders?

--

Dave Peterson

I have managed to stop the reminders but the event is still duplicatating on
the calendar