MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Set Check True if user enters data in row ???


Posted by RSL on February 05, 2002 9:37 AM

I have a Check set up default False. If the user enters data in the current row.cell range, then I want to set the Check true. Can this be done?


Posted by Damon Ostrander on February 05, 2002 2:56 PM

Hi RSL,

Yes, this can be done quite easily. If you can clarify a bit by posting a simple example, I will provide a method. For example, I need to know if Check is a cell in the row of interest, or is it somewhere else, or is it SOMETHING else, like a checkbox control?

Damon

Posted by RSL on February 06, 2002 6:18 PM

Thanks, Damon
My set up is for sales.
Check Product Jan Feb Mar Apr May ...
I want to put Check True if any field Jan thru Dec has something typed into it.
Hope that gives you enough info.
Bob

Posted by Damon Ostrander on February 08, 2002 8:58 AM

Hi Bob,

Just use the formula:

=(C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2="")

in the Check column, row 2 and autofill it down.

Happy computing.

Damon


Posted by Damon Ostrander on February 08, 2002 12:47 PM

Re: Set Check True if user enters data in row --solution.

Hi again Bob,

I posted a response this morning, but I guess the bulletin board server didn't accept it. Here it is again:

Just use this formula in the Check cell A2 and fill it down.

=(C2&D2&E2&F2&G2&H2&I2&J2&K2&L2="")

Damon

Posted by RSL on February 11, 2002 9:11 AM

Thanks, Damon
That works. What if I have data in the cells, and want that Check = True if the user changes it?
I had thought about "if keypress, then set check true", but I couldn't get it to work.
Any more ideas?
Bob

Posted by Damon Ostrander on February 12, 2002 12:48 PM

Hi again Bob,

Yes, if you want check = True if the cell is edited, this is easy, but requires a different approach. Use the Worksheet's Change event. For example, say you want to set the corresponding cell in column 1 to True if any cell in columns 2 to 9 is changed:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 1 And Target.Column < 10 Then
Cells(Target.Row, 1) = True
End If

End Sub

Just put this code in the worksheet's event code module (right-click on the worksheet's tab, select View Code..., paste this code into the VBE code pane).

Cheers.

Damon

Posted by RSL on February 14, 2002 9:27 AM

Damon, that did it ! Thanks very much