• SheetChange Event

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » SheetChange Event

    Author
    Topic
    WSkjktoo
    AskWoody Lounger
    April 9, 2001 at 9:26 pm #354849

    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

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSAndrew Cronnolly
      AskWoody Lounger
      April 9, 2001 at 10:53 pm #522192

      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 :-

      Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
' enter your code here 
Application.EnableEvents = True
End Sub

      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

      Reply | Quote
      • WSkjktoo
        AskWoody Lounger
        April 10, 2001 at 2:15 am #522214

        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.

        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

        TIA
        Ken

        Reply | Quote
        • WSAndrew Cronnolly
          AskWoody Lounger
          April 10, 2001 at 7:06 am #522229

          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

          Reply | Quote
        • WSrory
          AskWoody Lounger
          April 10, 2001 at 8:44 am #522242

          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.

          Reply | Quote
          • WSkjktoo
            AskWoody Lounger
            April 10, 2001 at 10:44 am #522245

            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

            Reply | Quote
    • WSkjktoo
      AskWoody Lounger
      April 11, 2001 at 8:53 pm #522568

      Thanks to Andrew and Rory for their help.

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

      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

      Ken

      Reply | Quote
    Viewing 1 reply thread
    Reply To: SheetChange Event

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.