ExcelBanter - View Single Post

Gareth[_6_]

No problem - good luck!

SYBS wrote:
Sorry not to have seen reply, couldn;t even find post when I looked this
morning and assumed I had sent it to the wrong area. Anyway thanks for both
responses and from Ian also. I looked at someone elses answer to a query
and thought I probably needed to link a cell to the checkbox and then use the
link cell in the formula. I tried it out and it does work but it's meaning
all the checkboxes (80 of them) have to be individually linked I think
because I have tried to take the simple way as I am a beginner and am a bit
scared of VB etc. I am working on it !

"Gareth" wrote:

I replied to your previous post of this (see below):

Note further, if you wish to use the same code for each checkbox you
could do something like this (working out which row the checkbox is on
from its linked cell):

Private Sub CheckBox1_Click()
WriteNilInRow CheckBox1.LinkedCell.Row
End Sub

Sub WriteNilInRow(lRow as long)
WIth activesheet.
.Cells(lRow, 3) = "NIL"
.Cells(lRow, 5) = "NIL"
.Cells(lRow, 6) = "NIL"
End With
End Sub

But you still need to have an event for each checkbox. Which is why I
suggested using single buttons at the top of the form in my earlier
response.

'----Previous reponse ----------
If you posted your code so far it might be easier to correct it.

Without seeing it, maybe this will point you in the right direction,
although it doesn't work from checkboxes, rather I would suggest placing
a button on your worksheet (use freeze panes to prevent it scrolling off
the visible window).

Sub WriteNilInSelectedRows()
Dim r as range
With Selection.Parent
For Each r In Selection.Rows
.Cells(r.Row, 3) = "NIL"
.Cells(r.Row, 5) = "NIL"
.Cells(r.Row, 6) = "NIL"
Next r
End With
End Sub

Sub WriteNilInAllRows()
With ThisWorkbook.ActiveSheet
Range(.Cells(7, 3), .Cells(87, 3)) = "NIL"
Range(.Cells(7, 5), .Cells(87, 6)) = "NIL"
End With
End sub

HTH,
G

SYBS wrote:

CHECK BOX HELP PLEASE.

My sheet contains 80 rows. (80 competitors). Their results are posted, one
row to a competitor. Starting on row 7 - 87

I have a check box (default €“ unchecked) in cell K7. Its purpose is to
indicate, whether the competitors times qualify for the event, There are
three timers. The three times are averaged to give €œThe Time€. If they run
over their time, this checkbox should be clicked and the previously entered
times in C7,E7,E8 should be replaced with NIL.
This check box will be in Col k on every row.
I can record a macro to do this on that line, but I want to use a relative
function so that I can copy it down the page to make give the same code to
every row, instead of having 80 macros. I have nearly been there but
everytime I get close, I seem to come up with something not quite right. Any
help very much appreciated.