asked on

Macro needs slight change

EE Pros,

I have a Macro application that I received great help from Fanpages and Martin Liss. When button was selected, it should change the date/time format in two cells. I added one date/time format to the macro and now it won't work. Can you take a look?

Much thanks in advance,

B.
Time-Interval-Frequency-calculationv51.x

Kanti Prasad

Hi

Can you please attach the xls file or the code you can and what you are trying ?

Bright01

ASKER

Sorry. Here it is.

B.
Time-Interval-Frequency-calculationv51.x

Bright01

ASKER

Option Explicit
Sub ChangeFormat()
With Range("C4")
Select Case .NumberFormat
Case "m/d/yy h:mm;@" ' DATE/TIME
.NumberFormat = "yyyy"
ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Yr."

' Case "mm/ddd/yyyy" 'Date MO DAY YEAR"
Case "yyyy" 'Date MO DAY YEAR"
.NumberFormat = "mm/dd/yyyy"
' .ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Day Yr."
ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Day Yr."

' Case "yyyy" ' DATE YR
Case "mm/dd/yyyy" ' DATE YR
.NumberFormat = "mm/yyyy"
ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Yr."
Case "mm/yyyy" ' DATE MO YR
.NumberFormat = "ddd"
ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Day"
Case "ddd" ' DATE DAY
.NumberFormat = "hh:mm"
ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Time: Hr./Min."
Case "hh:mm"
.NumberFormat = "hh:mm:ss"
ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Time: Hr./Min./Sec."
Case "hh:mm:ss"
.NumberFormat = "m/d/yy h:mm;@"
ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date/Time"
End Select
Range("C9").NumberFormat = .NumberFormat
End With
End Sub

David Johnson, CD

it helps if you place code into a code block
also Experts Exchange currently has a 40 character limit on filenames so anything after 40 characters gets truncated .. your attached file was an .xlsm

Option Explicit
Sub ChangeFormat()
    With Range("C4")
        Select Case .NumberFormat
            Case "m/d/yy h:mm;@" ' DATE/TIME
                .NumberFormat = "yyyy"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Yr."
               
'            Case "mm/ddd/yyyy" 'Date MO DAY YEAR"
            Case "yyyy" 'Date MO DAY YEAR"
                .NumberFormat = "mm/dd/yyyy"
'                .ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Day Yr."
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Day Yr."
               
'            Case "yyyy" ' DATE YR
            Case "mm/dd/yyyy" ' DATE YR
                .NumberFormat = "mm/yyyy"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Yr."
            Case "mm/yyyy" ' DATE MO YR
                .NumberFormat = "ddd"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Day"
            Case "ddd" ' DATE DAY
                .NumberFormat = "hh:mm"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Time: Hr./Min."
            Case "hh:mm"
                .NumberFormat = "hh:mm:ss"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Time: Hr./Min./Sec."
            Case "hh:mm:ss"
                .NumberFormat = "m/d/yy h:mm;@"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date/Time"
        End Select
        Range("C9").NumberFormat = .NumberFormat
    End With
End Sub

Open in new window

Bright01

ASKER

Dave,

Thanks for the tips. Do I use "Code" and then place the code in between the parameters?

B.

David Johnson, CD

nopoints: precisely.. I was in another area and there was a mention of the filename being truncated that is why I popped in here
Do not give me any points please

Martin Liss

What isn't working?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

Martin, PERFECT! Now I get it. You have to declare the format you may be using before using it.

Thanks again for the work and the lesson.

B.

Bright01

ASKER

Martin,

I've got to issue another EE Post. I found another bug in the original code that calculated the Time/Date. Hope you will pick it up if you have the time.

B.