CheckBox Array . . . . I think?

kotting

New Member
Joined
Mar 18, 2002
Messages
17
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi,

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

Range("Vendor").Select

what does "Vendoe" reference to ?

Thank you.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top