VBA Conflict

jayjaysb

Board Regular
Joined
Nov 16, 2011
Messages
193
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,046
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe it would help if you posted the code your are using.
 

jayjaysb

Board Regular
Joined
Nov 16, 2011
Messages
193
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,046
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
IS that the only code you have, as the posted code shouldn't affect the time component
AND works fine for me !
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula Then Exit Sub
Target = UCase(Target)
End Sub
 

jayjaysb

Board Regular
Joined
Nov 16, 2011
Messages
193

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,046
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
It's not a column width issue is it ??
Try widenng the column.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 16, 2011
Messages
193
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,335
Messages
5,624,090
Members
416,010
Latest member
NJT

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
Top