If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Default Cell value in changing environment
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! |
#2
|
|||
|
|||
Default Cell value in changing environment
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" wrote: 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! |
#3
|
|||
|
|||
Default Cell value in changing environment
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).R ange(CoachRating) = csDefault End Sub When the debugger comes up it highlights the line SalesManagersCoachingReport.Worksheets(MainPage).R ange(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. "JLatham" wrote: 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" wrote: 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! |
#4
|
|||
|
|||
Default Cell value in changing environment
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 -- Best Regards, Luke M "TheDrescher" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|