Solved: Excel 2003 converted to Excel 2007 help.

[dleckie]

Hi all,

I was helped back in Sept with some code that entered the colon ( : ) in automatically when a number was entered for time. Example 1234 = 12:34:00.

Now my company (with their infinite wisdom) had decide to upgrade MS Office from 2003 to 2007. Unfortunatelly the code will not work in Office 2007.

Can anyone help me with this issure. The code is supplied below.

Thanks for you help.
Dave



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
On Error GoTo EndMacro
'Enter the range in the spreadsheet where you will be entering time
If Application.Intersect(Target, Range("A1:AR117")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
.NumberFormat = "h:mm:ss" 'Comment this line out if you want to have AM/PM show.
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

[Bob Phillips]

Works for me Dave. What are you getting?

[dleckie]

Hi there, thanks for looking at this.

I actually get the same responce as if there was no code at all and I don't get the message to Enable Macro like I did with 2003.

When I go to the worksheet tab; I am able to View Code. When I enter a time of 1234 I get 5/18/1903 12:00:00 AM.

This is the 1st time using 2007 so I am not sure if there are security settings I must do. Been going that direction.

Dave

[Bob Phillips]

OK, I thought after I posted that that might be the problem.

For some odd reason, they have changed the macro warning in Excel 2007. You no longer get an 'Enable macros' dialog, instead in the formula bar area there is a button that says Options. Click that, and you get a dialog with optionbuttons, one to Enable content (not the default).

There is one supplement to the above, if the VBIDE is open when you open a workbook, you get the old enable macros dialog.

Is this wierd or what?

[georgiboy]

Excel 2007, you gotta love it. The new menu bars at the top confused the hell out of me, i was lost for weeks!

I have permanently enabled macros cos im a risk taking kind of guy, live life on the edge. It should always ask you about enabling macros at the top of the screen just under the confusing menu ba,r but only when you first open the document.

[dleckie]

"For some odd reason, they have changed the macro warning in Excel 2007. "

Leave it to Microsoft to not only change the complete look of things but also change the expected behavior. A least you could have been notified with a warning screen stating "Macros are no longer enabled, would you like to revise this?" :banghead: :bug:

"instead in the formula bar area there is a button that says Options."

I am not sure where this is. I looked under the "Formulas" tab and did not see a button stating Options in the Function Library, Defined Named, Formula Auditing, or Calculation ribbon. Am I at the wrong place?

Thanks
</IMG></IMG>

[dleckie]

I did find this.....
1) Go to the Windows Office logo at the top left next to save.
2) Clickon that and go to Excel Options at the bottom left of that window
3) Next select Trust Center > Trust Center Settings > Macro Settings

Not sure which to select here. I choose Enable all Macros, then Ok, Ok.
Tried to enter a time and did the same thing.

[Bob Phillips]

I am not sure where this is. I looked under the "Formulas" tab and did not see a button stating Options in the Function Library, Defined Named, Formula Auditing, or Calculation ribbon. Am I at the wrong place?

See here

[Bob Phillips]

Perhaps you need to reset security settings

Click the Orange Office button

Click the Excel Options button

Select Popular from the left-hand menu pane

In the right-hand pane, click the 'Show developer tab in the Ribbon' checkbox

Macro Security is on the Code group of this tab.

[dleckie]

Perhaps you need to reset security settings

Click the Orange Office button

Click the Excel Options button

Select Popular from the left-hand menu pane

In the right-hand pane, click the 'Show developer tab in the Ribbon' checkbox

Macro Security is on the Code group of this tab.



I did this and the 'Show developer tab in the Ribbon' was not checked. Checked it and saved it. Went back to the spreadsheet and still did nothing. Exited the workbook and went back in. Did not see the Security Warning that you circled.

Is this something I need to install as an 'Add-On' in Office?

[dleckie]

Got it.....

I had to go back and check 'Disable all macros with notification' for the security to appear back here....


1) Go to the Windows Office logo at the top left next to save.
2) Clickon that and go to Excel Options at the bottom left of that window
3) Next select Trust Center > Trust Center Settings > Macro Settings

The time works great now...

Thanks

[Bob Phillips]

Got it.....

I had to go back and check 'Disable all macros with notification' for the security to appear back here....


Exactly, as I said ... Macro Security is on the Code group of this tab.