Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macro requirement

    Hello,


    I have a report in Excel 2007 format comprising 5 columns, namely, Title, Object ID, Issue No., Version, and External ID, which is filled by each member of my team.

    My requirement is to create a Macro (that is compatible with 2003 and 2007), where when a user update a column, "Object ID"; he should be enforced to fill the columns, "Issue No." and "Version" as well and should be restricted from saving or closing the document unless the mentioned columns are filled.

    For reference, I am attaching the report I am working on.

    Thanks in advance.

    Regards,
    Gurpreet Kaur
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    I understand preventing saving and closing the document if "Issue No." and "Version" are empty.

    What do you mean by "enforced to fill the columns, "Issue No." and "Version"?" what exactly do you want the code to do after someone enters something in ObjectID?

    Steve

  4. #3
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for you reply, Steve!!

    By enforced, I meant that as and when the content of "ObjectID" field is changed, the person should be forced to also update the "Issue No." and "Version" fields without which should not be allowed to save and close the Excel file.

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    If they truly are "forced to also update the "Issue No." and "Version" fields" then prevention of saving and closing without them being done is impossible.

    But the question remains "what exactly do you want the code to do after someone enters something in ObjectID?" How do you envision code "forcing" them to do something. For example, imagine the user makes an entry in B2. After the entry is made, what do you want excel/vba to do exactly?

    Steve

  6. #5
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Sorry for not being clear in my previous posts.

    My requirement is when the ObjectID is updated, the Version and Issue no. columns corresponding to the ObjectID should ALSO be updated.

    Since we are already using this report, I observed people forget to update the version and Issue no., that is why to ensure that both of these are also updated I thought if there can be a macro for this.


    For example:
    If the user changes the value in B2, he should also change the corresponding C2 and D2 cells.

    P.S: There could be a scenario where objectID does not change at all but if it gets changed, the other two columns that is, issue no. and version should also be changed.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    I understand the requirement. But it seems to be an issue with training your people. We can create code to fill in the values (if you define what numbers you want the code to fill in) or even post a note that it must be filled. It can be checked when they close and save.

    But how do you want to enforce it after they enter something in B and before they try to save and/or close. If they save or try to close and do not fill values in c&D do you want the non-compliant values in B erased or do you want to do something else?

    The question is defining enforcement...

    Steve

  8. #7
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hello Steve,

    I managed to put the condition, that is, when Column B is filled the application prompts to enter the value in corresponding C and D cells.

    Now I want that whenever the Column B value changes, the prompt for filling the issue no. and version should again be prompted.

    In current scenario, it works only when I deleted the content but if I edit the value by double clicking the cell, the prompt does not come.

    Can you help on this, please ??

    Thanks,
    gurpreet
    Attached Files Attached Files

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    As it is written, when an entry in col B is edited the code notes the change in B, but since C&D are NOT blank (the requirements are fulfilled), it does not prompt. If you want to always be prompted,clear the contents of C&D in the row whenever a change is made in Col B.

    Steve

  10. #9
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    how can i detect if something has changed in the range of cells ?

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    The "target" in the worksheet_change event is the range of cells that has been changed. That is how the "intersect" part of the code functions.

    That is a very general answer to a very general question. Are you asking for something in particular?
    Steve

  12. #11
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Sorry for asking an obvious question, as I am a novice to Excel VBA programming.
    I want that whenever anyone updates any cell of column B, the complete row should be highlighted with yellow color.

    That's why asking if we can scan the complete column to find out the particular cell that got updated.

    Would request if you can provide the code as well. I have googled a lot but nothing is coming out as per my requirement.

    Thanks.

  13. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    Gurpreet,

    This code will color the entire row when ever a cell in Col B is changed. The code has to be placed in the Sheet where you want to use it, in this case Sheet1, see graphic.
    Code:
    Option Explicit
    
    Sub Worksheet_Change(ByVal Target As Range)
    
        If Application.Intersect(Range("B:B"), Target) Is Nothing Then
          '*** Do Nothing not interested ***
        Else
          With Target.EntireRow.Interior
              .ColorIndex = 3
              .Pattern = xlSolid
          End With
        End If
        
    End Sub
    You'll probably need code to execute upon saving or reloading the workbook to clear the highlighting.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


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

    gurpreet (2011-07-13)

  15. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    I prefer the construction:
    If Not Application.Intersect(Range("B:B"), Target) Is Nothing Then
    With Target.EntireRow.Interior

    So it does not need the ELSE part...

    Steve

  16. The Following User Says Thank You to sdckapr For This Useful Post:

    gurpreet (2011-07-13)

  17. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    Steve,

    Good Point!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  18. #15
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you so much RG!!

    This is what I was looking for Thanks a tonne !!
    This works perfectly fine, however, I observed that it highlights even when the cell is simply double clicked, when no text is either entered or removed. Can Excel in any way detect this as well, whether its just a double click or actually clicked for editing purpose ?

Page 1 of 2 12 LastLast

Posting Permissions

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