Macro Help Requires

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.
 
D

Dave Peterson

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 there:

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)...
 
D

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.
 
D

Dan Wood

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?
 
D

Dan Wood

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

Dave Peterson

You call the Add_Appointment twice in this portion:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top