A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Default Cell value in changing environment



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2010, 04:13 PM posted to microsoft.public.excel.worksheet.functions
TheDrescher
external usenet poster
 
Posts: 16
Default 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  
Old April 22nd, 2010, 04:28 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old April 22nd, 2010, 05:23 PM posted to microsoft.public.excel.worksheet.functions
TheDrescher
external usenet poster
 
Posts: 16
Default 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  
Old April 22nd, 2010, 06:41 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:34 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.