Macro to convert date to year, month, or day

jshutlz

New Member
Joined
Mar 10, 2010
Messages
12
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:
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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:
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:
For Each xCell In Selection
xCell.Value = Month(xCell.Value)
xCell.NumberFormat = "General"
 
Upvote 0
I responded too soon...you are right, i needed to modify my naming usage.

thanks...got it to work.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top