hi-liting one cell changes another cell

[juan1]

I would like to have cell C50 (presently shows the letter A in it, representing a dimension) to show the value of another cell that I click from (would be from locked cells F4:Z4), not actually change it.

I would then do this for other typical areas for other dimensions.

thanks for any help
Juan

 

[Joe4]

I am afraid that your question is not quite clear. It sounds like you want to change what is in C50, but then not actually change it?
That does not make much sense to me.

Can you try explaining again, maybe walk us through an actual example of what you are trying to do?

 

[juan1]

Where I talk about C50, this is part of a sketch indicating one of many dimensions on the sketch. Columns F:Z have many different types of products each with different dimensions directly below. I would like to click on one of the dimensions under a specific type and have that dimension show up on the sketch in C50 (from row 4). There are a lot more rows with other dimensions to link to other spots on the sketch (ex: row 5 dimensions when clicked would show up at B60).
thanks again
Juan

 

[Joe4]

OK, here is how I am interpretting this:
- If you click on any cell in the range F4:Z4, the value from that cell should show up in cell C50.

If that is the case, use the VBA code below. Right-click on the sheet tab name at the bottom of the sheet, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim isect As Range
    
    Set isect = Intersect(Target, Range("F4:Z4"))
    
    If Not isect Is Nothing Then
        Range("C50") = ActiveCell
    End If
    
End Sub

 

[juan1]

ok this works, Now another question. C50 is a locked cell and then it doesn't work (would like to keep it this way for various reasons). So it would just really show in C50 temporarily but not change it. maybe change back after clicking another cell that is not in row 4?

thanks
Juan

 

[Joe4]

Then you will need to some VBA code to temporarily unprotect the sheet in order to update cell C50.
Here is code that will do that, and set C50 back to "A" when any cell outside of F4:Z4 is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim isect As Range
    
'   Unprotect sheet
    ActiveSheet.Unprotect Password:="pass"
    
    Set isect = Intersect(Target, Range("F4:Z4"))
    
    If isect Is Nothing Then
        Range("C50") = "A"
    Else
        Range("C50") = ActiveCell
    End If
    
'   Re-protect sheet
    ActiveSheet.Protect Password:="pass"
    
End Sub

Update the password to match yours.

 

[juan1]

Wow seems to be working very nicely. Can you add that when the file closes it puts C50 back to A, in case another cell was not clicked in when ending.


thanks again
and happy new year

 

[Joe4]

Place this code in the "ThisWorkbook" module, and update the sheet name to match the name of your sheet this is found on (along with the password):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'   Select appropriate sheet
    Sheets("Sheet1").Activate

'   Unprotect sheet
    ActiveSheet.Unprotect Password:="pass"

'   Update C50
    Range("C50") = "A"
    
'   Re-protect sheet
    ActiveSheet.Protect Password:="pass"
    
'   Save workbook
    ActiveWorkbook.Save
    
End Sub

You will notice many similarities to the other code.

 

[juan1]

If I answer, don't save, and reopen it has the last cell click in C50.
any solutions to this
thanks so much

 

[Joe4]

If I answer, don't save, and reopen it has the last cell click in C50.

Well, of course! If you don't save the change, it won't be saved!

Perhaps it would be better to use the Workbook_Open event to set this to "A" upon opening, as opposed to using the Workbook_BeforeClose event.
Just copy the same code as above to the Workbook_Open event, leaving off the "Save" part.