sooty8
06-06-2013, 05:52 AM
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
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