Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 1st, 2010, 01:10 PM
Registered User
 
Join Date: Aug 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to hide rows for specific months

Below is my code for my timesheet. I want to be able to hide rows 111 to 116 for months that have less than 31 days (months 4,6,9,11) when i click the command button. Thank you for any help!


Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Template").Visible = True

Worksheets("Template").Copy Before:=Worksheets("Template")

temp = ActiveSheet.Index - 1

oldmonth = Month(Worksheets(temp).Range("D2"))
oldDay = Day(Worksheets(temp).Range("D2"))


If oldDay = 1 Then
'new starting date
newday = 16
lastday = endofmonth(oldmonth)
newmonth = oldmonth

'new ending date


Else
newday = 1
lastday = 15

If oldmonth = 12 Then
newmonth = 1
Else
newmonth = oldmonth + 1
End If
End If

'Rename current Sheet
newsheetname = newmonth & "-" & newday & " to " & newmonth & "-" & lastday

ActiveSheet.Name = newsheetname

ActiveSheet.Range("D2") = newmonth & "/" & newday
' ActiveSheet.Index

'If (Year(Worksheets(temp).Range("s2")) < Year(Worksheets(temp).Range("s2") + 13)) Then
' Worksheets.Add
' ActiveSheet.Name = Year(Worksheets(temp).Range("q2") + 13)
' If Application.Version > 10 Then
' ActiveSheet.Tab.ColorIndex = 3
' End If
' Worksheets(newsheetname).Activate
'End If


Sheets("Template").Visible = False

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



End Sub


Module:
Public Function endofmonth(monthA)

Select Case monthA
Case 1, 3, 5, 7, 8, 10, 12
monthend = 31
Case 4, 6, 9, 11
monthend = 30
Case Else
monthend = 28
End Select

If monthA = 2 Then
If IsLeapYear(Year(Now())) = True Then
monthend = 29
Else
monthend = 28
End If
End If

endofmonth = monthend

End Function

Public Function nextmonth(Month)



End Function

Public Function IsLeapYear(Y As Integer)
IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function
 
Old August 3rd, 2010, 02:46 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Do you have any dates in 111 to 116; for eg, D111 etc?

Then you can get the Date part and check

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old August 18th, 2010, 04:34 PM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Interesting way to accommodate for date data types.

This is how I handled it:

Create a module and place the actual functionality below there. no point in having replicated data due to template copying.

Code:
Public Sub CreateNextTimesheet()

'Creates next Timesheet from Template
  Dim wsPrevTimesheet As Worksheet, wsNewTimesheet As Worksheet, wsTemplate As Worksheet
  Dim dDateFrom As Date, dDateTo As Date, sBuildDate As String, iMonth As Long, iYear As Long
  Dim iLastDay As Long

'Create pointer to worksheets and add new one to list
  Set wsTemplate = ActiveWorkbook.Worksheets("Template")
  Set wsPrevTimesheet = ActiveWorkbook.Worksheets(wsTemplate.Index - 1)
  wsTemplate.Visible = xlSheetVisible
  wsTemplate.Copy Before:=wsTemplate
  Set wsNewTimesheet = ActiveWorkbook.ActiveSheet

'Copy over the template with button.  This way only 1 line of code is replicated.
  wsTemplate.Visible = xlSheetHidden
  dDateFrom = CDate(wsPrevTimesheet.Range("D2").Value) 'Use full date with year
  iMonth = Month(dDateFrom)
  iYear = Year(dDateFrom)

'Create Timesheet 1st to 15th
  If Day(dDateFrom) = 1 Then
    dDateFrom = dDateFrom + 15
    iMonth = iMonth + 1
    If iMonth > 12 Then
      iMonth = 1
      iYear = iYear + 1
    End If
    dDateTo = CDate(iMonth & "/01/" & iYear) - 1 'Subtract back 1 day, make Excel Date type do the work

'Create Timesheet 16th to end of month
  Else
    iMonth = iMonth + 1
    If iMonth > 12 Then
      iMonth = 1
      iYear = iYear + 1
    End If
    dDateFrom = CDate(iMonth & "/01/" & iYear)
    dDateTo = CDate(iMonth & "/15/" & iYear)
  End If

'Get last day of month making Excel Date type do the work again.
'Formula starts with 25th of month, adds 9 days, then subtracts
'day of month which ends at last day of previous month.
  iLastDay = Day(CDate(Month(dDateTo) & "/25/" & Year(dDateTo)) + 9 _
    - Day(CDate(Month(dDateTo) & "/25/" & Year(dDateTo)) + 9))

'Set new timesheet values and make it the active sheet
  With wsNewTimesheet
    .Activate
    .Name = Format(dDateFrom, "MM-DD-YYYY") & " to " & Format(dDateTo, "MM-DD-YYYY")
    .Range("D2").Value = dDateFrom
    If iLastDay < 31 Then .Range("111:116").EntireRow.Hidden = True
  End With

End Sub
Then on the template sheet your button code would be:
Code:
Private Sub NextTimeSheet_Button_Click()

  Call CreateNextTimesheet

End Sub
This may not be exactly what you want, but hopefully it points you in the right direction.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide Empty Rows jzsolt Excel VBA 3 August 17th, 2010 06:07 PM
Highlight specific rows in a gridview rao965 ASP.NET 2.0 Professional 6 June 14th, 2007 03:20 AM
Deleting rows not equal to specific values Oprete Excel VBA 1 March 25th, 2007 11:14 PM
Hide/show rows in a table smi13y XSLT 6 December 28th, 2006 08:48 PM
Insert row between specific rows Paula222 Access VBA 2 February 10th, 2006 03:56 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.