Gray background is shown when loading a workbook

  • Thread starter Thread starter Erik Beck Jensen
  • Start date Start date
E

Erik Beck Jensen

I have made at nice little - someone would say complicated - automated Excel
workbook. The workbook is used only inside my company.

To ensure that no one will open the workbook without macros enabled, the
book is always saved with an info page visible and all other pages very
hidden. When opening the workbook a macro will run and switch the visible
properties for the appropriated sheets.

The workbook is used once a month and will be opened by all my nearly 40
colleagues. It has performed very well for at least a year, but now a very
serious problem has occurred.

In the attempt to open the workbook the data will be loaded from disk, but
when finished only a gray background is shown on the screen below the
formula bar. You can't se the cursor, but it is possible to move around the
cursor while the appropriate values or formulas are shown in the formula
bar, therefore it is out of question, that the workbook is fully loaded.

The problem has "only" affected about 8 of my colleagues. When the affected
persons go to another computer to make a logon on a different computer, no
problems appear. When I go to an affected computer and make a logon as my
self, I don't get any problems at all.

I will be very happy if someone could give me a clue, what to do, to get rid
of the problem.



OS: Win XP Professional, Service Pack 1 Excel: Excel 2002 sp-2



Regards

Erik
 
It sounds like the workbook is being displayed off screen.

Try going into the VBE and executing code like this:

Sub MoveBook()
Workbooks("Filename.xls").Windows(1).Top = 1
Workbooks("Filename.xls").Windows(1).Left = 1
End Sub

Change filename.xls to the name of your workbook

See if that will make it visible.
 
Thank you for your reply. Your proposal will be on of the first thing to try
on Monday morning when my colleagues and I are back at work. Then I will let
you know the outcome.

For now I wish you a pleasant weekend





Regards

Erik
 
Sorry, but this seems not to work.


Running your code I get a run-time error 1004: Unable to set the Top
property of the Window class.

I have tried with hard coded filename and with variables, but got the same
result. I cut a bit of code from Excel help but it doesn't run any better.
See code below.



Sub MoveBook()

Dim wb As Workbook

Dim FileName As String



' For debug information

MsgBox "Start", vbInformation + vbOKOnly, "Sub: MoveBook"



Set wb = Application.ThisWorkbook

FileName = wb.Name



' For debug information

MsgBox "Filnavn: " & FileName, vbInformation + vbOKOnly, "Sub: MoveBook"



' This macro will unlock all sheets

LockAllSheets False



' Here we go

' From help (slightly modified)

wb.Windows(1).Caption = "Consolidated Balance Sheet" ' This line change
the Caption

wb.Windows("Consolidated Balance Sheet").Top = 1 ' This line lead to a
run-time error



' Hard coded

Workbooks("Prognose(Test).xls").Windows(1).Top = 1 ' This line lead to a
run-time error

Workbooks(FileName).Windows(1).Left = 1 ' This line lead to a run-time
error



' This macro will lock all sheets

LockAllSheets True



' For debug information

MsgBox "Over and out", vbInformation + vbOKOnly, "Sub: MoveBook"

End Sub





But how is that? When I open the workbook and with me, about 80% of my
colleagues open the same workbook, we encounter no problems. Then the
workbook will be saved and then opened again by another colleague, and he
won't be able to se anything. When the other colleague opens an quite
different workbook he have no problem.

By the way, inside the problematic workbook is a macro witch purpose is to
make a copy of the of the active sheet and place it in another workbook.
When running this macro, the copied sheet will be perfectly shown on the
screen.


Regards
Erik
 
Back
Top