Macro does not work after the program has been restarted in excel.Run time error 2147417848 (8001010

[knut]

I have a serious problem which i can not solve. I have buildt a simple
password login macro in excel making it possible to give different
users different information.

The macro works fine when I made it, however After I save it and
restart it again the above message pops up, when I start the Macro.

the macro I have made look like this,
I seems like the macro crash on this line ( Sheets(i).Visible =
xlSheetVeryHidden)

Private Sub showAll()
Dim i As Integer
For i = 2 To Sheets.Count
Sheets(i).Visible = xlSheetVisible
Next i
End Sub

Private Sub CommandButton1_Click()
Dim vPasswords As Variant
Dim result As String
Dim i, x As Integer
Dim arrLength As Integer


x = 0



vPasswords = Array("OSLO", "Northern Europe", "FERRARI", "Southern
Europe", "HEINEKEN", "Central Europe")
arrLength = UBound(vPasswords) - 1


For i = 2 To Sheets.Count
Sheets(i).Visible = xlSheetVeryHidden
Next i

result = Application.InputBox(prompt:="Enter password", Type:=2,
Title:="Port Report 2005")

If result = "system" Then
For i = 2 To Sheets.Count
Sheets(i).Visible = xlSheetVisible
Next i
Else

Do
If result = "False" Then Exit Sub

For i = LBound(vPasswords) To UBound(vPasswords) Step 2
If vPasswords(i) = result Then
With Sheets(vPasswords(i + 1))
.Visible = True
.Activate
x = x + 1
End With

End If

If i = arrLength Then Exit Do

Next i

Loop While True
If x = 0 Then
MsgBox "You entered wrong password, please try
again, If you miss your password pls contact Knut Espegren"
'Else
'Sheets(2).Visible = xlSheetVisible
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


I apprecatie any help with this one as it is driving me crazy!

Thank you in advance

Brgds

Knut

 

[paul.robinson]

Hi
Is the workbook protected? If it is, you will have to remove protection
before making the sheets hidden or visible (as you are changing the
workbook).

regards
Paul

 

[ymze]

Hi

The workbook is not protected...
The strange thing is that the macro work the second time you try in the
visual basic modul, without doing any changes.
this does not work in Excel, as excel freezes when you start the macro
in excel.

However I might think that another macro which is running when the
computer is started might interupt the other macro. I use this macro to
hide all sheets exept the frontpage where the login function is
located.
Can this macro play a part?

Sub Auto_open()
Application.Calculation = xlCalculationAutomatic
Sheets("Login").Visible = xlSheetVisible
Sheets("Northern Europe").Visible = xlSheetVeryHidden
Sheets("Central Europe").Visible = xlSheetVeryHidden
Sheets("Country Overview").Visible = xlSheetVeryHidden
Sheets("Sales Site").Visible = xlSheetVeryHidden
Sheets("Sales Port").Visible = xlSheetVeryHidden
Sheets("Logistics_Info").Visible = xlSheetVeryHidden
Sheets("Charts_Overview").Visible = xlSheetVeryHidden
Sheets("Port Sales").Visible = xlSheetVeryHidden
Sheets("Region Overview").Visible = xlSheetVeryHidden
Sheets("Chart per Site").Visible = xlSheetVeryHidden
Sheets("Southern Europe").Visible = xlSheetVeryHidden
Sheets("Area Overview").Visible = xlSheetVeryHidden
Sheets("Area per Country").Visible = xlSheetVeryHidden
Sheets("1").Visible = xlSheetVeryHidden
Sheets("Port Sales").Visible = xlSheetVeryHidden
Sheets("Sales Site Calc").Visible = xlSheetVeryHidden
Sheets("Site Sales").Visible = xlSheetVeryHidden
Sheets("Hierarchy Port").Visible = xlSheetVeryHidden
Sheets("hierarchy Acc").Visible = xlSheetVeryHidden
Sheets("Acc Sales").Visible = xlSheetVeryHidden
Sheets("Input Port Sales").Visible = xlSheetVeryHidden
Sheets("Input Acc Sales").Visible = xlSheetVeryHidden
Sheets("Input Costs").Visible = xlSheetVeryHidden
Sheets("Input Freight_Agent Costs").Visible = xlSheetVeryHidden
Sheets("Input inventory").Visible = xlSheetVeryHidden
Sheets("Input HR").Visible = xlSheetVeryHidden
Sheets("IDC").Visible = xlSheetVeryHidden
Sheets("Login").Select
MsgBox ("Welcome to the Port Report 2005, Login by typing the
Password for you Area, it is important that you it in Capitol letters,
if any errors contact Knut Espegren")
End Sub

Thank you again for your support!

Knut

 

[Tom Ogilvy]

Your autoopen macro should be completed when the user is presented the
msgbox and clicks OK.

 

[ymze]

ok, thanks. However it doesnt solve the problem

Do anyone have any ideas?

 

[Dave Peterson]

Is that code in a General module--or is it in ThisWorkbook or one of the sheet
modules?

 

[ymze]

The first code, part 1, (the login function) is part of sheet 1 while
the rest of the code in the workbook is code in the general module.

 

[Dave Peterson]

auto_open should be in a general module.

The first code, part 1, (the login function) is part of sheet 1 while
the rest of the code in the workbook is code in the general module.