Cell contains a value then copy and freeze other cell

[dungie78]

Not sure if this can be done but it would be very useful to me if it would work.

What I’m wanting to do when I click a button:

1. On sheet “Job Notes” Cell F24 - Contains a text value “INTERNAL DESIGN PRESSURE 1500 PSIG @ -20 °F AND 250 °F “
2. On sheet “DO NOT CHANGE” Cell C5 – To copy the text from “Job Notes” Cell F24 (only when button is clicked) I would like the text to stay the same even if I change “Job Notes” Cell F24 later.
3. If “Job Notes” Cell F24 is changed later then it will check “DO NOT CHANGE” Cell C5 to see if they have the some value. If not then it would put an “X” on sheet “Job Notes” Cell C24.


I already have all the conditional formatting after the “X” is on sheet “Job Notes” Cell C24 to mark it with a # revision. So the plan is after I make all the changes to the notes that I have changed will be marked with a rev. When I have to make a new rev. I can click the button and it will copy the changes from the last rev. so it will only rev. the new changes.

I would appreciate any help you can give me.

 

[Beyond_avarice]

Button Script:

Private Sub CommandButton1_Click()
Sheets("DO NOT CHANGE").Range("C5").Value = Sheets("Job Notes").Range("F24").Value

End Sub

On Sheet: "Job Notes" cell C24:

 =IF('DO NOT CHANGE'!C5<>'Job Notes'!F24,"X","")

I'm not sure I understand your "revision" procedure. Are you trying to mirror cells from your "Job Notes" to your "DO NOT CHANGE"?

 

[dungie78]

I will try to explain what I’m trying to do:

What I have working so far:
The end goal is to have notes that can be copied to shop drawings. I want the notes to be written similar no matter what engineer is working on the projects. This should also save time on typing, copying, and miss spelling. I currently have it set up that you can select different sheets (like Design notes, Manufacturing notes) and select what notes you want added to the “Job Notes” sheet and it will sort them in the correct order and be ready to be copied. I already have ways to manipulate commonly changed things in the notes and to add more notes on the end if needed. Notes can be overwritten if needed at any time. The notes can be saved and added to shop drawings at this time.

If there is any change to the notes we have to Rev. it to let the shop or customer know what notes have been changed. Currently I have it set up to put an “X” in the cell in front of the note that has been changed. This will add a colored flag (depending on what revision it’s a different color) in front of the notes changed. Depending on what the cell “DWG. Rev” has, it will populate it with the correct flag. If there is another change later we need to delete all the “X”s (to clear all the flags) then change the “DWG. Rev” cell add the “X”s to all the new notes that were changed.

This all works really well but trying to make it smarter so there will be less mistakes.

What I’m wanting to do:
I’m trying to make a button that will do a lot of things at the same time.

1. Copy all the notes to the “DO NOT CHANGE” sheet. I want the cells on the “DO NOT CHANGE” sheet to be frozen at this point. I do not want them to be copied again till to button is clicked again. (I want this because then I can have a different button that will check the differences between the “Job Notes” sheet and the “DO NOT CHANGE” sheet. It will also add the “X” if it notices any differences.)
2. Save the document.
3. Then turn the document into read only.
4. Send an email to the engineer or designer of the project staying the notes are ready for the drawings. (I would like to have a drop down list so we can pick who the email needs to be sent to. It will be a pre-loaded e-mail that will have a link to the excel file so the notes can be copied.)

At this point it is done till a revision needs to be made. When opening the old rev. it will be read only but when you change the cell “DWG. Rev” it will automatically save file as a new file with the new rev. # behind it (it will also make the new file read/write and remove all the rev. “X”s). Now that this new file has the old notes on the “DO NOT CHANGE” sheet I check for the differences when the changes are made on the “Job Notes” sheet.

 

[dungie78]

Thank you so much. I got that to work.

One last question I promise. I have a range of cell that needs this code. Is there a loop or something to save me lines of code?

Private Sub CommandButton1_Click()
Sheets("DO NOT CHANGE").Range("C5").Value = Sheets("Job Notes").Range("F24").Value
End Sub

End Sub
Example:
On Sheet: "DO NOT CHANGE" Range “C5” thru “C37” and
On Sheet: "Job Notes" Range “F24” thru “F56”

Thanks again for all your help.

I’m new to this. Is there anywhere I can click to say you solved my problem?

 

[Beyond_avarice]

There's no need for a loop if all of your data is located in one column of 33 rows.

Sheets("DO NOT CHANGE").Range("C5:C37").Value = Sheets("Job Notes").Range("F24:F56").Value

 

[dungie78]

That works. Thanks