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
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