Run code from UserForm

[DanGK1]

Hi

I am using checkboxes on a UserForm to call a macro but I have to have the required sheet active for the code to run. Even running the code from Visual Basic requires it and now puzzling me. Any suggestions or observations please

Sub HideRecurring
Application.ScreenUpdating = false
With Sheets("TestSheet")
lastrow = Range("c" & Rows.Count).End(xlUp).Row
BeginRow = 4
EndRow = lastrow
ChkCol = 5
For RowCnt = BeginRow To EndRow
if Cells(RowCnt, ChkCol).Value = "0" then
Cells(RowCnt,ChkCol).EntireRow.Hidden = True
end if
Next RowCnt
end with
Application.ScreenUpdating = true
end sub

 

[Fluff]

Your With statement is not doing anything. Anything that is part of the With should have a period at the start like

With Sheets("TestSheet")
Lastrow = .Range("c" & Rows.Count).End(xlUp).Row
BeginRow = 4

 

[DanGK1]

I have added the period but it still won’t run if not active on the sheet

 

[Trixterz]

If your going to use With Sheets then any cell in that sheets can be refer to as .Range("A3").Value but if your going to refer to a different sheet called My New Sheet then the code will like Sheets("My New Sheet").Range("A3").Value

 

[DanGK1]

I am running based on a checkbox on a UserForm and when the file opens, the sheet seen is Home. I want the code to run on TestSheet but I don’t want to have to ‘activate’ that sheet for the code to run

 

[Fluff]

I have added the period but it still won’t run if not active on the sheet

Did you add it to all the cells, ranges that are referring to that sheet?

 

[Fluff]

Can you also post you amended code?

 

[DanGK1]

Yes - here is the amended code

Sub HideRecurring()
Application.ScreenUpdating = False
With Sheets("TestSheet")
    lastrow = .Range("c" & Rows.Count).End(xlUp).Row
    BeginRow = 4
    EndRow = lastrow
    ChkCol = 5
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "0" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End With
Application.ScreenUpdating = True
End Sub

Sub UnHideRecurring()
Application.ScreenUpdating = False
With Sheets("TestSheet")
    lastrow = .Range("c" & Rows.Count).End(xlUp).Row
    BeginRow = 4
    EndRow = lastrow
    ChkCol = 5
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "0" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End With
Application.ScreenUpdating = True
End Sub

 

[Fluff]

You have not added the period to all the Ranges & cells, which you need to do.
Any object, property or method that belongs to the With must start with a period.

 

[DanGK1]

Ah got it, you mean like this. I wasn't sure Cells applied because I wasn't directly specifying anything there. That has done the trick, thank you very much

Sub HideRecurring()
Application.ScreenUpdating = False
With Sheets("TestSheet")
    lastrow = .Range("c" & Rows.Count).End(xlUp).Row
    BeginRow = 4
    EndRow = lastrow
    ChkCol = 5
    For RowCnt = BeginRow To EndRow
        If .Cells(RowCnt, ChkCol).Value = "0" Then
            .Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End With
Application.ScreenUpdating = True
End Sub

Sub UnHideRecurring()
Application.ScreenUpdating = False
With Sheets("TestSheet")
    lastrow = .Range("c" & Rows.Count).End(xlUp).Row
    BeginRow = 4
    EndRow = lastrow
    ChkCol = 5
    For RowCnt = BeginRow To EndRow
        If .Cells(RowCnt, ChkCol).Value = "0" Then
            .Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End With
Application.ScreenUpdating = True
End Sub