CheckBox Array . . . . I think?

[kotting]

I have a worksheet where information from inputboxes from another sheet is deposited. I have arranged the information into nice neat rows. The problem is:

I want a checkbox on each row to strikeout the text on that row if the checkbox on that row is 'checked'. There are 365 rows on this worksheet in use. The solution I have requires that each checkbox 'code area' be filled with - (something along the lines of

If text is strikeout = true then strikeout =false

else strikeout = true

this works but I have to write a macro for each Checkbox for each row. Is there a way to do this without having to write 365 macros?
Thanks

[crimlet]

I'm not sure what you are exactly trying to do, but I would put two suggestions forward. Either use the conditional formatting tool, or write a loop into your macro that repeats 360 times

[kotting]

On my worksheet I have 365 rows of data. On each row I have a checkbox. If a 'user' checks the checkbox, I want the text in that row (only) to 'strikeout'. Sounds so simple yet the solution eludes me.
Thanks

[Tom Schreiner]

Could you not just use a cell reference to do what you are doing?
Would require much less work and a macro with less that a dozen lines...
Place an x or something into a cell for each row, lets say in column S...
Then use a macro to run down this column...
If an X is found, do your strike out text...
I have used many controls before and withour the ability to use control arrays in VBA it is a pain in the @$#.
You can write code which will write code if you know what I mean...
Write one procedure and assign it to a variable, then use the mid, left, and right to change the code and then duplicate it on a worksheet... You can then copy it to a module.

I don't know....
Have fun!
Tom
Tom

[ This Message was edited by: TsTom on 2002-05-03 19:50 ]

[Joe Was]

In line with TsTom's note;

This code copies rows that are marked to a new sheet. I use it to move priority items to a short list. The code may get you started? JSW

Sub Priority()
'Find all the rows ("A:G") that have a "X" in column "A" copy
'that row to the next blank row on a different sheet.

Application.ScreenUpdating = False
Worksheets("Want_Full").Select
For Each r In Worksheets("Want_Full").UsedRange.Rows
n = r.Row
If Worksheets("Want_Full").Cells(n, 1) = "X" Then
Worksheets("Want_Full").Range(Cells(n, 2), Cells(n, 7)).Copy _
Destination:=Worksheets("Want_Now").Range("B65536").End(xlUp).Offset(1, -1)
Else
End If
Next r

Worksheets("Want_Full").Columns("A").Replace What:="X", Replacement:="*", _
SearchOrder:=xlByColumns, MatchCase:=True

Range("Vendor").Select
Selection.Copy

Sheets("Want_Full").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select

Sheets("Want_Now").Select
Range("B65536").End(xlUp).Offset(2, -1).Select
ActiveSheet.Paste

Range("A2").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
Range("A2:F2").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.RowHeight = 4
Range("A1:F1").Select
Application.CutCopyMode = True
Application.ScreenUpdating = True

End Sub

[atomicluis]

Hi,

I am trying to implement your code, but dont understand this line:

Range("Vendor").Select

what does "Vendoe" reference to ?

Thank you.

[Joe Was]

"Vendor" is just a named range, I used to pull a label, you do not need it.

[atomicluis]

Thanks for the response Joe Was,

I removed the following:

Range("Vendor").Select
Selection.Copy

...replaced "Want_Full" and "Want_Now" with my own sheets

run the code,but received the following error message:

Run-time error '1004'
Paste method of Worksheet class failed

on Debug, "ActiveSheet.Paste" is highlighted.

thank you

[Joe Was]

Remove all this from the code, I left it in so you can see how you can form,at and dress up the new list!
You don't need it!

Range("Vendor").Select
Selection.Copy

Sheets("Want_Full").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select

Sheets("Want_Now").Select
Range("B65536").End(xlUp).Offset(2, -1).Select
ActiveSheet.Paste

Range("A2").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
Range("A2:F2").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.RowHeight = 4
Range("A1:F1").Select


So you should have something like this:

Sub Priority()
'Find all the rows ("A:G") that have a "X" in column "A" copy
'that row to the next blank row on a different sheet.

Application.ScreenUpdating = False
Worksheets("Want_Full").Select
For Each r In Worksheets("Want_Full").UsedRange.Rows
n = r.Row
If Worksheets("Want_Full").Cells(n, 1) = "X" Then
Worksheets("Want_Full").Range(Cells(n, 2), Cells(n, 7)).Copy _
Destination:=Worksheets("Want_Now").Range("B65536").End(xlUp).Offset(1, -1)
Else
End If
Next r

Worksheets("Want_Full").Columns("A").Replace What:="X", Replacement:="*", _
SearchOrder:=xlByColumns, MatchCase:=True

Sheets("Want_Full").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select

Application.CutCopyMode = True
Application.ScreenUpdating = True

End Sub

[atomicluis]

How come some rows are not pasted into the destination sheet ? the row that did not get pasted still got a "*" on column A.

For example: If the source sheet contains any number on cell "B2", and 'X' on cell A1. (destination sheet is empty). The number on cell B2 does not get pasted on the destination sheet.

thanks for the help. as you can assume I'm new to excel. thanks again.