Solved: Hiding Sheet Via Userform

[sooty8]

Hi All

I have a sheet in the workbook named Template is there a way of hiding the sheet in the workbook and when needing the sheet it can be opened and closed via a checkbox box on the userform. On hiding normally via the tab this line Below Sheets("Template").Select gives an error message.

Many Thanks

Sooty8




'Get month from userform
month_form = Month(Tb1A.Value)

'Now check if month exists in workbook

FoundSheet = False
For Each ws1 In ActiveWorkbook.Worksheets
month_sheetstr = Mid(Trim(ws1.Name), 1, 3)

If LCase(month_sheetstr) = "jan" Then
month_sheetnum = 1
ElseIf LCase(month_sheetstr) = "feb" Then
month_sheetnum = 2
ElseIf LCase(month_sheetstr) = "mar" Then
month_sheetnum = 3
ElseIf LCase(month_sheetstr) = "apr" Then
month_sheetnum = 4
ElseIf LCase(month_sheetstr) = "may" Then
month_sheetnum = 5
ElseIf LCase(month_sheetstr) = "jun" Then
month_sheetnum = 6
ElseIf LCase(month_sheetstr) = "jul" Then
month_sheetnum = 7
ElseIf LCase(month_sheetstr) = "aug" Then
month_sheetnum = 8
ElseIf LCase(month_sheetstr) = "sep" Then
month_sheetnum = 9
ElseIf LCase(month_sheetstr) = "oct" Then
month_sheetnum = 10
ElseIf LCase(month_sheetstr) = "nov" Then
month_sheetnum = 11
ElseIf LCase(month_sheetstr) = "dec" Then
month_sheetnum = 12
Else
End If

' MsgBox (ws1.Name)

If month_form = month_sheetnum Then
'If ws1.Name = "March 2013" Then

FoundSheet = True
End If

Next
If FoundSheet = False Then
If LCase(month_form) = 1 Then
month_sheettab = "January"
ElseIf LCase(month_form) = 2 Then
month_sheettab = "February"
ElseIf LCase(month_form) = 3 Then
month_sheettab = "March"
ElseIf LCase(month_form) = 4 Then
month_sheettab = "April"
ElseIf LCase(month_form) = 5 Then
month_sheettab = "May"
ElseIf LCase(month_form) = 6 Then
month_sheettab = "June"
ElseIf LCase(month_form) = 7 Then
month_sheettab = "July"
ElseIf LCase(month_form) = 8 Then
month_sheettab = "August"
ElseIf LCase(month_form) = 9 Then
month_sheettab = "September"
ElseIf LCase(month_form) = 10 Then
month_sheettab = "October"
ElseIf LCase(month_form) = 11 Then
month_sheettab = "November"
ElseIf LCase(month_form) = 12 Then
month_sheettab = "December"
Else

End If

Sheets("Template").Select
Sheets("Template").Copy Before:=Sheets(Sheets.Count)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = month_sheettab & " 2013"
Sheets(month_sheettab & " 2013").Select
Set ws = ActiveSheet
End If
'End add 03/02/2013

[SamT]

Current code
Sheets("Template").Select
Sheets("Template").Copy Before:=Sheets(Sheets.Count)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = month_sheettab & " 2013"
Sheets(month_sheettab & " 2013").Select
Set ws = ActiveSheet
End If
'End add 03/02/2013

Try
Sheets("Template").Copy Before:=Sheets(Sheets.Count)
Sheets("Template (2)").Name = month_sheettab & " 2013"
Set ws = Sheets(month_sheettab & " 2013")
End If
'End add 03/02/2013

BTW, the rest of your code can be cleaned up, shortened, made more effcient.

[sooty8]

Hi SamT

Thanks for the help works superbly just had to remember to unhide the the Template sheet on the tab. Will have to inform the users that on the 1st of every month they must unhide the Template sheet - is there a way that would automatically insert the sheet when opening the workbook for the first time on the first of the month.

How would I go about cleaning up the code as you mentioned.

Many Thanks - your help has been much appreciated.

Regards

Sooty8

[SamT]

If Format(Date, "d") = "1" Then It is the first day of the month.



Sub NewSheetOnFirstDay()
'Checks to see if a sheet named for the current month and year exists.
'If Not and it is after the first, create the sheet.
'The Date checking is redundant, but I left it here for your study, you can omit that line

'If Sheet doesn't exist then don't exit sub
On Error Resume Next
If Not Sheets(Format(Date, "mmmm") & " " & Format(Date, "yyyy")) Is Nothing Then Exit Sub

'If we're still here, then the sheet does not exist.
'Double check the date
If Clng(Format(Date, "d") => 1 Then 'It's on or after the first
Sheets("Template").Copy Before:=Sheets(Sheets.Count)
'Name the New Sheet with the current month and year
Sheets("Template (2)").Name = Format(Date, "mmmm") & " " & Format(Date, "yyyy"))

To convert the number from Tb1A.Value to a month (long Form)
MonthName = Format(DateValue(Tb1A & "/1/1999"), "mmmm") '/1/19999 can be any day/year combo
'Value is the default output of TextBoxes
BTW, Sheet.Copy works fine with hidden sheets.

[sooty8]

Hi SamT

Thanks for the reply & info not having much luck inserting the new code successfuly lots of error messages attaching the current copy of the workbook perhaps you could have a look and guide me in the right direction.

Regards

Sooty 8

[SamT]

I moved all that Tb1A code to module1 and called it from the (new) Workbook Open sub in the ThisWorkbook module because you only need to call it once per opening. If you play with this book after midnight, you can close and open the book or put a New_Month_Button on the form.

I also set and cleared the autofilters as each sheet was (de)activated and the book was open/closed.

There is a ToggleTemplate sub in the Thisworkbook mod that you can manually run when you need to work with the template.

I set the active sheet, "ws" in the Form Initialize sub and added a Tb1A_Change sub to reset it if you put a different date in the box.

This is the sheet Template copying code in ThisWorkbook_Open
If Not CurrentMonthSheetExists Then MakeNewCurrentMonthSheet
And Here are the two Functions in module1 that do the work
Function CurrentMonthSheetExists() As Boolean
On Error GoTo SetFalse

CurrentMonthSheetExists = True
If Not Sheets(Format(Date, "mmmm") & " " & Format(Date, "yyyy")) Is Nothing _
Then Exit Function

SetFalse: CurrentMonthSheetExists = False
End Function

Function MakeNewCurrentMonthSheet()
Application.ScreenUpdating = False

Sheets("Template").Copy Before:=Sheets(Sheets.Count)
'Name the New Sheet with the current month and year
Sheets("Template (2)").Visible = xlSheetVisible
Sheets("Template (2)").Name = Format(Date, "mmmm") & " " & Format(Date, "yyyy")

Application.ScreenUpdating = True
End Function CurrentMonthSheetExists is written that way, because if you call a sheet that doesn't exist, an error occurs.

The logic is:

Set the Function's Return to True
Call the sheet
If successful, exit the Function
Else Set the Return to False

[sooty8]

Hi SamT

Top man exactly what I needed again many thanks for all your help

Regards

Sooty8