Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CHECK BOX HELP NEEDED
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CHECK BOX HELP NEEDED
Post the macro code you recorded and let folks have a look at it.
-- Ian -- "SYBS" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CHECK BOX HELP NEEDED
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CHECK BOX HELP NEEDED
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CHECK BOX HELP NEEDED
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check box hero needed please. | Excel Discussion (Misc queries) | |||
formula needed to sum check marks | Excel Worksheet Functions | |||
formula needed to sum check marks | Excel Worksheet Functions | |||
formula needed to sum check marks | Excel Worksheet Functions | |||
CHECK BOX HELP NEEDED PLEASE | Excel Programming |