Trouble with worksheet_calculate
 

Thanks for your help in advance. I have searched through quite a bit
of posts and have found some very helpful information that I have
incorporated into my spreadsheet.

However, I have recently encountered some problems.

I have a workbook I created in order to quote manufacturing costs. On
the first tab (worksheet) I have a drop down menu that a customer
contact (address, phone numbers, etc.) is selected. A table/named
range and cell link all set up to function/drive the drop down
selections. I finally got worksheet_calculate to fire a macro to go
down and copy and then paste the customer contact information back up
in the address area thus reducing customer contact entry to one
click/selection from the drop down. No problems here. But, when I
was finishing up some additional formatting and programming, I had to
copy/insert a worksheet. Then it happened. My macro fired on the
newly copied sheet and wiped out some important cells.

With Excel, inserting a copied sheet is highly flexible and important
for my department to use on the fly, but we are at risk of forgetting
to "fix" the cells that get wiped out when inserting a copied sheet.

Here is the code I setup:

'Private Sub Worksheet_Calculate()
'If Range("$b$108") < 78 Then
' Application.Run "Get_Customer_Address"
'Else
' If Range("b$108") = 78 Then
' Application.Run "Get_Customer_Address"
' End If
'End If
'End Sub

B108 is the link cell for the drop down. Under 78 is a selection from
the list. Over 78 is the blank-out selection for the drop down. Not
even sure if I need this.

Please advise if there is another way I can set this up in order to
avoid the hassle with a copied sheet. Thanks again!!