worksheet change question 04-02-10

I want to be able to enter a time, using "." instead of ":", so that
when I enter the number, then press "Enter", the number changes from,
say, 13.27.33 to 13:27:33. But I am having to reselect the cell to get
it to change. What is wrong with my code?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Selection.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
        ReplaceFormat:=False
    Selection.NumberFormat = "h:mm:ss"
End Sub

Thanks,

Tonso
0
Tonso
4/2/2010 2:55:51 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
658 Views

Similar Articles

[PageSpeed] 12

Wrong event.  Try this one.

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Target.NumberFormat = "h:mm:ss"
End Sub


Gord Dibben  MS Excel MVP

On Fri, 2 Apr 2010 07:55:51 -0700 (PDT), Tonso <wthomasss@hotmail.com>
wrote:

>I want to be able to enter a time, using "." instead of ":", so that
>when I enter the number, then press "Enter", the number changes from,
>say, 13.27.33 to 13:27:33. But I am having to reselect the cell to get
>it to change. What is wrong with my code?
>
>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>    Selection.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
>        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
>_
>        ReplaceFormat:=False
>    Selection.NumberFormat = "h:mm:ss"
>End Sub
>
>Thanks,
>
>Tonso

0
Gord
4/2/2010 3:14:39 PM
On Apr 2, 11:14=A0am, Gord Dibben <gorddibbATshawDOTca> wrote:
> Wrong event. =A0Try this one.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 =A0 Target.Replace What:=3D".", Replacement:=3D":", LookAt:=3DxlPart,=
 _
> =A0 =A0 =A0 =A0 SearchOrder:=3DxlByRows, MatchCase:=3DFalse, SearchFormat=
:=3DFalse, _
> =A0 =A0 =A0 =A0 ReplaceFormat:=3DFalse
> =A0 =A0 Target.NumberFormat =3D "h:mm:ss"
> End Sub
>
> Gord Dibben =A0MS Excel MVP
>
> On Fri, 2 Apr 2010 07:55:51 -0700 (PDT), Tonso <wthoma...@hotmail.com>
> wrote:
>
>
>
> >I want to be able to enter a time, using "." instead of ":", so that
> >when I enter the number, then press "Enter", the number changes from,
> >say, 13.27.33 to 13:27:33. But I am having to reselect the cell to get
> >it to change. What is wrong with my code?
>
> >Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > =A0 =A0Selection.Replace What:=3D".", Replacement:=3D":", LookAt:=3DxlP=
art, _
> > =A0 =A0 =A0 =A0SearchOrder:=3DxlByRows, MatchCase:=3DFalse, SearchForma=
t:=3DFalse,
> >_
> > =A0 =A0 =A0 =A0ReplaceFormat:=3DFalse
> > =A0 =A0Selection.NumberFormat =3D "h:mm:ss"
> >End Sub
>
> >Thanks,
>
> >Tonso- Hide quoted text -
>
> - Show quoted text -

As always Gord, thanks so much. it works perfectlty!!!

Tonso
0
Tonso
4/2/2010 4:46:16 PM
Reply:

Similar Artilces:

windows update 04-15-10
I am running windows vista on a MAC computer with VMWare Fusion. I cannot get the service pack 1 or internet explorer 8 update to run. i get error code 490 and 800704090 Too late now! Support for Vista Gold (no Service Packs) ended on Tuesday, 13 April 2010! Computers running Vista Gold "will no longer receive software updates from Windows Update" until SP1 has been manually installed (which will require several Prerequisite Updates to be installed first). See http://windowsteamblog.com/blogs/windowsvista/archive/2010/04/13/end-of-support-for-windows-vista-with-no...

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

Problems saving a worksheet with Links
Does anyone know how I can resolve this issue ... I have a directory which contains 129 worksheets which have links to external data (in a Master Spreadsheet) -- I need to copy these files into a New Directory, but kee the Master Spreadsheet (which they are linked to) in the original location. If I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets moved to the New Directory (where the file does not exist), but if I open the worksheet (in the original directory/location) and Save As to the New Directory, the worksheet saved in the New Directory maintains its link t...

2 Questions
Hi I run Win 2K with Excel 2K Question 1 I have some VB code in the "Sheet 1" module of my excel file. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column <> 12 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub I would like this code to run automatically whenever the file is opened. (I am assuming that this would remove the enable/disable dialog box option when opening the file) Can someone tell ...

Exchange 5.5 to Exchange 2003 Migration Question: No ADC??
Hello all: I have an environment where I need do do an Exch 5.5 to 2003 Migration. The domain has already been migrated from NT4 to 2003. The only thing is, is that there is no Active Directory Connector setup anywhere in the environment. That leads me to my first question I guess. How is this possible that Exch 5.5 is even working with AD? In terms of upgrading, what would recommendations be? It is a single Site. I was thinking about bringing up a new Exch 2003 server, and using "Move Mailbox" to the new exch server. Replicate all the necessary public folders, decomission the old ...

Simple worksheet becomes large and slow
I use 2 simple worksheets daily. They contain little data and should be less than 50k. I change them by copying data from various other spreadsheets into them, then saving or saving as with a new name. Some of these spreadsheets for some reason become very large (1.5mb or more) and moving from cell to cell becomes very slow. Copying from those spreadsheets to new sheets can take up to 30 seconds for a single cell to copy. There is nothing complex in any of them, just text, some multiplication and sum. The sheets eventually become so slow that the only way I can work with them is b...

Publisher 2000 changes default font
Having loaded Publisher 2000 onto a Win98 m/c I have discovered fonts used by Groupwise have changed. This implies that P2000 has changed the default font somewhere - any ideas please? How do I change the fonts in GroupWise http://support.novell.com/cgi-bin/search/searchtid.cgi?/10061270.htm -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "PS" <anonymous@discussions.microsoft.com> wrote in message news:1be4401c45220$f2b1b4a0$a601280a@phx.gbl... > Having loaded Publisher 2000 onto a Win98 m/c I have > discover...

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

Pivot Table Question #15
Hey all, Love pivot tables but i have a question, the table is set up as so Month Pallet# BoxesPur BoxesSold Price Frt Gross Net Jan 1 5 1.0 ..3 Formula Formula 2 5 2.0 .5 3 10 3 .15 Jan Total 10 10 6.0 ..95 So, the problem is in the Jan Total row, is there a way...

why do changes only save locally on networked excel file?
why do changes only save locally on networked excel file? ...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

Mock question?
Hi all Not sure if this is the right group, I am happyo to repost to correct group if I know what that is! but in the mean time - here goes I am using NMock2 and I have question. I have the following interface: public interface ITime { int Hour { get; } } and the following weather service: public class WeatherService:ITime { public string GetTodaysWeather() { if (Hour >= 0 && Hour <= 6) { return "Sunny"; } else if (Hour >= 7 &&...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

Report Error 11-24-04
When attempting to run CRM Reports for the first time we are receiving the error "Information is needed before this report can be processed" I have found documentation that states that this can happen if your server has an underscore. Our server does not it is named ourcompany.server.eeecrm Can the periods have the same effect as the underscore. Or does anyone have another cause? Thank you Hi There - Hope you don't mind but I wish to piggy-back on your post. I get the same error on our custom reports that use OLE DB to connect to another SQL server other than the MS CRM ...

Column styles doesn't appear on the worksheet
Hello, one of my customers has changed the windows xp designs to its own. From this moment excel doesn't show the font color and the background color of a cell in the worksheet. Only in the print preview you can see the color settings of the cells. I'm not shure if this problem belongs to the changing of the windows xp designs, but from this moment it did occur. This occurs on new excel-documents and on existing ones. With another user account on the same machine the problem doesn't occur. I have reinstalled Office 2003 and even deleted user registry entries for Office...

Script Errors 08-12-04
When launching CRM 1.2 through the web interface or through Outlook I am experiencing errors. This occurs on all the PCs on our network. CRM installed on Windows 2000 Advanced Server Client PCs running 2000 or XP Pro Browser IE6 with all updates. The main 3 I see are: Error #1 Line: 3 Char: 1 Error: Syntax Error Code: 0 URL: https://crm/home/home_home.aspx Error #2 Line: 104 Char: 1 Error: 'ORG_DATE_START_DAY' is undefined Code: 0 URL: https://crm/home/home_home.aspx Error #3 Line: 15 Char: 3 Error: 'ORG_DATE_FORMAT' is undefined Code: 0 URL: https://crm/home/home_home...

conditional format 04-15-10
Hi, I want the color of the text in cell B1 to change depending on the value in cell A1 How can this be done? I know I can do it with conditionale format for the cell itself but not for another one. Thanks JP Try this. Assume you want B1 text to change colour when the value in A1 is 5, select B1 and go to conditional formatting. In the condition, select 'Formula is' then in the empty space, type =A1=5 choose the font colour that you want. "Jean-Paul" wrote: > Hi, > > I want the color of the text in cell B1 to change depending on th...

hide/change color of selected row headings
Is it possible to hide the row heading numbers for selected rows (i.e. rows 51 and greater) for just the selected sheet? I'd like to have a color with no row heading number appear that matches the fill color I select for the adjacent cells. Secondly, any ideas as to how I can prevent the user from scolling further down than a certain point (i.e. row 51). Thanks in advance. Joe Row headings are either on or off, you can't hide some. However, you can achieve a similar effect by hiding *all* headings (Tools/Options/General) and putting the numbers 1:51 in A1:A51. to limit scro...

Installation question 06-05-07
I have one server with sql2005 running MOSS 2007 (sharepoint) I would like that sql server also to run CRM dynamics 3.0 pro. We are only 5 users and its a rather fast server so we believe it will go fine performance vice. Can I install SRS from the crm3 disk on a SQL2005 ? does it NEED default website and port 80 ? (the MOSS are using that) any help appreciated Hello, I suggest install SQLServer 2005 Reporting Services (SRS) manually using the SQLServer 2005 installation media. Configure SRS to use a different (new) website, having its own application pool. Then during CRM setup speci...

changing a name in mulit parts of a document
I am setting up a word document. This will be used as a template In this document I wish to insert the same "name" throughtout this document. Is it possible to change the name in one spot and all of the others change automatically thanks See http://gregmaxey.mvps.org/Repeating_Data.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>...

cant send emails 02-14-10
I have tried everything you guys have suggested in the other posts, including uninstalling and reinstalling the mail software. I have two email accounts & neither will send. It had been working since Nov 09 and just suddenly stopped. I have windows 7 & windows live mail. Here is the error I keep getting: Your server has unexpectedly terminated the connection. Possible causes for this include server problems, network problems, or a long period of inactivity. This is the local server I use. Subject 'Fw: Fwd: xoxo' Server: 'smtp.acsalaska.net' Window...

change exchange name suffix
Hi We need to change our exchange server 'name' suffix. The situation is that we have a local DNS domain 'bs.si' where all our company computers and server are (Win2k srv AD) We have also external domain 'bs-group.si' that is also for delivering mail (mx record). The Exchange server name is 'posta' Now when someone connects to Exchange server from outside via port 25 it responds like '220 posta.bs.si ...', but I want that the response will be like '220 posta.bs-group.si'. So that mail that is coming from our server will be seen like that i...

Stocks with high volume increase
Stocks with high volume increase - 04/14/2006 For a list of stocks that had high percent increase in volume, in Dow 30, Nasdaq 100, S&P 100, S&P 400 and S&P 500 indices. please go to: http://www.investment-tools.com and then click on "Enter Reports Area" button. Access to reports area is free. ...

Changing password #2
Hi, If I change a domain user's password, will that user's Exchange Server 2003 mailbox's pssword be changed too? I mean, does Active Directory share the same password with Exchange Server 2003? Thanks! Exchange never had passwords separate from domain, not even in Exchange 4-5.5 era. So you would change just one password, which belongs to AD account. Michael wrote: > Hi, > > If I change a domain user's password, will that user's > Exchange Server 2003 mailbox's pssword be changed too? > I mean, does Active Directory share the same password > w...

select worksheet
There is probably a very easy answer to this question, but if you don't know you ask. I want a specific worksheet to open when I open the workbook. Is there specific code that needs to be written or can I do it in the worksheet properties? The help files seem to be vague on this issue. Thank you for any help. ...