Copying value when cell value changes

[skittlz]

I have a spreadsheet in which the user will enter data in Column A. Every time that data is entered, I need that data to be stored as a string "Asset" for additional steps. I cant get this to work:


Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the changed cell is A1
Sheets("Cal & PM Initial Entry").Activate
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
Asset = ActiveCell.Activate
MsgBox = Asset
End If
End Sub

What am I missing? Also, can I take that stored value in Asset across multiple Sub's ()? or is that value lost when I call another sub?

 

[Joe4]

That code looks a little problematic. You don't need to select the sheet, because event procedure code should already be in that particular sheet's module (the "Cal & PM Initial Entry") sheet.

I fixed up your code a bit. If declare asset as a Global variable outside of the procedure at the top of your module, like this:

Dim Asset As String

Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the changed cell is column A
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Asset = Target.Value
    End If
End Sub

You can then use it in other procedures in the same module.

So it you then have code like this is that same sheet module:

Sub MyTest()
    MsgBox Asset
End Sub

if you run it after updating a value in column A, it will return that value. You can also use it in calculations, etc.

 

[skittlz]

I'm not sure I understand what you mean by module - can you clarify?

I have the code you provided in the Sheet4 code box (module?) called (what is it called???)

Then I have another set of code in the Sheet3 code box (Called MyMacro() )


Can I Call "MyMacro()" from Sheet 4? Or do they need to be continuous?

 

[Joe4]

Each "Sheet" in the VBA explorer is a separate module. There is also the "ThisWorkbook" module, and you can insert your own general modules under "Modules", though automated even procedure like "Worksheet_Change" do not work in general modules (as they are not "linked" to any particular worksheet"). So Global variables do NOT expand across multiple modules (i.e. VBA code in Sheet3 cannot see variables in Sheet4).

In order to come up with a solution that does what you need, forget about any code for a moment. Please show us some example data, and explain (in plain English) exactly what you want to happen (show us an example of your expected output).

 

[skittlz]

I wish I could upload the spreadsheet....But I'll do my best. In the "Equipment-Initial Entry" Tab, my plan is to list the basic equipment details - Make, Model, Description, Spec#, internal unique "Asset ID", and if the equipment needs calibration or maintenance (PM). This would constitute as an "Initial Entry for a NEW make/model of equipment".

In Next Tab - "Cal and PM Initial Entry" - the user (operator) - would enter the "Asset ID" in Column A - and the macro would auto-run to pre-populate the Specs/Calibration/PM details from "Equipment-Initial Entry" tab. If Calibration is set as Yes - it would leave the cells open. If no, it'll grey them out (and maybe even lock the cells). Same for PM. There may be multiple asset IDs (potentially 100s - further differentiated by Unit ID - SN01, SN02, SN03, and so on). I need something that'd run in that ROW and update all the entries.

Note: Entries will not be in order - so I may have E-017 (10 times), followed by E-027 2 times, and then 10 more E-017 in the Cal & PM Initial Entry tap. It shouldn't matter for the code, but just to clarify that Cal-PM Initial Entries would be in a random sequence.

Thank you for your help @Joe4 !!

 

[Joe4]

So let me see if I have this correct - as a user enters the Asset ID in column A of the "Cal & PM Initial Entry" sheet, you want it to look up that Asset ID on the "Equipment - Initial Entry" sheet, and populate various columns in that same row on the "Cal & PM Initial Entry" sheet? Is that right?

If so, I think I would do that with a bunch of XLOOKUP formula for those columns, to match on Asset ID and pull back the various matching values.

To incorporate/automate that, you have a few options, such as:

1. Set up your "Cal & PM Initial Entry" sheet as a Table, with formulas built in. Then you insert new records into that table, it will automatically copy those formulas down. You just need to be sure that you insert a blank row into the table before making a new entry (can do this manually or maybe create a VBA button to do it).

2. Pre-populate the formula in all columns. Just copy it down for as many rows as you think you will ever need. You can make it return nothing if column A is blank by structuring your formula like this:

=IF(A2="","",XLOOKUP(...))

The big advantage to this method is no VBA is required at all.

3. Have a Worksheet_Change populate all the needed columns with the necessary XLOOKUP formulas as column A of your "Cal & PM Initial Entry" sheet is updated. To get exactly what these formulas need to look like in VBA, simply turn on your Macro Recorder and record yourself entering those formulas across one row. Then, you can take portions of that recorded code to put in your Worksheet_Change procedure to populate the formulas.

4. Somewhat similar to option 3 above, but you could pre-populate row 2 of your "Cal & PM Initial Entry" sheet with the necessary formulas. Then, have your Worksheet_Change code simply copy the formulas from row 2 down to the row whose column A you just updated.

So as you can see, many ways to accomplish populating this data.

 

[skittlz]

Thanks @Joe4 - i learned from experience that Vlookup and Xlookup are volatile functions that can slow spreadsheets down ALOT - so trying to avoid those. But your bullet# 3 gave me an idea! I can capture the VBA and incorporate that into my formulas. Thanks!!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[MARK858]

i learned from experience that Vlookup and Xlookup are volatile functions that can slow spreadsheets down ALOT

Neither are volatile functions, they only recalculate if you make a change to a cell in their dependency chain since the last calculate.
A volatile function recalculates every time you do a calculate.