+ Reply to Thread
Results 1 to 3 of 3

Auto add space and / when I press space bar in excel 2003

Hybrid View

bajramo Auto add space and / when I... 02-28-2016, 04:59 AM
:) Sixthsense :) Re: Auto add space and / when... 02-28-2016, 05:07 AM
bajramo Re: Auto add space and / when... 02-28-2016, 05:47 AM
  1. #1
    Registered User
    Join Date
    02-28-2016
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    8

    Auto add space and / when I press space bar in excel 2003

    I want to write a macro in Excel so every time I hit space bar (when I'm in column I ) I want to automatically send "space slash and space again".

    eg. Im in cell I150. I type 00123 press space bar on my keyboard and instead just blank space, I want to get space slash space again so I can finish my data.

    I want this to happen ONLY when im in cell on column I.

    Thanks

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Auto add space and / when I press space bar in excel 2003

    Copy the below code and do right click on sheet tab and select view code and paste it.

    Close the VBA window (Alt+Q to close VBA window) and return to that sheet and check.

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        If Target.Columns.Count = 1 Then
            If Target.Cells.Count = 1 Then
                If Target.Column = Range("I:I").Column Then
                    Application.EnableEvents = False
                        Target.Value = Replace(Target.Value, " ", " / ")
                    Application.EnableEvents = True
                End If
            End If
        End If
        
    End Sub
    The above code will convert the spaces as you described after pressing the enter button only. It will not replace the space when you are in editing mode.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    02-28-2016
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    8

    Re: Auto add space and / when I press space bar in excel 2003

    Thanks, I found one more solution:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Columns(9)) Is Nothing Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            Dim rng As Range
            For Each rng In Intersect(Target, Columns(9))
                'make safe anything that already has been changed
                rng = Replace(rng.Value2, " / ", ChrW(8203))
                'change new spaces
                rng = Chr(39) & Replace(rng.Value2, Chr(32), " / ")
                'try to repait Excel's auto-date-conversion
                If IsDate(rng) Then Application.Undo
                'restore the safeties
                rng = Replace(rng.Value2, ChrW(8203), " / ")
            Next rng
        End If
    
    bm_Safe_Exit:
        Application.EnableEvents = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Shift+Space and Ctrl+Space shortcuts intermittently working
    By yumyumdimsum in forum Excel General
    Replies: 8
    Last Post: 10-14-2017, 11:54 AM
  2. VBA: Put extra space before units (keywords) if space is not there
    By sroysroy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-25-2015, 10:19 AM
  3. Need VBA Script to press space bar button
    By rajdotcom in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-25-2015, 06:37 AM
  4. parsing text return space to space
    By dosmastr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2015, 12:09 AM
  5. [SOLVED] If Cell Starts with a Space, remove that space
    By Ocean Zhang in forum Excel General
    Replies: 2
    Last Post: 09-29-2012, 01:52 PM
  6. replace double space with single space within a range
    By erikd80 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-05-2011, 01:20 PM
  7. Paper Space / Model Space ?
    By Coolboy55 in forum Excel General
    Replies: 0
    Last Post: 09-01-2005, 03:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1