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]

Alternatively, you could suppress the "Save" warning like this:

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
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    
End Sub

Though I kind of like the Workbook_Open way better.

 

[juan1]

you are correct the simple workbook open is easy and just will reset everything no matter how I choose to end.

thanks for all the help, I will continue my project and see if I come up with any more issues as I expand.
Juan

 

[Joe4]

You are welcome.

 

[juan1]

ok doing my official file now, not the example you helped me with. The following works, but when I try to add more I cant figure out how to add the 2nd option.
This is the 2nd option: below this is what works so far.

Set isect = Intersect(Target, Range("W35:AK36"))

If isect Is Nothing Then
Range("H67") = "a1 or a2"
Else
Range("H67") = ActiveCell


THIS WORKS SO FAR!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Range

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

Set isect = Intersect(Target, Range("W40:AK40"))

If isect Is Nothing Then
Range("H77") = "d"
Else
Range("H77") = ActiveCell

End If


' Re-protect sheet
ActiveSheet.Protect Password:="jlp"

End Sub

 

[Joe4]

We now have two cells being updated, H67 and H77.

So what should happen in the following cases:
- If a cell in W40:AK40 gets selected, H77 gets updated. What should happen to H67 in this case?
- If a cell in W35:AK36 gets selected, H67 gets updated. What should happen to H77 in this case?
- If a cell outside both those ranges gets selected, what should happen to both H67 and H77 in that case?

 

[Joe4]

Perhaps this is what you are looking for:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim isect As Range
    Dim isect2 As Range

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

    Set isect = Intersect(ActiveCell, Range("W40:AK40"))
    Set isect2 = Intersect(ActiveCell, Range("W35:AK36"))

    If (isect Is Nothing) And (isect2 Is Nothing) Then
        Range("H67") = "a1 or a2"
        Range("H77") = "d"
    Else
        If Not (isect Is Nothing) And (isect2 Is Nothing) Then
            Range("H77") = ActiveCell
            Range("H67") = "a1 or a2"
        Else
            If (isect Is Nothing) And Not (isect2 Is Nothing) Then
                Range("H67") = ActiveCell
                Range("H77") = "d"
            End If
        End If
    End If
        
'   Re-protect sheet
    ActiveSheet.Protect Password:="jlp"

End Sub

 

[juan1]

You are brilliant. I believe I can now expand on this now.

thanks

 

[juan1]

This works but, thought I could expand. What happens when I want to added more range options?

 

[Joe4]

Just how many more ranges are we talking here?

When doing this sort of coding, it is helpful to know exactly where you want to go. The danger in oversimplifying questions here, is that you might get an answer that is difficult to expand upon, since it addresses the question you asked (and not the real problem you are working on).

 

[Joe4]

OK, here is a version where you can add as many ranges as you like.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Unprotect sheet
    ActiveSheet.Unprotect Password:="jlp"
    
'   Set all initial "default" values
    Range("H67") = "a1 or a2"
    Range("H77") = "d"
    Range("H87") = "e"
    
'   Check first range
    If Not Intersect(ActiveCell, Range("W40:AK40")) Is Nothing Then
        Range("H77") = ActiveCell
        GoTo end_jump
    End If
    
'   Check second range
    If Not Intersect(ActiveCell, Range("W35:AK36")) Is Nothing Then
        Range("H67") = ActiveCell
        GoTo end_jump
    End If

'   Check third range
    If Not Intersect(ActiveCell, Range("W32:AK33")) Is Nothing Then
        Range("H87") = ActiveCell
        GoTo end_jump
    End If
    
end_jump:
'   Re-protect sheet
    ActiveSheet.Protect Password:="jlp"

End Sub

I did it for three ranges, but you can keep adding more. Just add new blocks under each section, and be sure to add the new initial default values at the top.