Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA - USA
    Posts
    21
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Help with VBA code

    Hi all,

    Am using Excel 2010.

    Cell K3 shows the monthly total.
    When this total reaches 250 a message will be displayed.

    Tried to run the following as a macro:

    Private Sub Worksheet_Warning()

    If Worksheets("Sheet1").Range(K3") > 250 then
    MsgBox "Limit reached"
    End If

    End Sub

    It returns the message Syntax Error after hitting F5 to test

    (It's my first attempt to write code)

    Thanks,
    Albert

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Albert,

    Your code works if you just add the missing " at the beginning of K3.
    However, you might want to put this code in the Sheet1 code area of your project as it will fire automatically everytime a cell is changed but only issue a message if K3 is greater than 250.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("K3"), Target)
       If isect Is Nothing Then
        ' MsgBox "Ranges do not intersect"   '*** uncomment for debugging only ***
       Else
         If Range("K3") > 250 Then
           MsgBox "Limit reached"
         End If
       End If
    
    End Sub
    Attached Files Attached Files
    Last edited by RetiredGeek; 2011-12-15 at 15:29.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    abaas (2011-12-15)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    I was interested in the
    ''*** Limiting the Worksheet_Change event to a firing when a single cell is changed"

    ..but this code also triggers when multiple cells are changed.

    A single cell is changed only when
    Target.Cells.Count = 1

    Multiple cells are changed whenever you 'copy and paste' a block of cells.

    In your code, if you copy a block of cells and paste them in such a way that cell [K3] is included in the paste range, then you will either get NO message at all OR the "Limit reached" depending on the actual value that end up in [K3]

    So your solution does work of course for the question posed.
    Am I being too picky?

    zeddy

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Thanks for the input. So we limit it by changing one statement:
    If Target.Cells.Count = 1 And Range("K3") > 250 Then

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #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
    If K3 is a formula, then neither will work unless you re-enter the formula...
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    This variation seems to work if the cell to be checked is a formula. Just check to see if any of the cells which make up the formula are the ones changed then check the limit cell for current value.
    See Sheet2 in the attached for this example.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("A1, B2, C3"), Target)
       If isect Is Nothing Then
    '     MsgBox "Ranges do not intersect"
       Else
         If Range("K3") > 250 Then
           MsgBox "Limit reached"
         End If
       End If
    
    End Sub
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    abaas (2011-12-24)

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    The simplest solution is of course:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("K3") > 250 Then
    MsgBox "Limit reached"
    End If

    End Sub

    ..since all you are interested in is the value in cell [k3].
    This will work whether [k3] is a formula or not.

    You don't need to check whether a single cell or multiple cells have changed on the worksheet.
    There is no speed advantage in this simple case.
    All you are interested in is the value in cell [k3]

    The point I was trying to make is that to test for when a single cell is changed is rarely appropriate in my experience.

    zeddy
    is

  10. #8
    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
    That depends entirely on the situation. If you are monitoring the input in one cell (say a DV list which then affects various reports) it may be quite appropriate.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Re: '*** Limiting the Worksheet_Change event to a firing when a single cell is changed

    The test for when a single cell is changed is Target.Cells.Count = 1
    I repeat - this is rarely of use in my experience.

    If you tested for a single cell being changed AND then tested to see if it was your particular cell that changed, you will miss out the cases when a block of cells have been changed (i.e. when Target.Cells.Count > 1 )
    which also include your cell of interest.

    If you are montoring the input in one particlar cell you still need to allow for the fact that your single cell of interest can also be affected when multiple cells are changed at the same time, e.g. by a cut and paste operation, or by an import operation etc. It's the documentation comment line I have an issue with. Perhaps it should say:
    '*** Using the Worksheet_Change event to see if a particular cell is changed

    zeddy

  12. #10
    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
    Agh, sorry - misinterpreted what you meant. I agree completely with that - it is indeed grossly overused, particularly in code I see provided on forums.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #11
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Does anybody know the answer to this query please:

    I'm able to disable menu items with either name or ID name i.e

    Sub MenuControl_Enabled_False
    Dim Ctrl As Office.CommandBarControl()
    For Each Ctrl In Application.CommandBars.FindControls(ID:=522)
    Ctrl.Enabled = False
    Next Ctrl
    End Sub

    But what I'm struggling with is disabling the Security menu within the options pop up menu, do you know how I can overcome this please.

  14. #12
    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
    Which version of Excel and which security settings are you after?
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #13
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Apologies, Excel 2003
    From Tools / Options then the pop up menu box has a security tab and it's this I would like to remove, I have already disabled the security option from the macro menu within tools, however, need this one as well

  16. #14
    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
    I don't believe you can do that with VBA per se - you need a group policy I think, unless you want to disable the whole Options setting.
    Regards,
    Rory

    Microsoft MVP - Excel

  17. #15
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, for your response and time, currently the options menu is disabled, however, getting complaints that some options within options are required like the general tab.
    Will look into group policies

Posting Permissions

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