Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Thread: Looking for advice / recommendations

  1. #11
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    Hi,
    This below now works but can you help put the yyyy on the same line code as mmmm please.

    Code:
    Private Sub Worksheet_Activate()
    
    With Sheets("GRASS INCOME")
          .Range("C3") = UCase(Format(Now, "mmmm"))
        With .Range("C3")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            With .Font
                .Name = "Calibri"
                .FontStyle = "Bold"
                .Size = 28
            End With
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent1
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
            End With
            Range("A5").Select
            End With
            End With
    End Sub
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #12
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,395
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Looking for advice / recommendations

    Are you using a non-english edition of excel ?

    As for worrying about loosing the formatting, I don't think you should because you are only clearing the contents of the cells ie : ClearContents.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  3. #13
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,395
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Looking for advice / recommendations

    Quote Originally Posted by ipbr21054 View Post
    Hi,
    This below now works but can you help put the yyyy on the same line code as mmmm please.
    Code:
    .Range("C3") = UCase(Format(Now, "mmmm yyyy"))
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  4. #14
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    Nearly but not quite.

    If i use this
    Code:
    .Range("C3") = UCase(Format(Now, "mmmm"))
    I see JULY

    If i use the code you advise i see
    Jul-19

    Im trying to get it to be JULY 2019
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  5. #15
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    Hi,
    I have a workaround which takes car of it,see below,

    Code:
    Private Sub Worksheet_Activate()
    
    With Sheets("GRASS INCOME")
            .Range("A3") = UCase(Format(Now, "mmmm"))
            .Range("D3") = Year(Now)
        With .Range("C3")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            With .Font
                .Name = "Calibri"
                .FontStyle = "Bold"
                .Size = 28
            End With
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            With .Interior
            .Pattern = xlSolid
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
            .PatternTintAndShade = 0
            End With
            Range("A5").Select
            End With
            End With
    End Sub
    
    
    BUT
    Can you advise the edit so the date & year are part of the saved pdf, see below.
    
    Code:
    Private Sub GrassSummarySheet_Click()
        Dim strFileName As String
        
        strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\\" & Range("C3").Value & ".pdf"
        If Dir(strFileName) <> vbNullString Then
            MsgBox "SUMMARY SHEET " & Range("C3").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
            Exit Sub
        End If
        
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
            MsgBox "SUMMARY SHEET " & Range("C3").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
            Range("A5:B41").ClearContents
            Range("A5").Select
            ActiveWorkbook.Save
        End With
    End Sub
    Currently C3 BUT need to edit it correctly so its A3 D3 then i think it will work
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  6. #16
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,395
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Looking for advice / recommendations

    Quote Originally Posted by ipbr21054 View Post
    Nearly but not quite.

    If i use this
    Code:
    .Range("C3") = UCase(Format(Now, "mmmm"))
    I see JULY

    If i use the code you advise i see
    Jul-19

    Im trying to get it to be JULY 2019
    That is strange... It works for me as expected.

    Try this and see how it goes:
    Code:
    .Range("C3") = UCase(Format(Now, "mmmm")) & " " & UCase(Format(Now, "yyyy"))
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  7. #17
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    Hi,
    Its the same.
    I will leave it and follow on with my work around if you could read the follow up post

    Thanks
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  8. #18
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,395
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Looking for advice / recommendations

    Quote Originally Posted by ipbr21054 View Post
    Hi,
    Its the same.
    I will leave it and follow on with my work around if you could read the follow up post

    Thanks
    Are you applying some custom number format to cell C3 ?

    You post15 looks fine to me it just places the year in cell D3.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  9. #19
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    Hi,
    No im not.

    I have had to move on now as it was holding me up.

    Post 15 works fine apart from the button code that it used to save the excel range as a pdf file.

    Originally the cell to take the name for the pdf file was C3 but as i was haiving an issue i decided to do this.
    I used cell A3 for the date of which cell A3 now shows JULY which is perfect

    I used cell D3 for the year of which cell D3 now shows 2019 which is perfect

    So in order to save the pdf file the new code needs to take the month from cell A3 & the year from cell D3 then save.
    So the pdf file will be JULY 2019

    This is the olf save code from when it was in cell Cú
    I now need to edit it,of which i cant so it takes into account the 2 cells for month & year being A3 & D3

    Code:
    Private Sub GrassSummarySheet_Click()    Dim strFileName As String
        
        strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\" & Range("A3,D3").Value & ".pdf"
        If Dir(strFileName) <> vbNullString Then
            MsgBox "GRASS SHEET " & Range("A3,D3").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "SUMMARY GRASS SHEET MESSAGE"
            Exit Sub
        End If
        
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
            MsgBox "GRASS SHEET " & Range("A3,D3").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "SUMMARY GRASS SHEET MESSAGE"
            Range("A5:B41").ClearContents
            Range("A5").Select
            ActiveWorkbook.Save
        End With
    End Sub
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  10. #20
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,395
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Looking for advice / recommendations

    Try this (Changes in red)
    Code:
    Private Sub GrassSummarySheet_Click()
        Dim strFileName As String
        
        strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\" & Range("A3") & " " & Range("D3") & ".pdf"
        If Dir(strFileName) <> vbNullString Then
            MsgBox "GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "SUMMARY GRASS SHEET MESSAGE"
            Exit Sub
        End If
        
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
            MsgBox "GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "SUMMARY GRASS SHEET MESSAGE"
            Range("A5:B41").ClearContents
            Range("A5").Select
            ActiveWorkbook.Save
        End With
    End Sub
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •