Trouble with worksheet_calculate

[opeth]

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!!

 

[Anne Troy]

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.

That could have been done with data-validation and a vlookup.
See http://www.theofficeexperts.com/excel.htm#TheDreadedVlookup

You are doing this the hard way.
Fix the file up and then save it as a template that cannot be overwritten.
**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
www.Brainbench.com Word Test Developer 2000,2002,2003
********************************

 

[opeth]

Thank you, Anne. You are correct. I already have this setup as a
vlookup. Sometimes the problem isn't with the code or a specific
command. In this case all was needed was a freash perspective!! I
did not need an auto macro to begin with!! Jeesh.

I have my problem rectified now.

Thanks for your help!!