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" <mhol...@safetysystemsuk.com> wrote in message
>
> news:4b82637d-e00e-4b35-959d-(E-Mail Removed)...
> On 30 Jan, 19:45, Mik <mhol...@safetysystemsuk.com> 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" andpaste 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" <mhol...@safetysystemsuk.com> wrote in message
>
> > >news:2c82bf60-bf72-4453-a2a2-(E-Mail Removed)....
>
> > > >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
Similar Threads
