Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SheetChange Event

    I am trying to write a macro that will allow me to enter a negative number with the minus sign at the end. The macro sees the minus sign in the last position and then checks to see if the rest of the entry is numeric. If so, it converts it to a number with the minus sign in front.

    The problem is (apparently) that the active cell changes before the event triggers, as my macro is changing the cell beneath the one I just entered.

    Is there some way around this problem? Also, where do I have to put this macro so that it is always available?

    Any help would be greatly appreciated. I guess too many years of entering data on an AS400 keyboard have got me in a rut,

    Thanks,
    Ken

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SheetChange Event

    The macro should be placed in the Workbook Object so that it is triggered for entry in all sheets. In the VBA editor double click on the ThisWorkbook object and use the following :-<pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    ' enter your code here
    Application.EnableEvents = True
    End Sub
    </pre>

    If you want this to be available in all your future workbooks, save the files as Book.xlt in you xlstart directory. It is important that you use the Save As option and select template as the file type.

    It should work without the switching of Application.EnableEvents, but if you use it make sure you trap all errors, or it may be left in a disabled state.

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SheetChange Event

    Andrew, Thanks much for responding. The macro is in the workbook module, and I added the lines you suggested. However, It still changes the cell below the one I just entered. Any more ideas would be appreciated.

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim i As Long
    Dim work As String

    Application.EnableEvents = False
    ' enter your code here
    work = ActiveCell.Formula

    If Right(work, 1) = "-" Then
    If IsNumeric(Left(work, Len(work) - 1)) Then
    ActiveCell.Formula = "-" & Left(work, Len(work) - 1)
    End If
    End If

    Application.EnableEvents = True

    End Sub

    </pre>


    TIA
    Ken

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SheetChange Event

    Ken, I have tried your code and it works just fine on my set up. I don't have excel set up to advance to any cell after data entry, but when I set it to move down, the code did not work at all. Try de-selecting the Move Selection after enter (Tools|Options|Edit), and if you wish have your macro advance the selection after the value has been updated.

    Hope that works

    Andrew

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SheetChange Event

    Hi Ken,
    If you want to keep your Move Selection after Enter setting,
    you need to change ActiveCell to Target in your code - i.e.:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim i As Long
    Dim work As String
    Application.EnableEvents = False
    ' enter your code here
    work = Target.Formula
    If Right(work, 1) = "-" Then
    If IsNumeric(Left(work, Len(work) - 1)) Then
    Target.Formula = "-" & Left(work, Len(work) - 1)
    End If
    End If
    Application.EnableEvents = True
    End Sub
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SheetChange Event

    Thank you Andrew and Rory. I'll get report back to you on this in a couple of days when I have a chance to try it.

    Ken

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SheetChange Event

    Thanks to Andrew and Rory for their help.

    Here's the final code I'm using. Seems to be working fine.

    <pre>Option Explicit

    Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Dim work As String
    Dim i As Long
    On Error GoTo Goodbye
    work = Target.Formula
    If Right(work, 1) <> "-" Then Exit Sub
    work = Left(work, Len(work) - 1)
    If IsNumeric(work) Then
    work = "-" & work
    If Application.FixedDecimal = False Then
    Target.Formula = work
    Else
    Target.Formula = Val(work) * 0.01
    End If
    End If
    Goodbye:
    End Sub
    </pre>


    Ken

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SheetChange Event

    Nice one Ken, thanks for the feedback.

    Andrew

Posting Permissions

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