Trying to run a Macro from another sheet using ActiveX button

[ShannonP]

Hello.

A sheet called “MemberData” is where all information is input and it is also where my graph displays. Information input into the Memberdata sheet is fed to a table in a sheet called “CoverageandRatescalculation”. The only sheet that will be visible to the end user is the MemberData sheet.

Each time updated information is input into the MemberData sheet I need to go to the CoverageandRatescalculation sheet to press an ActiveX control button that runs two macros. One macro resets the table and the other updates the table. The macros work perfectly when run from the CoverageandRatescalculation sheet.

Try as I might I can’t find a way to run the macros that update the CoverageandRatescalculation sheet directly from the MemberData sheet. Any assistance would be greatly appreciated.

Here are the macros that will only work when run from the CoverageandRatescalculation sheet.

First macro

Sub HideRows()

StartRow = 32

EndRow = 114

ColNum = 1

For i = StartRow To EndRow

If Cells(i, ColNum).Value = "0" Then

Cells(i, ColNum).EntireRow.Hidden = True

Else

Cells(i, ColNum).EntireRow.Hidden = False

End If

Next i

End Sub

And the second Macro is

Sub HideRows_based_On_Values()


For Each cell In Range("A32:A120, H32:H120")

If cell.Value = 0 Then cell.EntireRow.Hidden = True

Next cell


End Sub

And here is the Macro for the Command Button located in the CoverageandRatescalculation sheet. All macros only work when run from the CoverageandRatescalculation sheet.

Private Sub CommandButton1_Click()

HideRows

HideRows_based_On_Values

End Sub

 

[DanteAmor]

Hi and welcome to MrExcel. And Happy New Year!

For the macro to work from any other sheet, you must reference the sheet you need, I show you one way to do it in macro1 and another way in macro2.

Try this:

Sub HideRows()
  Dim StartRow As Long, EndRow As Long, ColNum As Long
  Dim i As Long
  
  Application.ScreenUpdating = False
  With Sheets("CoverageandRatescalculation")
  
    StartRow = 32
    EndRow = 114
    ColNum = 1
    For i = StartRow To EndRow
      If .Cells(i, ColNum).Value = "0" Then
        .Cells(i, ColNum).EntireRow.Hidden = True
      Else
        .Cells(i, ColNum).EntireRow.Hidden = False
      End If
    Next i
    
  End With
  Application.ScreenUpdating = True
End Sub

Sub HideRows_based_On_Values()
  Dim cell As Range
  Application.ScreenUpdating = False
  For Each cell In Sheets("CoverageandRatescalculation").Range("A32:A120, H32:H120")
    If cell.Value = 0 Then cell.EntireRow.Hidden = True
  Next cell
  Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()
  HideRows
  HideRows_based_On_Values
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.
------

 

[ShannonP]

Thanks so much Dante and happy new year to you as well!
When I input the macros into the MemberData sheet and run them I am getting a run time error 9 when the code reaches the line containing the sheet name "CoverageandRatescalculation". This also happens at the same point in the second macro. Any thoughts?
Thanks again!

 

[RoryA]

That means that the sheet name is incorrect.

 

[DanteAmor]

I am getting a run time error 9

As @RoryA commented, the error is because the sheet does not exist.
I put the name of the sheet "CoverageandRatescalculation", check in your file that the sheet is actually called "CoverageandRatescalculation", check that it does not have blank spaces at the beginning or end of the name.

 

[ShannonP]

Thanks so much @DanteAmor and @RoryA. I figures it out, there was a space after "CoverageandRatescalculation" so it was actually "CoverageandRatescalculation ". I updated it and it works perfectly. Many many thanks Dante, your help is much appreciated!!!