Need VBA Help on Unhiding Row Based on Value + Rows Below

[gwomfb]

I tried putting together a code for a checkbox in Sheet "Budget" which will unhide 4 rows on Sheet "Vendor Cost". When the checkbox is ticked, it should unhide the row on Sheet "Vendor Cost" which contains value "1000L" on column A and 3 more rows underneath it (no value on column A). I can't specify row numbers because there is an option in the sheet to add more rows so column A entries may change row address.

Here's what I have so far but not working.

Private Sub CheckBox1_Click() Dim c As Range

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Budget")
For Each c In ThisWorkbook.Worksheets("Vendor Cost").Range("A4:A1000" & ThisWorkbook.Worksheets("Vendor Cost").Cells(.Rows.Count, "A").End(xlUp).Row).Cells
If IsError(c) Then c.Value2 = "1000L" 'Hides errors as well
c.Resize(4).EntireRow.Hidden = (c.Value = "1000L" And ThisWorkbook.Worksheets("Vendor Cost").CheckBox1.Value) = False
Next
End With
Application.ScreenUpdating = True

End Sub

Can anyone take a look and teach me where I'm wrong? Please and thank you.

 

[HaHoBe]

Hi,

according to description

for a checkbox in Sheet "Budget"

while in code

ThisWorkbook.Worksheets("Vendor Cost").CheckBox1

Maybe try

Private Sub CheckBox1_Click()
Dim c As Range

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Vendor Cost")
  For Each c In .Range("A4", .Cells(.Rows.Count, "A").End(xlUp))
   'If you find a matching value, indicate success by setting bln to true and exit the loop;
   'otherwise, continue searching until you reach the end of the workbook.
    If IsError(c) Then c.Value2 = "1000L" 'Hides errors as well
    If c.Value = "1000L" Then c.Resize(4).EntireRow.Hidden = ThisWorkbook.Worksheets("Budget").CheckBox1.Value
  Next c
End With
Application.ScreenUpdating = True
End Sub

Ciao,
Holger

 

[gwomfb]

Hi,

according to description


while in code

ThisWorkbook.Worksheets("Vendor Cost").CheckBox1

Maybe try

Private Sub CheckBox1_Click()
Dim c As Range

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Vendor Cost")
  For Each c In .Range("A4", .Cells(.Rows.Count, "A").End(xlUp))
   'If you find a matching value, indicate success by setting bln to true and exit the loop;
   'otherwise, continue searching until you reach the end of the workbook.
    If IsError(c) Then c.Value2 = "1000L" 'Hides errors as well
    If c.Value = "1000L" Then c.Resize(4).EntireRow.Hidden = ThisWorkbook.Worksheets("Budget").CheckBox1.Value
  Next c
End With
Application.ScreenUpdating = True
End Sub

Ciao,
Holger

Hi, thanks for the help. I tried fresh with the code you edited but it's not doing anything unfortunately. By default, rows A4+ on Vendor Cost are hidden. I want the checkbox which is in Budget to unhide. I tried using it with the rows unhidden and ran the code and it hid them so I think it does the opposite?

 

[gwomfb]

Hi,

according to description


while in code

ThisWorkbook.Worksheets("Vendor Cost").CheckBox1

Maybe try

Private Sub CheckBox1_Click()
Dim c As Range

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Vendor Cost")
  For Each c In .Range("A4", .Cells(.Rows.Count, "A").End(xlUp))
   'If you find a matching value, indicate success by setting bln to true and exit the loop;
   'otherwise, continue searching until you reach the end of the workbook.
    If IsError(c) Then c.Value2 = "1000L" 'Hides errors as well
    If c.Value = "1000L" Then c.Resize(4).EntireRow.Hidden = ThisWorkbook.Worksheets("Budget").CheckBox1.Value
  Next c
End With
Application.ScreenUpdating = True
End Sub

Ciao,
Holger

Ah I tweaked it and finally got it working. Thanks @HaHoBe .

Working code:

Private Sub CheckBox1_Click()
Dim c As Range

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Vendor Cost")
For Each c In .Range("A4:A500", .Cells(.Rows.Count, "A").End(xlUp))
'If you find a matching value, indicate success by setting bln to true and exit the loop;
'otherwise, continue searching until you reach the end of the workbook.
If IsError(c) Then c.Value2 = "1000L" 'Hides errors as well
If c.Value = "1000L" Then c.Resize(4).EntireRow.Hidden = ThisWorkbook.Worksheets("Budget").CheckBox1.Value = False
Next c
End With
Application.ScreenUpdating = True
End Sub

Let me know if it makes sense why it works or if you see any trouble I may encounter in the future.

 

[HaHoBe]

Hi gwomfb,

please make good use of code-tags when posting your procedures here.

    If c.Value = "1000L" Then c.Resize(4).EntireRow.Hidden = Not ThisWorkbook.Worksheets("Budget").CheckBox1.Value

does the same.

Holger

 

[gwomfb]

Hi gwomfb,

please make good use of code-tags when posting your procedures here.

    If c.Value = "1000L" Then c.Resize(4).EntireRow.Hidden = Not ThisWorkbook.Worksheets("Budget").CheckBox1.Value

does the same.

Holger

Ah I'll try that.

Yeah I tried editing but couldn't afterwards :/

Thanks again!

 

[HaHoBe]

Hi,

for us as users Edit is only available for 10 minutes after publishing. A hint about posting: How to Post Your VBA Code.

Holger