Auto fill?

[cbm550]

I need to be able to click on a box and have it fil that box with a check, X, or other fill and have that fill go away if I click on that box again. Can I do that?

 

[Peter_SSs]

You are correct. Now, I am assuming that I need to give you the exact cells that I have that need the check mark? If so, I have 4 sheets that I have between 50 and 70 check boxes each.

Is there a pattern? eg
Are they all in column C and the data entry ones in other columns?

Are they on rows 5, 10, 15 etc with the data entry on other rows?

Whenever you click on any cell in any of the 4 sheets, exactly how do we work out if that cell is one that should add/remove "x" or not?

 

[cbm550]

There is no pattern. They are check boxes to answer questions on a form. There is a question of race/ethnicity and you have to check one box. Then there is a yes/no question. I just want to click once on the box to check it and click once on the box to uncheck it if I need to change the answer to the question.

But to answer your question, the check boxes are not in a row/column by themselves and there may be a box for text or number input onthe same row/column.

I know that is a little much, but if there is a formula to go by, I can easily type in the required info. I don't know how complicated it is though.

 

[Peter_SSs]

When you click on one of the merged cells, there has to be some way to tell if it is to have an "x" inserted/removed or whether it has to allow the user to enter other data. If there is no pattern then, yes, you would have to provide the exact cells that need the check mark. You could try some code like this, where the cells listed in myRange are the upper left cells of each merged range that requires the "x" to be added cleared.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Range("C3,H7,J12,G15:G18")<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, myRange) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> ActiveCell.FormulaR1C1<br>            <SPAN style="color:#00007F">Case</SPAN> ""<br>                ActiveCell.FormulaR1C1 = "x"<br>            <SPAN style="color:#00007F">Case</SPAN> "x"<br>                ActiveCell.FormulaR1C1 = ""<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[cbm550]

So, from what I am seeing, just copy that code and where it says "Set myrange = " just input all of the cell numbers that need to be checked and unchecked. Also, does this code allow for one click to check and uncheck?

 

[Peter_SSs]

So, from what I am seeing, just copy that code ...

Yes, right click the sheet name tab and choose "View Code" and paste the code in the main right hand pane.

... and where it says "Set myrange = " just input all of the cell numbers that need to be checked and unchecked.

Correct.

Also, does this code allow for one click to check and uncheck?

Yes

 

[cbm550]

Thank you so much. I will try it now and report back on how it works.

 

[cbm550]

I put in all of the cells. When I clicked on a cell it comes up with "Run-time error '1004': Method 'Range' of object'_Worksheet' failed"

 

[cbm550]

Here is the code:
Now when I typee in the cell numbers, I just typed it across and it did not wrap the text to another line.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range

Set myRange = Range("e50, e54, ab52, ab54, m58, bi50, bi54, e70, e74, e78, e82, aw70, aw74, aw78, aw82, cs70, cs74, cs78, e93, e97, e101, e105, ba93, ba97, ba101, cr93, cr97, d118, d122, d126, g130, g134, d138, d142, d146, ak118, ak122, ak126, ak130, ak134, ak138, ak142, bz118, bz122, bz126, bz130, bz134, bz138, bz142, bz146, dd118, dd122, dd126, dd130, d156, d160, bn156, bn160, bn164, bn168, bn172, bn176, bn180, bn184, d183, d187, bt193, cm193")
If Not Intersect(Target, myRange) Is Nothing Then
Select Case ActiveCell.FormulaR1C1
Case ""
ActiveCell.FormulaR1C1 = "x"
Case "x"
ActiveCell.FormulaR1C1 = ""
End Select
End If
End Sub

 

[Peter_SSs]

Too many arguments. Start your code off like this

Dim myRange As Range
Dim myRange1 As Range
Dim myRange2 As Range
Dim myRange3 As Range

Set myRange1 = Range(" list about a third of your cells here ")
Set myRange2 = Range(" list about a third of your cells here ")
Set myRange3 = Range(" list about a third of your cells here ")
Set myRange = Union(myRange1, myRange2, myRange3)
Rest of code

 

[cbm550]

Thank you, I will try that in the morning.