+ Reply to Thread
Results 1 to 17 of 17

User form appears only when user changes data manually, not with cell value by formula

  1. #1
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    User form appears only when user changes data manually, not with cell value by formula

    I want a vba code when certain cell value in a column changes automatically by formula, user form should appear.
    not when user manually change.
    Anyone pls help!!!

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: User form appears only when user changes data manually, not with cell value by formula

    paste the code in worksheet module.
    any changes in named range will make form appear.
    can be very irritating on a busy sheet
    torachan.

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: User form appears only when user changes data manually, not with cell value by formula

    @torachan
    Please note that a Worksheet_Change event will not be triggered by a manual change of data. If the change is the result of a formula, a Worksheet_Calculate event is needed.

    @rohit2019
    Is the formula in one cell or multiple cells? Which cells or cells contain the formulas? It would be easier to help if you could attach a copy of your file (de-sensitized if necessary).
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    Thanks torachan

  5. #5
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    But in my sheet formula calculating the value

  6. #6
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    Not the user changing manually

  7. #7
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    Pls find the attached file and advise me
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    Any one can help on this file ??

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: User form appears only when user changes data manually, not with cell value by formula

    You have formulas in columns F, G, H and I. Do you want the userform to be displayed when any value in columns F, G, H and I changes? Also, the current code in your userform doesn't do anything other than define two variables.

  10. #10
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: User form appears only when user changes data manually, not with cell value by formula

    Hi rohit2019,

    Your requirements were not clear so I added 'Sheet1' to your sample file and added code to use Worksheet_Calculate() to identify changes by formula. This is done by:
    a. Storing the initial values in the cells that you are interested in
    b. Identifying which if any of those cells changed value when there is a 'Calculate Event'

    See the attached file that contains the following code:

    In the ThisWorkbook Code module:
    Please Login or Register  to view this content.
    In Ordinary Code Module ModFindChanges:
    Please Login or Register  to view this content.
    In the Sheet1 code module:
    Please Login or Register  to view this content.
    To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). See https://www.excel-easy.com/vba/examp...-explicit.html

    Lewis
    Last edited by LJMetzger; 11-17-2019 at 01:51 PM.

  11. #11
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    I want user form to be triggered only when formula calculates "TM" in cell range I2:I6.
    Then when user input the value in user form and submits ok ,it should assigned to the same row but in column D and E.

  12. #12
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    Thanks LJMetzger for your reply but my requirements are as follows

    I want user form to be triggered only when formula calculates "TM" in cell range I2:I6.
    Then when user input the value(may be number or text) in user form and submits ok ,it should assigned to the same row but in column D and E.

  13. #13
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: User form appears only when user changes data manually, not with cell value by formula

    Hi,

    Try the following file which reflects your latest specifications.

    Code in the Sheet MONITOR MEDICIENE Code Module:
    Please Login or Register  to view this content.
    Code in the UserForm Code Module:
    Please Login or Register  to view this content.

    Code in Ordinary Code Module ModFindChanges:
    Please Login or Register  to view this content.
    Lewis

  14. #14
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Thumbs up Re: User form appears only when user changes data manually, not with cell value by formula

    Thanks a lot Mr. Lewis.

  15. #15
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    Hi .
    In the attached file,when the userform automatically triggers
    and w/o putting the values if I opened another Excel file and return to put the values in user form
    ..it's showing run time error 9(subscript out of range.
    How to avoid this?

    Pls help

  16. #16
    Registered User
    Join Date
    11-16-2019
    Location
    SAUDI ARABIA
    MS-Off Ver
    Office 365
    Posts
    14

    Re: User form appears only when user changes data manually, not with cell value by formula

    Any one can help on the above error...

  17. #17
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: User form appears only when user changes data manually, not with cell value by formula

    Sorry for the problem. I changed as little as possible in the Original code, not expecting you to have a second Workbook Open.

    See the corrected attached file - changes highlighted in red below.

    In the Sheet MONITOR MEDICIENE code module:
    Please Login or Register  to view this content.
    In the UserForm Code Module:
    Please Login or Register  to view this content.

    In the ModFindChanges Code Module:
    Please Login or Register  to view this content.
    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delimited Text To Columns Utlizing a User Form Text Box Can't Manually Enter Data
    By simpdogg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2016, 03:58 PM
  2. [SOLVED] User Form with Empty row but user may enter data manually
    By Nole68 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2015, 11:35 AM
  3. User form to post data and charts according to user defined selections
    By siroco79 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2013, 11:16 AM
  4. Replies: 1
    Last Post: 08-28-2013, 05:04 PM
  5. [SOLVED] User forms - choosing location of your data to be shown in your user form
    By jasonbwt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2012, 08:48 AM
  6. How to Create User form which appears upon opening workbook?
    By MacMasta in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2012, 12:55 PM
  7. Call user form for logged in user's cell only
    By Spagbog in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-11-2011, 10:41 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1