Automatically update cells when one changes.

[Luciano_Bradley]

I am trying to add to my sheet a way to automatically change the Cells (K14:K26) to equal Cells (R14:R26) when Cell K13 changes to a specific text which I have 34 different specification. Right now I have a drop down list in K13 that pulls from another sheet labeled Standard Specifications. When a selected item is picked in K13, (R14:R26) pulls the correct information, now I know I can simply do this in (K14:K26) but I wanted to make it possible for some one to input their own spec if the list did not include it.

This is what I came up with... It does not work, although at some point I did get this idea to work but somehow messed it up and I haven't been able to get it. If this could be automatic and fluid that would be fantastic.

Sub Worksheet_Specifications(ByVal Target As Excel.Range)
If Range("K13").Value = "704.02A" Then
Cell("K18") = Cell("R18")
Cell("K19") = Cell("R19")
Cell("K20") = Cell("R20")
Cell("K21") = Cell("R21")
Cell("K22") = Cell("R22")
Cell("K23") = Cell("R23")
or
If Range("K13").Value = "703.04A" Then
Cell("K18") = Cell("R18")
Cell("K19") = Cell("R19")
Cell("K20") = Cell("R20")
Cell("K21") = Cell("R21")
Cell("K22") = Cell("R22")
Cell("K23") = Cell("R23")

End If
End Sub

Thank you for your time in reading this thread.

[RossioPS]

Just to make sure I understood correctly, you have a drop down list that you populate from another sheet.
You want the user to be able to select any of the options or input something new if is not there.

There is an easy solution for this without macros.

When you create the list from data validation, just make sure you untick the box "show error alert after invalid data is entered" under the "Error Alert" tab.

This will allow users to insert new items.

[Luciano_Bradley]

That, I already have finished I hope this picture of my sheet will give a better understanding.My sheet.png

[RossioPS]

Is your user just going to introduce the information? Is it a lot of things?

I would just create a command button on the top "Add new". And attach that to a userform.

On a userform you can manipulate what a user is introducing more easily.

This a little example of one I made, where I gave the user the option of selecting something predefined or introducing his own value:
Capturar.JPG

[Luciano_Bradley]

No, nothing like that they will just input the information in the cells they need. This most likely will not happen very often but I wanted it to be there just in case.

[Mumps1]

It would be easier to help and test possible solutions if we could work with your actual file. Could you attach a copy of your file with a detailed explanation of what you want to do referring to specific cells and worksheets?

[Luciano_Bradley]

Gradation Template 2018 Test.xlsm I have attached my file.

[Mumps1]

Replace your current Worksheet_Change macro with the following:

Please Login or Register  to view this content.

If the user wants to input a spec manually, all he has to do is click on K13 and enter it by over typing what may be in the cell already.

[Luciano_Bradley]

Well look at that it works!!!!! Thank you so very much. I'm working at bettering myself in VBA and this one was kicking me for awhile.

[Mumps1]

You are very welcome.