Default Cell value in changing environment

[TheDrescher]

I don't know if this is possible to do, but I figured I'd ask. I have a
workbook where the first worksheet populates data on employees based on their
name selected from a drop down cell. All of the data populates fine here.
There is one cell that is a subjective rating decided upon by managers
(Coaching Score). I run into an issue where the value selected in the
Coaching Score cell remains the same when you switch between employees. Is
there any way to have this cell reset to a default value every time you
switch employees on the front sheet? I've tried some formulas, but every
time you select a rating in the Coaching Score cell, it replaces the fomula
with the value selected. Is there any way I can fix this? Thanks!

 

[JLatham]

Put the code below into the worksheet code module for the sheet where you
enter/switch the employee name. To do it easily, choose that sheet and
right-click on it's name tab and choose [View Code] from the list. Then
copy, paste and edit the code below into that module and close the vb editor.

Private Sub Worksheet_Change(ByVal Target As Range)
'change these Const values as needed
'
'cell address on this sheet where employee name is
'entered/selected
Const empNameCell = "$A$1" ' be sure to use $ symbols.

'name of sheet where the coaching score cell is
'even if it is this same sheet
Const csSheetName = "CoachingScoreSheet"

'cell address for the coaching score entry
Const csCell = "$G$6" ' dollar symbols not required here

'this must be a valid phrase for the coaching score entries
Const csDefault = "Default Score" 'or set to number if needed

If Target.Address <> empNameCell Then
'not a name change
Exit Sub
End If
ThisWorkbook.Worksheets(csSheetName).Range(csCell) = csDefault

End Sub

 

[TheDrescher]

Thanks for the help with the code! When I change everything around to
correspond with the sheet, I get a Runtime Error '424' Object Required Error.
The code I used is:
Private Sub Worksheet_Change(ByVal Target As Range)

Const empNameCell = "$A$9"
Const csSheetName = "MainPage"
Const csCell = "CoachRating"
Const csDefault = "Select"

If Target.Address <> empNameCell Then

Exit Sub
End If
SalesManagersCoachingReport.Worksheets(MainPage).Range(CoachRating) =
csDefault

End Sub

When the debugger comes up it highlights the line
SalesManagersCoachingReport.Worksheets(MainPage).Range(CoachRating) =
csDefault

Is this error stemming from the fact that the coaching rating cell is named?
I did this because the rating selected influences the metrics on other
sheets.

Put the code below into the worksheet code module for the sheet where you
enter/switch the employee name. To do it easily, choose that sheet and
right-click on it's name tab and choose [View Code] from the list. Then
copy, paste and edit the code below into that module and close the vb editor.

Private Sub Worksheet_Change(ByVal Target As Range)
'change these Const values as needed
'
'cell address on this sheet where employee name is
'entered/selected
Const empNameCell = "$A$1" ' be sure to use $ symbols.

'name of sheet where the coaching score cell is
'even if it is this same sheet
Const csSheetName = "CoachingScoreSheet"

'cell address for the coaching score entry
Const csCell = "$G$6" ' dollar symbols not required here

'this must be a valid phrase for the coaching score entries
Const csDefault = "Default Score" 'or set to number if needed

If Target.Address <> empNameCell Then
'not a name change
Exit Sub
End If
ThisWorkbook.Worksheets(csSheetName).Range(csCell) = csDefault

End Sub

I don't know if this is possible to do, but I figured I'd ask. I have a
workbook where the first worksheet populates data on employees based on their
name selected from a drop down cell. All of the data populates fine here.
There is one cell that is a subjective rating decided upon by managers
(Coaching Score). I run into an issue where the value selected in the
Coaching Score cell remains the same when you switch between employees. Is
there any way to have this cell reset to a default value every time you
switch employees on the front sheet? I've tried some formulas, but every
time you select a rating in the Coaching Score cell, it replaces the fomula
with the value selected. Is there any way I can fix this? Thanks!

 

[Luke M]

Sounds like you'd have to use some type or VB script, such as the
Worksheet_Change event. An example:

Private Sub Worksheet_Change(ByVal Target As Range)
'Did the user change cell A1?
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Application.EnableEvents = False

'Do stuff here
'Clear cell contents
Range("A2").ClearContents
'Or, give the cell a formula
Range("A2").Formula = "=SUM(B1:B2)"

Application.EnableEvents = True
End Sub