VBA Sub runs from Developer Ribbon and macro book but not when assigned to a button

[Dispa]

Hello there!

I've been using VBA for a few months and so far it's been a very fulfilling experience. Just this one time i've bumped into a weird problem and were not able to find a solution.

It goes like this. I am designing a "Settings" tab on a fairly large report. It has data sheets, proccessing sheets and exhibition sheets. Just the latter are user friendly, and the others are meant to be accessed by an administrator only. Since the administrator is not VBA trained as well, my efforts right now are directed at making the settings administrator-friendly. My data tabs and proccessing tabs all work underneath the report being "Very Hidden". So to allow for sporadic updates, i've built a macro that unhides these sheets, whose names are stored in a named range called "hide".

Here's what my code looked like:

[COLOR=#373E4D][FONT=helvetica]Sub Exhibit()

Dim ws As Range
Dim st As String

For Each ws In Range("Hide")
st = Cells(ws.Row, ws.Column).Value
Worksheets(st).Visible = True
Next ws

End Sub[/FONT][/COLOR]

I've found that it works when i run it from the Macros button on the Developer Ribbon, and also directly from my Visual Basic Macro Book. But when i assign it to a form (my "button" of sorts), it returns a Runtime Error # 9.

In order to troubleshoot i've separated the data and proccessing sheets and found that the error is the same for both (using " ' " to make lines turn to comments on the code):

Sub Exhibit()

Dim ws As Range
Dim st As String


For Each ws In ActiveSheet.Range("Hidedata")
On Error Resume Next
st = Cells(ws.Row, ws.Column).Value
Sheets(st).Visible = True
Next ws


'For Each ws In ActiveSheet.Range("Hideprocs")
'On Error Resume Next
'st = Cells(ws.Row, ws.Column).Value
'Sheets(st).Visible = True
'Next ws


End Sub

Running the code with each one of the two parts activated at wonce yielded the same result: Just two of the four sheets in each case were made visible, and i did not get an error because of the "On Error Resume Next".
I guess i can live without this code working, but I would very much have some light shed on why could it work from the Macro Book and not from the Button. Any help will be very appreciated! Thanks for the attention to anyone who has read this far!

Using Excel 2013 on Windows 10.

 

[davesexcel]

I wonder if it the code doesn't understand what wb or worksheet you are referring to.

Sub Exhibit()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range

    Set wb = ThisWorkbook
    Set ws = Sheets("MySheet")    'sheet with the range 'Hide'

    With wb
        With ws
            For Each rng In .Range("Hide")
                Sheets(rng.Value).Visible = False
            Next rng
        End With
    End With

End Sub

 

[Dispa]

That worked! Thank you very much!