Results 1 to 6 of 6

Macro to convert date to year, month, or day

This is a discussion on Macro to convert date to year, month, or day within the Excel Questions forums, part of the Question Forums category; I'm working on this macro that I've setup in a userform in order to convert dates into either Year, Month, ...

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    12

    Default Macro to convert date to year, month, or day

    I'm working on this macro that I've setup in a userform in order to convert dates into either Year, Month, or Day form. I want users to be able to highlight dates in their workbook, activate the form and then select the option button of their choice. Not sure why this isn't working, any help?

    HTML Code:
    Private Sub Convert_Click()
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    Application.StatusBar = "Calculating, please wait"
    
    For Each xCell In Selection
    
    If Month = True And xCell > 0 Then
    xCell.Value = Month(xCell.Value)
    xCell.NumberFormat = "General"
    
    ElseIf Year = True And xCell > 0 Then
    xCell.Value = Year(xCell.Value)
    xCell.NumberFormat = "General"
    
    ElseIf Day = True And xCell > 0 Then
    xCell.Value = Day(xCell.Value)
    xCell.NumberFormat = "General"
    
    End If
    Next xCell
    
    
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Unload Me
    
    
    End Sub

  2. #2
    Board Regular gaj104's Avatar
    Join Date
    Nov 2002
    Posts
    846

    Default Re: Macro to convert date to year, month, or day

    Possibly this:

    Code:
    If Month = True
    Is Month your variable or your object (possibly a checkbox)? You shouldn't use reserved words for variables. Same applies to Year and Day.
    graemejones.co.uk My website!

  3. #3
    New Member
    Join Date
    Mar 2010
    Posts
    12

    Default Re: Macro to convert date to year, month, or day

    Month = True relates to the name of the option button. When the option button 'Month' is selected it should be True

    Right now the result i get it my dates formatted as "general" the part I cannot seem to get to work is

    HTML Code:
    xCell.Value = Month(xCell.Value)
    However, if ditch the form and just use the following it works just fine, but I want to keep the form so I can have all 3 options available.

    HTML Code:
    For Each xCell In Selection
    xCell.Value = Month(xCell.Value)
    xCell.NumberFormat = "General"

  4. #4
    New Member
    Join Date
    Mar 2010
    Posts
    12

    Default Re: Macro to convert date to year, month, or day

    I responded too soon...you are right, i needed to modify my naming usage.

    thanks...got it to work.

  5. #5
    Board Regular gaj104's Avatar
    Join Date
    Nov 2002
    Posts
    846

    Default Re: Macro to convert date to year, month, or day

    To rule out conflicting variables\objects, I'd suggest renaming the option buttons. Something like myYear, myMonth, myDay.

    Also suggest completing the property of the cell i.e.

    If Month = True And xCell > 0

    becomes If myMonth.Value = True and xCell.Value > 0
    graemejones.co.uk My website!

  6. #6
    Board Regular gaj104's Avatar
    Join Date
    Nov 2002
    Posts
    846

    Default Re: Macro to convert date to year, month, or day

    No worries, glad to help.
    graemejones.co.uk My website!

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
  •  


DMCA.com