Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: CheckBox Array . . . . I think?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    May 2002
    Location
    Ipswich, Suffolk, England
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Nov 2005
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

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

    Range("Vendor").Select

    what does "Vendoe" reference to ?

    Thank you.

  7. #7
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    "Vendor" is just a named range, I used to pull a label, you do not need it.
    JSW: Try and try again: "The way of the Coder!"

  8. #8
    New Member
    Join Date
    Nov 2005
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    JSW: Try and try again: "The way of the Coder!"

  10. #10
    New Member
    Join Date
    Nov 2005
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •