Skip to Content
0
Jul 11, 2011 at 04:19 PM

VBA issue in BEx workbook (run-time error 91)

547 Views

I developed a workbook that, in the first sheet, has a button to refresh variables (BUTTON_5, where CMD = SHOW_VARIABLE_SCREEN).

Now I want that the workbook is refreshed as soon as it is opened, even when the user opens it from his local PC--and not just when it opens it from the BW server.

Since I can't take advantage of the BEx workbook properties, I therefore added the following VBA code, in ThisWorkbook object:

Private Sub Workbook_Open()
    Sheet1.BUTTON_5_Click       
End Sub

When I open the workbook it works ok. The variables window popups and the workbook get refreshed. However, when a user opens it, he gets the error: Run-time error 91 object variable or With block variable not set

The error is raised by the line:

Sheet1.BUTTON_5_Click

What am I missing? I also tried to declare Sheet1, like:

Private Sub Workbook_Open()
    Dim Sheet 1 As Object
    Set Sheet1 = ThisWorkbook.Worksheets.Item("Sheet1")
    Sheet1.BUTTON_5_Click       
End Sub

But it doesn't change at all--the workbook won't run in the user environment!

Edited by: Davide Cavallari on Jul 11, 2011 6:19 PM