This is probably a simple command I'm overlooking. I'm working on a worksheet to track progress of orders. It's a fairly simple set-up. In one row is the name of the ordered item, a drop-down list detailing where the item is located, and check boxes (all Forms-based) to track the progress of the item. The last check box (ActiveX control) is in a column (starting from G3) marked "Completed." When this is checked, the entire row background switches to red.
At the top of the worksheet is a Master Checkbox (Also ActiveX control) marked "Hide Completed." This box is set up to hide all red rows in the worksheet.
Here's the current code:
So, Checkbox1 is in Cell G3, Linked to cell Z3 (just to get the true/false text out of the viewable area), and when Z3 returns the falue of "True" (when Checkbox 1 is checked), the color index for the row background is set to Red.
The Master Checkbox looks to range Blah2 to seek out red rows and hide them. The Master Checkbox when checked also hides Checkbox1.
This works great. However, with this code I would need to alter the code for each row individually (like, for Row 4, Checkbox2 will need to be manually linked to Cell Z4, and then Checkbox2 would also need to be manually set to visible.false when Master is checked.)
This worksheet will contain hundreds upon hundreds of rows, each of which need their own control to turn the background to red AND to have the Completed checkbox turn invisible when the Master is hiding all Completed rows. As I've said before, I'm still very much an infant when it comes to VBA (this is the first code I've written without asking for direct help), so I'm unsure what commands should be used to accomplish this.
Is there a VBA control that works similarly to the INDIRECT command that will adjust itself to reflect its current row via copy/paste? Or is there a regular Forms control I'm overlooking that will work this way? (Am I overthinking this?)
At the top of the worksheet is a Master Checkbox (Also ActiveX control) marked "Hide Completed." This box is set up to hide all red rows in the worksheet.
Here's the current code:
Code:
Private Sub CheckBox1_Click()
Dim Blah As Range
Set Blah = Range("z3")
If Blah.Value = True Then
Blah.EntireRow.Interior.ColorIndex = 3
Else
Blah.EntireRow.Interior.ColorIndex = 2
End If
End Sub
Private Sub Master_Click()
Dim Blah2 As Range
Set Blah2 = Range("a3:a1000")
If Master.Value = True Then
For Each cl In Blah2
If cl.Interior.ColorIndex = 3 Then
cl.EntireRow.Hidden = True
End If
Next cl
Else
For Each cl In Blah2
If cl.Interior.ColorIndex = 3 Then
cl.EntireRow.Hidden = False
End If
Next cl
End If
If Master.Value = True Then
CheckBox1.Visible = flase
Else
CheckBox1.Visible = True
End If
End SubSo, Checkbox1 is in Cell G3, Linked to cell Z3 (just to get the true/false text out of the viewable area), and when Z3 returns the falue of "True" (when Checkbox 1 is checked), the color index for the row background is set to Red.
The Master Checkbox looks to range Blah2 to seek out red rows and hide them. The Master Checkbox when checked also hides Checkbox1.
This works great. However, with this code I would need to alter the code for each row individually (like, for Row 4, Checkbox2 will need to be manually linked to Cell Z4, and then Checkbox2 would also need to be manually set to visible.false when Master is checked.)
This worksheet will contain hundreds upon hundreds of rows, each of which need their own control to turn the background to red AND to have the Completed checkbox turn invisible when the Master is hiding all Completed rows. As I've said before, I'm still very much an infant when it comes to VBA (this is the first code I've written without asking for direct help), so I'm unsure what commands should be used to accomplish this.
Is there a VBA control that works similarly to the INDIRECT command that will adjust itself to reflect its current row via copy/paste? Or is there a regular Forms control I'm overlooking that will work this way? (Am I overthinking this?)
