Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

Your going to need a list of the restricted addresses or some identifiable
feature that they all have in common.

"Bunnyboy" wrote:

Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

The thing is there are no definate sites for us. We can go to any property
within 20 miles. I basically wanted a seperate sheet we could add these
addresses with the name and a reason why and a warning if any of those
addresses were entered on the main sheet.

"Lee Diggins" wrote:

Hi James

Would you want the record added even if you don't cover that site? If not,
you could do this using Data Validation using the List option, pointing to a
named range stored in a hidden worksheet containing the allowed sites.

Maybe have a read about Data Validation and post back if you need any
further clarification or if you don't feel it does not acheiev what you're
after.

Regards,

Lee

"Bunnyboy" wrote:

Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

Sorry am getting confused the addresses you are refferring to are house
addresses not web addresses? and you are wanting to allert the end user if
the address they enter is beyond 20miles of a base address?
If this is correct then you could use a sub to calculate distance to address
by post code / zip code!
Alternatively if you are creating a list of addresses you could check for
these using "find" when data is entered!
Is data entered by user form or directly into the cells of a worksheet?

"Bunnyboy" wrote:

The thing is there are no definate sites for us. We can go to any property
within 20 miles. I basically wanted a seperate sheet we could add these
addresses with the name and a reason why and a warning if any of those
addresses were entered on the main sheet.

"Lee Diggins" wrote:

Hi James

Would you want the record added even if you don't cover that site? If not,
you could do this using Data Validation using the List option, pointing to a
named range stored in a hidden worksheet containing the allowed sites.

Maybe have a read about Data Validation and post back if you need any
further clarification or if you don't feel it does not acheiev what you're
after.

Regards,

Lee

"Bunnyboy" wrote:

Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

Hi James

Would you want the record added even if you don't cover that site? If not,
you could do this using Data Validation using the List option, pointing to a
named range stored in a hidden worksheet containing the allowed sites.

Maybe have a read about Data Validation and post back if you need any
further clarification or if you don't feel it does not acheiev what you're
after.

Regards,

Lee

"Bunnyboy" wrote:

Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

ok lol

Let me start again.

We use a spreadsheet to collect information from customers who require us to
attend to a property to do repairs. Occasionally we may come across a
property that our workers won't want to go back too for a specific reason
such as the property was messy or the customer was abusive. What i wanted was
somewhere we could put these addresses and the reason why we won't go back.
Then if the customer rings again and someone tries to put the address in then
a warning or an error will come up saying that the property is blacklisted
and why.

Is this possible?

James

"Atishoo" wrote:

Sorry am getting confused the addresses you are refferring to are house
addresses not web addresses? and you are wanting to allert the end user if
the address they enter is beyond 20miles of a base address?
If this is correct then you could use a sub to calculate distance to address
by post code / zip code!
Alternatively if you are creating a list of addresses you could check for
these using "find" when data is entered!
Is data entered by user form or directly into the cells of a worksheet?

"Bunnyboy" wrote:

The thing is there are no definate sites for us. We can go to any property
within 20 miles. I basically wanted a seperate sheet we could add these
addresses with the name and a reason why and a warning if any of those
addresses were entered on the main sheet.

"Lee Diggins" wrote:

Hi James

Would you want the record added even if you don't cover that site? If not,
you could do this using Data Validation using the List option, pointing to a
named range stored in a hidden worksheet containing the allowed sites.

Maybe have a read about Data Validation and post back if you need any
further clarification or if you don't feel it does not acheiev what you're
after.

Regards,

Lee

"Bunnyboy" wrote:

Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

Absolutely it is possible! Do you have an idea of what a new worksheet would
be called and which range of cells would contain which aspects of the address
and where the reason would be entered?
Do you know how data is entered? A user form would sound ideal for this sort
of task!
There may be an issue to consider of what if an address is entered slightly
different to that on the black list, if searching for an exact match then it
may not show up!
Is there any other identifier for each customer such as a customer
refference number or post code etc

"Bunnyboy" wrote:

ok lol

Let me start again.

We use a spreadsheet to collect information from customers who require us to
attend to a property to do repairs. Occasionally we may come across a
property that our workers won't want to go back too for a specific reason
such as the property was messy or the customer was abusive. What i wanted was
somewhere we could put these addresses and the reason why we won't go back.
Then if the customer rings again and someone tries to put the address in then
a warning or an error will come up saying that the property is blacklisted
and why.

Is this possible?

James

"Atishoo" wrote:

Sorry am getting confused the addresses you are refferring to are house
addresses not web addresses? and you are wanting to allert the end user if
the address they enter is beyond 20miles of a base address?
If this is correct then you could use a sub to calculate distance to address
by post code / zip code!
Alternatively if you are creating a list of addresses you could check for
these using "find" when data is entered!
Is data entered by user form or directly into the cells of a worksheet?

"Bunnyboy" wrote:

The thing is there are no definate sites for us. We can go to any property
within 20 miles. I basically wanted a seperate sheet we could add these
addresses with the name and a reason why and a warning if any of those
addresses were entered on the main sheet.

"Lee Diggins" wrote:

Hi James

Would you want the record added even if you don't cover that site? If not,
you could do this using Data Validation using the List option, pointing to a
named range stored in a hidden worksheet containing the allowed sites.

Maybe have a read about Data Validation and post back if you need any
further clarification or if you don't feel it does not acheiev what you're
after.

Regards,

Lee

"Bunnyboy" wrote:

Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

It would just be a work sheet called blacklist. I'm not sure of the range of
cells yet as i was just thinking about it. We only enter the information into
the cells as we arrnage appointments based around areas so it has to be cell
entry.

I can get the addresses entered in a uniform way so that they're always
entered the same way. Post codes wouldn't work as sometimes an entire street
will have the same postcode.

If you guys could tell me how to do it, I can test it and see how it works.

"Atishoo" wrote:

Absolutely it is possible! Do you have an idea of what a new worksheet would
be called and which range of cells would contain which aspects of the address
and where the reason would be entered?
Do you know how data is entered? A user form would sound ideal for this sort
of task!
There may be an issue to consider of what if an address is entered slightly
different to that on the black list, if searching for an exact match then it
may not show up!
Is there any other identifier for each customer such as a customer
refference number or post code etc

"Bunnyboy" wrote:

ok lol

Let me start again.

We use a spreadsheet to collect information from customers who require us to
attend to a property to do repairs. Occasionally we may come across a
property that our workers won't want to go back too for a specific reason
such as the property was messy or the customer was abusive. What i wanted was
somewhere we could put these addresses and the reason why we won't go back.
Then if the customer rings again and someone tries to put the address in then
a warning or an error will come up saying that the property is blacklisted
and why.

Is this possible?

James

"Atishoo" wrote:

Sorry am getting confused the addresses you are refferring to are house
addresses not web addresses? and you are wanting to allert the end user if
the address they enter is beyond 20miles of a base address?
If this is correct then you could use a sub to calculate distance to address
by post code / zip code!
Alternatively if you are creating a list of addresses you could check for
these using "find" when data is entered!
Is data entered by user form or directly into the cells of a worksheet?

"Bunnyboy" wrote:

The thing is there are no definate sites for us. We can go to any property
within 20 miles. I basically wanted a seperate sheet we could add these
addresses with the name and a reason why and a warning if any of those
addresses were entered on the main sheet.

"Lee Diggins" wrote:

Hi James

Would you want the record added even if you don't cover that site? If not,
you could do this using Data Validation using the List option, pointing to a
named range stored in a hidden worksheet containing the allowed sites.

Maybe have a read about Data Validation and post back if you need any
further clarification or if you don't feel it does not acheiev what you're
after.

Regards,

Lee

"Bunnyboy" wrote:

Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

OK one way would be to put a command button ((view-control tollbox-design
mode)then drag command button onto main sheet) onto your main sheet, label it
something like "check Address" then insert the following sub

Private Sub CommandButton1_Click()
With Worksheets("blacklist").Range("A:A")
If ActiveCell.Value < "" Then
Set c = .Find(ActiveCell.Value, LookIn:=xlValues)
If Not c Is Nothing Then
Dim Res As Long
Res = MsgBox(c.Offset(0, 1).Value)
Cancel = Res < 6
End If
End If
End With
End Sub

If the end user selects a cell containing an address then clicks this
command button it will throw up a message box that would contain the
information in the cell next to the address on the blacklist sheet.
So if you have addresses in column a of the blacklist worksheet and reasons
for blacklisting the address in column B it will display the reason for
blacklisting in the message box when the address of the selected cell is
identical to one in column a blacklist.
"Bunnyboy" wrote:

It would just be a work sheet called blacklist. I'm not sure of the range of
cells yet as i was just thinking about it. We only enter the information into
the cells as we arrnage appointments based around areas so it has to be cell
entry.

I can get the addresses entered in a uniform way so that they're always
entered the same way. Post codes wouldn't work as sometimes an entire street
will have the same postcode.

If you guys could tell me how to do it, I can test it and see how it works.

"Atishoo" wrote:

Absolutely it is possible! Do you have an idea of what a new worksheet would
be called and which range of cells would contain which aspects of the address
and where the reason would be entered?
Do you know how data is entered? A user form would sound ideal for this sort
of task!
There may be an issue to consider of what if an address is entered slightly
different to that on the black list, if searching for an exact match then it
may not show up!
Is there any other identifier for each customer such as a customer
refference number or post code etc

"Bunnyboy" wrote:

ok lol

Let me start again.

We use a spreadsheet to collect information from customers who require us to
attend to a property to do repairs. Occasionally we may come across a
property that our workers won't want to go back too for a specific reason
such as the property was messy or the customer was abusive. What i wanted was
somewhere we could put these addresses and the reason why we won't go back.
Then if the customer rings again and someone tries to put the address in then
a warning or an error will come up saying that the property is blacklisted
and why.

Is this possible?

James

"Atishoo" wrote:

Sorry am getting confused the addresses you are refferring to are house
addresses not web addresses? and you are wanting to allert the end user if
the address they enter is beyond 20miles of a base address?
If this is correct then you could use a sub to calculate distance to address
by post code / zip code!
Alternatively if you are creating a list of addresses you could check for
these using "find" when data is entered!
Is data entered by user form or directly into the cells of a worksheet?

"Bunnyboy" wrote:

The thing is there are no definate sites for us. We can go to any property
within 20 miles. I basically wanted a seperate sheet we could add these
addresses with the name and a reason why and a warning if any of those
addresses were entered on the main sheet.

"Lee Diggins" wrote:

Hi James

Would you want the record added even if you don't cover that site? If not,
you could do this using Data Validation using the List option, pointing to a
named range stored in a hidden worksheet containing the allowed sites.

Maybe have a read about Data Validation and post back if you need any
further clarification or if you don't feel it does not acheiev what you're
after.

Regards,

Lee

"Bunnyboy" wrote:

Hey

We currently use a spreadsheet on office 2003 to store a large amount of
personal information regarding property work. This spreadsheet is populated
by opertives who answer phone calls and enter the information onto it,
Recently we have found some sites that are no longer covered by us.

I was thinking if we could put a restriction on the spreadsheet so when an
operative tries to enter an address that is restricted then it will bring up
a warning to say that its restricted and a reason why.

Can this be done?

Hope to hear from you soon.

James
(Technology Officer)

Private Sub CommandButton1_Click()
With Worksheets("blacklist").Range("A:A")
If ActiveCell.Value < "" Then
Set c = .Find(ActiveCell.Value, LookIn:=xlValues)
If Not c Is Nothing Then
Dim Res As Long
Res = MsgBox("Address is Blacklisted:" & vbCrLf &
c.Offset(0, 1).Value)
Cancel = Res < 6
End If
End If
End With
End Sub

This variant adds the line "Address is Blacklisted" before the reason why,
or could contain any other comment you wished to place between the "" in the
sub.

When Addresses are input is the entire address entered into one cell or is
it split across cells?
ie street name and number in collumn A , town in column B and so on? and
would you be looking to use the same format on the blacklist?
If so the sub above would need to be changed to compare all cells containing
elements of the address.
All the best John