PDA

View Full Version : Compile error in hidden module: ThisWorkbook


PaSha
12-06-2007, 06:47 AM
helo guys... i have a problem...

my workbook worked fine couple days befor... but now i realised something is not workig ok...

so i'm gonna try explain to you what's wrong maybe someone see's the mistake i don't :doh: ...

i have i simple workbook, on which are only values of time like day in week, in month, week in month and so on...

among this values the code behind works after the users clicks start button...

when a week in month is 2nd and day 3th in week it opens a special workbooks for week 2 and selects sheet for specific day in this point day 3...

but now if i make START the workobook opens but it doesn't selects the sheet and i get that erorr Compile error in hidden module: ThisWorkbook ...

so if someone nows were the problem is help me please...
i can also copy my code in here but it's a huge list... but anyway i can...

so thanks in forward...
rory
12-06-2007, 06:52 AM
It seems you have some code in the ThisWorkbook module of your workbook that is incorrect. Open the workbook, switch to the VBEditor, double click on the ThisWorkbook object of your project in the Project Explorer window, then choose Debug->Compile VBAProject from the menu and see what errors get flagged (if any).
PaSha
12-06-2007, 07:01 AM
It seems you have some code in the ThisWorkbook module of your workbook that is incorrect. Open the workbook, switch to the VBEditor, double click on the ThisWorkbook object of your project in the Project Explorer window, then choose Debug->Compile VBAProject from the menu and see what errors get flagged (if any).

but there is the problem ...

i have no code in The ThisWorkbook, i deleted it becouse it was useless...
and when i run the debug it highlights me the row which is just ok...

it highliths:
Set wb = Workbooks.Open(strPath)
rory
12-06-2007, 07:06 AM
Can you post the workbook? That looks OK - even if strPath is not actually a valid path, you shouldn't get a compile error on it.
PaSha
12-06-2007, 07:18 AM
yes i know that there shouldn't be a error, becouse everything is correct, all the path and everyathing, but the error is still there :banghead:

so here is my code, hope you can get true it, becouse of the mess...

Private Sub CommandButton1_Click()

Dim a As Long, b As Long, wb As Workbook, wb2 As Workbook, wbRuckstand1 As Workbook, ws As Worksheet, strPath As String, strSheetName As String
Dim dan As Long, mesec As Long, leto As Long
dan = Range("i10").Value
mesec = Range("i14").Value
leto = Range("i18").Value

'copy values when the first of month comes

If leto = 2007 And dan = 1 And mesec = 12 Then

Set wbRuckstand1 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\Ruckstand1.xlsx")

Workbooks("Ruckstand1.xslx").Sheets("Jahr").Range("c62:r62").Value = Workbooks("Ruckstand1.xslx").Sheets("Woche 1").Range("g20:v20").Value
End If

If leto = 2008 And dan = 1 And mesec Then

Set wbRuckstand1 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\Ruckstand1.xlsx")

With Sheets("Woche 1")

Select Case mesec

Case 1
Sheets("Jahr").Range("c67:r67").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 2
Sheets("Jahr").Range("c12:r12").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 3
Sheets("Jahr").Range("c17:r17").Value = Sheets("Woche 1").Range("g20:v20").Value
Case 4
Sheets("Jahr").Range("c22:r22").Value = Sheets("Woche 1").Range("g20:v20").Value
Case 5
Sheets("Jahr").Range("c27:r27").Value = Sheets("Woche 1").Range("g20:v20").Value
Case 6
Sheets("Jahr").Range("c32:r32").Value = Sheets("Woche 1").Range("g20:v20").Value
Case 7
Sheets("Jahr").Range("c37:r37").Value = Sheets("Woche 1").Range("g20:v20").Value
Case 8
Sheets("Jahr").Range("c42:r42").Value = Sheets("Woche 1").Range("g20:v20").Value
Case 9
Sheets("Jahr").Range("c47:r47").Value = Sheets("Woche 1").Range("g20:v20").Value
Case 10
Sheets("Jahr").Range("c52:r52").Value = Sheets("Woche 1").Range("g20:v20").Value

End Select

End With

End If



'DELETE VALUES IN SHEETS WHEN THE WEEK NUMBER IS 1ST

If dan = 1 Then
Set oWB = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag1.xlsm")
Windows(oWB.Name).Visible = False
With oWB.Worksheets(1)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(2)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(3)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(4)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(5)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(6)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
Windows(oWB.Name).Visible = True
oWB.Close savechanges:=True

End If


If dan = 1 Then
Set oWB = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag2.xlsm")
Windows(oWB.Name).Visible = False
With oWB.Worksheets(1)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(2)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(3)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(4)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(5)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(6)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
Windows(oWB.Name).Visible = True
oWB.Close savechanges:=True

End If
If dan = 1 Then
Set oWB = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag3.xlsm")
Windows(oWB.Name).Visible = False
With oWB.Worksheets(1)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(2)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(3)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(4)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(5)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(6)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
Windows(oWB.Name).Visible = True
oWB.Close savechanges:=True

End If
If dan = 1 Then
Set oWB = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag4.xlsm")
Windows(oWB.Name).Visible = False
With oWB.Worksheets(1)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(2)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(3)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(4)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(5)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(6)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
Windows(oWB.Name).Visible = True
oWB.Close savechanges:=True

End If
If dan = 1 Then
Set oWB = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag5.xlsm")
Windows(oWB.Name).Visible = False
With oWB.Worksheets(1)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(2)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(3)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(4)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(5)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
With oWB.Worksheets(6)
.Unprotect Password:="ihlas"
.Range("i7:i14").ClearContents
.Protect Password:="ihlas"
End With
Windows(oWB.Name).Visible = True
oWB.Close savechanges:=True

End If









' OPEN SPECIAL WORKBOOK ON SPECIAL MWEEK
Set wb2 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\ProduktNumern.xlsx")


a = Range("i12").Value
b = Range("i16").Value

strPath = "C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag" & a & ".xlsm"

Set wb = Workbooks.Open(strPath)

With wb

Select Case b

Case 1
strSheetName = "PONEDELJEK-Montag"
Case 2
strSheetName = "TOREK-Dienstag"
Case 3
strSheetName = "SREDA-Mitwoch"
Case 4
strSheetName = "ČETRTEK-Donnerstag"
Case 5
strSheetName = "PETEK-Freitag"
Case 6
strSheetName = "SAMSTAG-Sobota"

End Select

Sheets(strSheetName).Select


End With


End Sub
rory
12-06-2007, 07:40 AM
Try adding:
Dim oWb as Workbook
to your declaration section. It may be that you have added Option Explicit to your code module, which requires you to declare all variables.
PaSha
12-06-2007, 07:43 AM
oh my God, :banghead: :banghead: i hate when such stupid mistakes happen...

the problem was actually not in that workbook, but in the workbook which opens after clicking on the button... in that workbook i had in the ThisWorkbook some mistake...

oh man, sorry people... sometimes i just write to fast...

and rory thank you very much, my mistake, lol...

:mkay
rory
12-06-2007, 07:52 AM
PS You may want to try this version of your code:
Const mc_strPassword As String = "ihlas"
Private Sub CommandButton1_Click()

Dim a As Long, b As Long
Dim wb As Workbook, wb2 As Workbook, wbRuckstand1 As Workbook
Dim ws As Worksheet, wksJahr As Worksheet, wksWoche As Worksheet
Dim strPath As String, strSheetName As String
Dim dan As Long, mesec As Long, leto As Long
Dim lngIndex As Long
dan = Range("i10").Value
mesec = Range("i14").Value
leto = Range("i18").Value

'copy values when the first of month comes

If leto = 2007 And dan = 1 And mesec = 12 Then

Set wbRuckstand1 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\Ruckstand1.xlsx")

Set wksJahr = wbRuckstand1.Sheets("Jahr")
Set wksWoche = wbRuckstand1.Sheets("Woche 1")
wksJahr.Range("c62:r62").Value = wksWoche.Range("g20:v20").Value
End If

If leto = 2008 And dan = 1 And mesec Then

Set wbRuckstand1 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\Ruckstand1.xlsx")
Set wksJahr = wbRuckstand1.Sheets("Jahr")
Set wksWoche = wbRuckstand1.Sheets("Woche 1")

With wksWoche

Select Case mesec

Case 1
wksJahr.Range("c67:r67").Value = .Range("g20:v20").Value

Case 2
wksJahr.Range("c12:r12").Value = .Range("g20:v20").Value

Case 3
wksJahr.Range("c17:r17").Value = .Range("g20:v20").Value
Case 4
wksJahr.Range("c22:r22").Value = .Range("g20:v20").Value
Case 5
wksJahr.Range("c27:r27").Value = .Range("g20:v20").Value
Case 6
wksJahr.Range("c32:r32").Value = .Range("g20:v20").Value
Case 7
wksJahr.Range("c37:r37").Value = .Range("g20:v20").Value
Case 8
wksJahr.Range("c42:r42").Value = .Range("g20:v20").Value
Case 9
wksJahr.Range("c47:r47").Value = .Range("g20:v20").Value
Case 10
wksJahr.Range("c52:r52").Value = .Range("g20:v20").Value

End Select

End With

End If



'DELETE VALUES IN SHEETS WHEN THE WEEK NUMBER IS 1ST

If dan = 1 Then
For lngIndex = 1 To 5
CleanWorkbook "C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag" & lngIndex & ".xlsm"
Next lngIndex
End If
' OPEN SPECIAL WORKBOOK ON SPECIAL MWEEK
Set wb2 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\ProduktNumern.xlsx")


a = Range("i12").Value
b = Range("i16").Value

strPath = "C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag" & a & ".xlsm"

Set wb = Workbooks.Open(strPath)

With wb

Select Case b

Case 1
strSheetName = "PONEDELJEK-Montag"
Case 2
strSheetName = "TOREK-Dienstag"
Case 3
strSheetName = "SREDA-Mitwoch"
Case 4
strSheetName = "CETRTEK-Donnerstag"
Case 5
strSheetName = "PETEK-Freitag"
Case 6
strSheetName = "SAMSTAG-Sobota"

End Select

Sheets(strSheetName).Select


End With


End Sub
Sub CleanWorkbook(strWorkbookName As String)
Dim oWb As Workbook
Dim lngIndex As Long
Set oWb = Workbooks.Open(strWorkbookName)
Windows(oWb.Name).Visible = False
For lngIndex = 1 To 6
With oWb.Worksheets(lngIndex)
.Unprotect Password:=mc_strPassword
.Range("i7:i14").ClearContents
.Protect Password:=mc_strPassword
End With
Next lngIndex
Windows(oWb.Name).Visible = True
oWb.Close savechanges:=True
End Sub
gnod
12-06-2007, 08:45 AM
or is it you missed the "\" on your path after RuckstandEintrag

should be

strPath = "C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag\" & a & ".xlsm"
rory
12-06-2007, 08:53 AM
Judging by the previous code, I assumed it's:
RuckstandEintrag1.xlsm
RuckstandEintrag2.xlsm
rather than:
1.xlsm
2.xlsm
in the RuckstandEintrag directory.