VBA Conflict

[jayjaysb]

Hi all, Looking for some advice. I added VBA to my spreadsheet to enable Auto Capital letters, which works fine. In turn I have a Time Column, which is set to HH:MM, but when I input the time it defaults to scientific 2 decimals. I removed the VBA and the time works as normal. Added VBA again and the same Time error. Any ideas? Thanks JJSB

 

[Michael M]

Maybe it would help if you posted the code your are using.

 

[jayjaysb]

Hi Michael, now added, Thanks

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.HasFormula Then Exit Sub

Application.EnableEvents = False

Target = UCase(Target.Cells(1))

Application.EnableEvents = True

End Sub

 

[Michael M]

IS that the only code you have, as the posted code shouldn't affect the time component
AND works fine for me !

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula Then Exit Sub
Target = UCase(Target)
End Sub

 

[jayjaysb]

Hi Michael, Yes that's the only used within the spreadsheet. I tried yours and similarly it has issues with certain times input like 06:08 is fine, but the likes of 01:01 or 01:08, or 02:04 come up with the error ######, by going into formatting customize hh:mm it changes correctly, but same happens cell after cell. Its bizarre as it doesn't do it with all times. Thanks for your help

 

[Michael M]

It's not a column width issue is it ??
Try widenng the column.

 

[Peter_SSs]

by going into formatting customize hh:mm

When you go in there to do that, what is the existing cell formatting before you change to hh:mm? When you go in there are you finding "Scientific"?

The behaviour sounds like the cell is not already formatted as hh:mm and that the column is wide enough to display a time but not wide enough to display a scientific notation value which you would probably get for attempted time values before 02:24.

 

[jayjaysb]

When you go in there to do that, what is the existing cell formatting before you change to hh:mm? When you go in there are you finding "Scientific"?

The behaviour sounds like the cell is not already formatted as hh:mm and that the column is wide enough to display a time but not wide enough to display a scientific notation value which you would probably get for attempted time values before 02:24.

Hi Peter,

Thanks for your reply. Column A (time column) the whole column highlighted set as custom HH:MM as I don't require the seconds, but only certain times was defaulting to scientific, when this happened it showed as xxxxxx like the column wasn't wide enough, when I went back into format and changed back to HHMM it worked. However I have gone in and selected HH:MM:SS and it appears to work regardless of the time i.e 01:01:00. Strange but it works. Thanks again JJSB

 

[Peter_SSs]

but only certain times was defaulting to scientific

Yes, as I mentioned, I would expect those times earlier than 02:24 would be the culprits.

it appears to work regardless of the time

Great. Glad it seems to be resolved.