Excel VBA Macro

[udpolo15]

I am trying to create a macro that performs as follows.

I have a list of items in Excel with a check box for each row. When the box is true, I want format the row in a certain way. When it is false, I want to to format another way. I am able to do this.

If I make an addition to the list, I want the list to be sorted by date (a column in the list). Again I can do this.

My problem is that once I add something and it is sorted, the check boxes that are true, are no long aligned with the correct items. Is there away to get the check box properties to follow the sort?

For example I have the following list

Row Date Check Box

1 12/5/2009 False (Format Normal)

2 12/8/2009 True (Format Bold)

Then want to add to row three a new entry

Row Date Check Box

1 12/5/2009 False (Format Normal)

2 12/8/2009 True (Format Bold)

3 12/7/2009 False (format normal)

this is auto sorted on the change to give me

Row Date Check Box

1 12/5/2009 False (Format Normal)

2 12/7/2009 True (Format Bold) should be False/Normal

3 12/8/2009 False (format normal) should be True/Bold

Now I can change the checkboxes so they move with the cells, but the problem is that each checkbox is name corresponds to it's initial row.

For example it is row2checkbox with a caption value of 2 so that I can uses the cells(row2checkbox.caption), j) code to format the correct row. If is use the sort, the caption no longer represents the correct row to make formatting changes. Is there way to solve this issue?

 

[MonteBiker]

I am usually pretty good at these but I guess I am not completely understanding the problem. You can send me the file or post it and I would be happy to take a look at it.

I am trying to create a macro that performs as follows.
I have a list of items in Excel with a check box for each row. When the box is true, I want format the row in a certain way. When it is false, I want to to format another way. I am able to do this.

If I make an addition to the list, I want the list to be sorted by date (a column in the list). Again I can do this.

My problem is that once I add something and it is sorted, the check boxes that are true, are no long aligned with the correct items. Is there away to get the check box properties to follow the sort?

For example I have the following list

Row Date Check Box

1 12/5/2009 False (Format Normal)

2 12/8/2009 True (Format Bold)

Then want to add to row three a new entry

Row Date Check Box

1 12/5/2009 False (Format Normal)

2 12/8/2009 True (Format Bold)

3 12/7/2009 False (format normal)

this is auto sorted on the change to give me

Row Date Check Box

1 12/5/2009 False (Format Normal)

2 12/7/2009 True (Format Bold) should be False/Normal

3 12/8/2009 False (format normal) should be True/Bold

Now I can change the checkboxes so they move with the cells, but the problem is that each checkbox is name corresponds to it's initial row.

For example it is row2checkbox with a caption value of 2 so that I can uses the cells(row2checkbox.caption), j) code to format the correct row. If is use the sort, the caption no longer represents the correct row to make formatting changes. Is there way to solve this issue?

 

[OSUguy98]

Is there a reason you're using check boxes? Why not just make a column that you'd put in a "Y" or something similar for a "check"... then Excel would sort it just as it does every other cell.... Or do the check boxes provide some other VBA-related feedback?

 

[udpolo15]

Thanks for the replies. I was using check boxes because they look prettier than having an X. Plus, with a check box it is a one click change versus an X which is a click then a key stroke. (not a big deal but just more intuitive

Plus I don't want the check box to show when I print. Easy to do with a check box, but with an X, you could white it out, but then it is hard to know if you have clicked it.

The more I think about it, I might just have it set up so on clicking a check box true, it cuts and pastes the cells in another worksheet. I was planning on having a running tally for a certain period of everything on my list, but I might be getting too complicated.

 

[MonteBiker]

Thanks for the replies. I was using check boxes because they look prettier than having an X. Plus, with a check box it is a one click change versus an X which is a click then a key stroke. (not a big deal but just more intuitive
Plus I don't want the check box to show when I print. Easy to do with a check box, but with an X, you could white it out, but then it is hard to know if you have clicked it.

The more I think about it, I might just have it set up so on clicking a check box true, it cuts and pastes the cells in another worksheet. I was planning on having a running tally for a certain period of everything on my list, but I might be getting too complicated.

Of course you could just use white font on your X or place your option boxes outside of your print area...

 

[OSUguy98]

2nd vote for the check boxes being out of your print area....

 

[udpolo15]

I am going with a command button that when click activates the following macro

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim selected

Dim rowcall

Dim ws As Worksheet

Dim insertpt As Range

Dim datept As Range

Sub checkbox_click()

Application.ScreenUpdating = False

Set insertpt = Range("A2")

Set datept = Range("M2")

Set ws = Worksheets("_task list")

If selected = True Then

Range(Cells(rowcall, 2), Cells(rowcall, 12)).Select

Selection.Copy

ws.Activate

Cells(1, 2).Select

Selection.Insert Shift:=xlDown

ws.datept.Value = Date

Sheets("_PM Dashboard").Range(Cells(rowcall, 2), Cells(rowcall, 12)).Clear

End I

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Everything works up until the Cells(1,2).Select. At which I get a runtime 1004 "Select method of Range class failed. I have tried a bunch of variations and can't get it to work. Any advice?

 

[MonteBiker]

Dim rowcall

Dim ws As Worksheet

Dim insertpt As Range

Dim datept As String

Private Sub CheckBox1_Click()

Dim rowcall As Integer

Dim test As String

rowcall = 1

test = "M2"

Application.ScreenUpdating = False

Set insertpt = Range("A2")

Set datept = "M2"

Set ws = Worksheets("_task list")

If CheckBox1.Value = True Then

Range(Cells(rowcall, 2), Cells(rowcall, 12)).Select

Selection.Copy

ws.Activate

Cells(1, 2).Select

Selection.Insert Shift:=xlDown

ws.Range(datept) = Date

Sheets("_PM Dashboard").Range(Sheets("_PM Dashboard").Cells(rowcall, 2), _

Sheets("_PM Dashboard").Cells(rowcall, 12)).Clear

End If

End Sub

I have this working in a sheet where I am calling the procedure with a checkbox, similar to your command button... Let me know if this helps. I did not have any problems with the Cells(1,2).Select but I had issues with a bit of the rest of the code.

I am going with a command button that when click activates the following macro
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim selected

Dim rowcall

Dim ws As Worksheet

Dim insertpt As Range

Dim datept As Range

Sub checkbox_click()

Application.ScreenUpdating = False

Set insertpt = Range("A2")

Set datept = Range("M2")

Set ws = Worksheets("_task list")

If selected = True Then

Range(Cells(rowcall, 2), Cells(rowcall, 12)).Select

Selection.Copy

ws.Activate

Cells(1, 2).Select

Selection.Insert Shift:=xlDown

ws.datept.Value = Date

Sheets("_PM Dashboard").Range(Cells(rowcall, 2), Cells(rowcall, 12)).Clear

End I

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Everything works up until the Cells(1,2).Select. At which I get a runtime 1004 "Select method of Range class failed. I have tried a bunch of variations and can't get it to work. Any advice?