Default Cell value in changing environment
in [Functions]
|
Prev: identify the numbers cancelling by amounts
Next: Excel - How to get tab to go to next unlocked cell across columns
From: TheDrescher on 22 Apr 2010 11:13 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!
From: JLatham on 22 Apr 2010 11:28 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! >
From: TheDrescher on 22 Apr 2010 12:23 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. "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! > >
From: Luke M on 22 Apr 2010 13:41
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" <TheDrescher(a)discussions.microsoft.com> wrote in message news:654C5CE0-DBD6-4A3B-8863-113718AAF2BE(a)microsoft.com... >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! > |