PDA

View Full Version : Run Time errror 1004



cbs81
04-16-2007, 10:51 PM
Hi everyone.... The following code asks the user to enter the :

operator name,
month,
year,

once the user enters these parameters they press a button and all the worksheets that are initially hidden become unhidden....

With the exception of April, june, sept, november and february all sheets are displayed... For these months above, certain sheets are hidden, which are days 31 (as these months do not have 31 days)

where the :banghead: is .. is where the code i think is giving me an error:

it says:

run time error '1004'
unable to set the hidden property of range class..


this has got me buggered.... any reason why this happens... i commented the :banghead: section out.. and the code runs propertly.... but this has got me puzzeled..... any help or have i done something wrong?? any help would be highly appreciated





Private Sub CommandButton1_Click()
Dim wbpath$, oprtr$, mnth$, yr$
Dim Sht As Worksheet
' name new workbook according to file path to which original was saved and add Operator, Month, and Year to name
Application.ScreenUpdating = False
If Range("f24") = "-" Then
MsgBox "Operator name must be entered"
Exit Sub
End If
If Range("f26") = "-" Then
MsgBox "Month must be entered"
Exit Sub
End If
If Range("f27") = "-" Then
MsgBox "Year must be entered"
Exit Sub
End If
With Sheets("cover sheet")
oprtr$ = .Range("f24")
mnth$ = .Range("f26")
yr$ = .Range("f27")
End With
Sheets("Cover Sheet").Shapes("CommandButton1").Delete
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Cover Sheet" Then Sht.Visible = True
Next
Application.Run "rename_sheets"
Application.Run "hidefirstlast"
Dim fn As String
fn = Dir(ActiveWorkbook.Path & "\Cash Reconciliation " & oprts$ & " " & mnth$ & " " & yr$ & ".xls")
If Len(fn) Then
MsgBox "File already exists"
Exit Sub
End If
ThisWorkbook.SaveAs Application.ActiveWorkbook.Path & "\Cash Reconciliation " & oprtr$ & " " & mnth$ & " " & yr$ & ".xls"
Application.ScreenUpdating = True

:banghead:
If Range("f26") = "April" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "June" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "September" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "November" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "February" Then
Sheet36.Visible = False
Sheet35.Visible = False
Sheet1.Range("39:40").EntireRow.Hidden = True
End If

End Sub

cbs81
04-16-2007, 11:08 PM
Ohhhh.. another thing I forgot to mention

where it says

Sheet1.Range("40:40").EntireRow.Hidden = True

that particular sheet is protected


I tried writing the code to automatically unprotect it then hide it.... is it:


If Range("f26") = "April" Then
Sheet36.Visible = False


With Worksheets("Cover Sheet")
.Unprotect Password:="cbs"
Sheet1.Range("40:40").EntireRow.Hidden = True
.Protect Password:="cbs"


I tried the above for april... but it still doesnt work

Aussiebear
04-17-2007, 01:04 AM
try the following code

With Worksheets ("CoverSheet")
.UnProtect "CBS"
Sheet1.Range("40:40").EntireRow.Hidden = True
.Protect "cbs"

Bob Phillips
04-17-2007, 02:09 AM
Sheet1.Range("40:40").EntireRow.Hidden = True

Only needs

Sheet1.Rows(40).Hidden = True