Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click to show value in active cell
I require assistance with showing a value within the active cell.
It is intended for a internal company survey, where ratings are given against adjacent questions. For example, there are 5 questions, 5 possible ratings. Columns B,C,D,E,F represent a number from 1 to 5, where B=5, C=4, D=3, E=2 and F=1. If Question1(Q1) deserves a 4 rating, then click on cell C1. So, cell C1 would then show 4 in that cell. A B C D E F Q1. 4 Q2. 3 Q3. 5 Q4. 2 Q5. 4 The user will work down the rows from top to bottom, clicking on a particular column to apply the appropriate rating. Also, i want only ONE cell per row to be selected, so if user first chose rating 3, but then wishes to change it to rating 2, i want the rating 3 to disappear, and show only the new selected rating 2. Can anybody help with this issue? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click to show value in active cell
Mik,
Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:F")) Is Nothing Then Exit Sub Application.EnableEvents = False Intersect(Target.EntireRow, Range("B:F")).ClearContents Target.Value = 7 - Target.Column Application.EnableEvents = True End Sub "Mik" wrote in message ... I require assistance with showing a value within the active cell. It is intended for a internal company survey, where ratings are given against adjacent questions. For example, there are 5 questions, 5 possible ratings. Columns B,C,D,E,F represent a number from 1 to 5, where B=5, C=4, D=3, E=2 and F=1. If Question1(Q1) deserves a 4 rating, then click on cell C1. So, cell C1 would then show 4 in that cell. A B C D E F Q1. 4 Q2. 3 Q3. 5 Q4. 2 Q5. 4 The user will work down the rows from top to bottom, clicking on a particular column to apply the appropriate rating. Also, i want only ONE cell per row to be selected, so if user first chose rating 3, but then wishes to change it to rating 2, i want the rating 3 to disappear, and show only the new selected rating 2. Can anybody help with this issue? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click to show value in active cell
On 30 Jan, 19:38, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Mik, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:F")) Is Nothing Then Exit Sub Application.EnableEvents = False Intersect(Target.EntireRow, Range("B:F")).ClearContents Target.Value = 7 - Target.Column Application.EnableEvents = True End Sub "Mik" wrote in message ... I require assistance with showing a value within the active cell. It is intended for a internal company survey, where ratings are given against adjacent questions. For example, there are 5 questions, 5 possible ratings. Columns B,C,D,E,F represent a number from 1 to 5, where B=5, C=4, D=3, E=2 and F=1. If Question1(Q1) deserves a 4 rating, then click on cell C1. So, cell C1 would then show 4 in that cell. A * * * *B *C *D *E *F Q1. * * * * *4 Q2. * * * * * * * 3 Q3. * * *5 Q4. * * * * * * * * * 2 Q5. * * * * *4 The user will work down the rows from top to bottom, clicking on a particular column to apply the appropriate rating. Also, i want only ONE cell per row to be selected, so if user first chose rating 3, but then wishes to change it to rating 2, i want the rating 3 to disappear, and show only the new selected rating 2. Can anybody help with this issue? Thanks in advance.- Hide quoted text - - Show quoted text - Bernie, Fantastic... Thanks a lot. Mik |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click to show value in active cell
On 30 Jan, 19:45, Mik wrote:
On 30 Jan, 19:38, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mik, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:F")) Is Nothing Then Exit Sub Application.EnableEvents = False Intersect(Target.EntireRow, Range("B:F")).ClearContents Target.Value = 7 - Target.Column Application.EnableEvents = True End Sub "Mik" wrote in message ... I require assistance with showing a value within the active cell. It is intended for a internal company survey, where ratings are given against adjacent questions. For example, there are 5 questions, 5 possible ratings. Columns B,C,D,E,F represent a number from 1 to 5, where B=5, C=4, D=3, E=2 and F=1. If Question1(Q1) deserves a 4 rating, then click on cell C1. So, cell C1 would then show 4 in that cell. A * * * *B *C *D *E *F Q1. * * * * *4 Q2. * * * * * * * 3 Q3. * * *5 Q4. * * * * * * * * * 2 Q5. * * * * *4 The user will work down the rows from top to bottom, clicking on a particular column to apply the appropriate rating. Also, i want only ONE cell per row to be selected, so if user first chose rating 3, but then wishes to change it to rating 2, i want the rating 3 to disappear, and show only the new selected rating 2. Can anybody help with this issue? Thanks in advance.- Hide quoted text - - Show quoted text - Bernie, Fantastic... Thanks a lot. Mik- Hide quoted text - - Show quoted text - Bernie, If i wanted to extend the survey, and add seperate questions where answers would be in columns H:L (in addition to columns B:F), how would i calculate this? Mik |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click to show value in active cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:F, H:L")) Is Nothing Then Exit Sub Application.EnableEvents = False If Intersect(Target, Range("B:F")) Is Nothing Then Intersect(Target.EntireRow, Range("H:L")).ClearContents Target.Value = 13 - Target.Column Else Intersect(Target.EntireRow, Range("B:F")).ClearContents Target.Value = 7 - Target.Column End If Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Mik" wrote in message ... On 30 Jan, 19:45, Mik wrote: On 30 Jan, 19:38, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mik, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:F")) Is Nothing Then Exit Sub Application.EnableEvents = False Intersect(Target.EntireRow, Range("B:F")).ClearContents Target.Value = 7 - Target.Column Application.EnableEvents = True End Sub "Mik" wrote in message ... I require assistance with showing a value within the active cell. It is intended for a internal company survey, where ratings are given against adjacent questions. For example, there are 5 questions, 5 possible ratings. Columns B,C,D,E,F represent a number from 1 to 5, where B=5, C=4, D=3, E=2 and F=1. If Question1(Q1) deserves a 4 rating, then click on cell C1. So, cell C1 would then show 4 in that cell. A B C D E F Q1. 4 Q2. 3 Q3. 5 Q4. 2 Q5. 4 The user will work down the rows from top to bottom, clicking on a particular column to apply the appropriate rating. Also, i want only ONE cell per row to be selected, so if user first chose rating 3, but then wishes to change it to rating 2, i want the rating 3 to disappear, and show only the new selected rating 2. Can anybody help with this issue? Thanks in advance.- Hide quoted text - - Show quoted text - Bernie, Fantastic... Thanks a lot. Mik- Hide quoted text - - Show quoted text - Bernie, If i wanted to extend the survey, and add seperate questions where answers would be in columns H:L (in addition to columns B:F), how would i calculate this? Mik |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click to show value in active cell
On 30 Jan, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:F, H:L")) Is Nothing Then Exit Sub Application.EnableEvents = False If Intersect(Target, Range("B:F")) Is Nothing Then * *Intersect(Target.EntireRow, Range("H:L")).ClearContents * *Target.Value = 13 - Target.Column Else * *Intersect(Target.EntireRow, Range("B:F")).ClearContents * *Target.Value = 7 - Target.Column End If Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Mik" wrote in message ... On 30 Jan, 19:45, Mik wrote: On 30 Jan, 19:38, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mik, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:F")) Is Nothing Then Exit Sub Application.EnableEvents = False Intersect(Target.EntireRow, Range("B:F")).ClearContents Target.Value = 7 - Target.Column Application.EnableEvents = True End Sub "Mik" wrote in message .... I require assistance with showing a value within the active cell. It is intended for a internal company survey, where ratings are given against adjacent questions. For example, there are 5 questions, 5 possible ratings. Columns B,C,D,E,F represent a number from 1 to 5, where B=5, C=4, D=3, E=2 and F=1. If Question1(Q1) deserves a 4 rating, then click on cell C1. So, cell C1 would then show 4 in that cell. A B C D E F Q1. 4 Q2. 3 Q3. 5 Q4. 2 Q5. 4 The user will work down the rows from top to bottom, clicking on a particular column to apply the appropriate rating. Also, i want only ONE cell per row to be selected, so if user first chose rating 3, but then wishes to change it to rating 2, i want the rating 3 to disappear, and show only the new selected rating 2. Can anybody help with this issue? Thanks in advance.- Hide quoted text - - Show quoted text - Bernie, Fantastic... Thanks a lot. Mik- Hide quoted text - - Show quoted text - Bernie, If i wanted to extend the survey, and add seperate questions where answers would be in columns H:L (in addition to columns B:F), how would i calculate this? Mik- Hide quoted text - - Show quoted text - Bernie, Once again, Thanks. That's perfect. Mik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
double click cell to show formula references | Excel Discussion (Misc queries) | |||
Visual Basic - active macro on cell click | Excel Programming | |||
Using double click on active cell to open a custom help window | Excel Programming | |||
How to show the active cell after running a macro? | Excel Programming | |||
Only want color to show when cell is active | Excel Worksheet Functions |