Problem with Radio Button's format control

darcyfournier

Board Regular
Joined
Nov 21, 2017
Messages
54
I am creating an employee evaluation template (a sample is below)

Column A is the category

Column B, C D, E and F will be ratings (unacceptable, below, meets, exceeds, outstanding) with radio button options.

Column E is the ratings total for whatever rating above is selected 1-5 because their are 5 options

In the the first category Attire- it works perfectly. No matter what I select, it places the right value in BOX E in that row. If I select Unacceptable, it puts a 1 there..if I selected Outstanding it puts a 5.

However- when doing the same thing for the next category in the row below (Qaulity of work), instead of putting a 1-5, it adds one and my values are now 6-10.

How do I get the options buttons to only be 1-5 for each row?


1575601743795.png
 

Attachments

  • 1575601570992.png
    1575601570992.png
    15.5 KB · Views: 7
Last edited:
Merged cells should be avoided at all costs, they will cause you nothing but pain & grief.

That said, what is the code you are currently using and can you please confirm if this statement is correct
in coluns A through H are merged cells
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Glad to see you realized using a large number of Option buttons is not a good way to do things.
And using merged cells can cause a lot of problems when using Vba.
Is this supposed to be a helpful reply...sounds very sarcastic and not helpful to me. Is that what these forums are designed for? I have met a ton of people on here that provide excellent feedback and actually help...and then there is this from a "well known member"...tell me, how does your reply answer me question with the problem I am having?
 
Upvote 0
Merged cells should be avoided at all costs, they will cause you nothing but pain & grief.

That said, what is the code you are currently using and can you please confirm if this statement is correct
Hi Fluff, thanks for the reply.

I am using this code
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("H:L")) Is Nothing Then
Cells(Target.Row, 2).Resize(, 5).ClearContents
Target.Value = "X"
Cells(Target.Row, 7) = Target.Column - 1
Cancel = True
End If
End Sub

This evaluation form has in column A the categories to evaluate (column A does not have merged cells)

Columns B-F are merged to allow a description of the categories for example: Column A3 : Quality of Work Column B3-F3 (merged):"Evaluate the employee on the accuracy, completeness, legibility, composition and order of their work"

Columns H:L Are non merged rating cells where the code applies for selection by inserting "X" but the merged cells above cause the problem.

Is there a way to manipulate the code to make this work? I can always put the descriptions of the categories on page 2- not wanting to do that unless there is a workaround for the current error.

Thanks again for the help...I am new to Excel, learning a lot and did not know merged cells in other columns can cause a problem with code applied to different ones...(it's the rows, I get it).
 
Upvote 0
Is this supposed to be a helpful reply...sounds very sarcastic and not helpful to me. Is that what these forums are designed for? I have met a ton of people on here that provide excellent feedback and actually help...and then there is this from a "well known member"...tell me, how does your reply answer me question with the problem I am having?
All I was trying to say was using a large number of controls like Option buttons can be hard to work with. And using merged cells can be difficult to work with. Sorry you felt like my comments were sarcastic. Please excuse me.
 
Upvote 0
What column do you want the score in?
 
Upvote 0
1575990799533.png


The merged cells are b-g that is causing the problem...the code range I am using to insert an X on double click is H-L- the score goes in M
 
Upvote 0
Give this a try
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("H:L")) Is Nothing Then
        Cells(Target.Row, 8).Resize(, 5).ClearContents
        Target.Value = "X"
        Cells(Target.Row, 13) = Target.Column - 7
        Cancel = True
    End If
End Sub
As long as there are no merged cells in cols H:M it should work
 
Upvote 0
Give this a try
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("H:L")) Is Nothing Then
        Cells(Target.Row, 8).Resize(, 5).ClearContents
        Target.Value = "X"
        Cells(Target.Row, 13) = Target.Column - 7
        Cancel = True
    End If
End Sub
As long as there are no merged cells in cols H:M it should work


You are a freakin' genius Excel wizard! Thank you so much! You should have a statue erected of you somewhere that pigeons cannot mess with it!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top