text in caps - Page 4
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 45

Thread: text in caps

  1. #31
    Guest

    Default

     
    On 2002-02-27 15:53, Anonymous wrote:

    <<<
    Correction. Better make that :-

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range, rng2 As Range, cell As Range
    Application.EnableEvents = False
    If Not Intersect(Selection, Columns(5)) Is Nothing Then
    Set rng1 = Intersect(Selection, Columns(5))
    Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
    For Each cell In rng2
    If cell.Formula <> "" Then
    cell.Formula = Format(cell.Formula, ">")
    End If
    Next cell
    End If
    Application.EnableEvents = True
    End Sub
    >>>


    Setting rng1 as the intersection of "selection" and the affected column has an unexpected result - it only affects the cell below the changed cell. By setting rng1 = columns (x) you won't miss any cells.
    This is because you have your Move Selection After Entry set to Down. The posted macro works if Move Selection After Entry is unchecked. I'm not sure whether your suggested amendment fixes it - no time to test it right now.

  2. #32
    Guest

    Default

    Still want work

  3. #33
    Guest

    Default

    On 2002-02-27 17:09, Anonymous wrote:
    Still want work
    What, even after un-checking MoveSelectionAfterEntry?

  4. #34
    Guest

    Default

    The marco crashes when it gets to For Each cell In rng2

  5. #35
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You need the version with the errortrapper. Before you place it in your worksheet code run this in a normal module:

    sub workit()
    application.enableevents = true
    end sub

    Then, replace the code you were working on with the following:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range, rng2 As Range, cell As Range
    On Error GoTo errorhandler
    Application.EnableEvents = False
    If Not Intersect(Selection, Columns(5)) Is Nothing Then
    Set rng1 = Intersect(Selection, Columns(5))
    Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
    For Each cell In rng2
    If cell.Formula <> "" Then
    cell.Formula = Format(cell.Formula, ">")
    End If
    Next cell
    End If
    errorhandler:
    Application.EnableEvents = True
    End Sub

    HTH (For Chr&$t's Sake) (j/k),

    Nate

    [ This Message was edited by: NateO on 2002-02-27 19:35 ]

  6. #36
    Guest

    Default

    Ran the code first still want work

  7. #37
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You ran sub workit() first and (while it worked before) the errortrapper caused it to fail? Can't be...I'm at a loss....Sorry.

    Either way, Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-27 19:46 ]

  8. #38
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just thought I would let you know it does not work for me . I am using Excel '97

  9. #39
    Guest

    Default

    I knew I could not be the only one! I see a lot of people have looked at this post, I wonder how many it works for and how many it does not?

  10. #40
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-02-27 19:43, Anonymous wrote:
    Ran the code first still want work
    When using the event procedures you should
    always use the events Target.....change
    the Selection to target and see if this works

    eg

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range, rng2 As Range, cell As Range

    On Error GoTo errorhandler
    Application.EnableEvents = False
    If Not Intersect(Target, Columns(5)) Is Nothing Then
    Set rng1 = Intersect(Target, Columns(5))
    Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
    For Each cell In rng2
    If cell.Formula <> "" Then
    cell.Formula = Format(cell.Formula, ">")
    End If
    Next cell
    End If
    errorhandler:
    Application.EnableEvents = True
    End Sub


    HTH

    Ivan

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com