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!!!
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!!!
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.
@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.
Thanks torachan
But in my sheet formula calculating the value
Not the user changing manually
Pls find the attached file and advise me
Any one can help on this file ??
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.
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:
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.htmlPlease Login or Register to view this content.
Lewis
Last edited by LJMetzger; 11-17-2019 at 01:51 PM.
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.
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.
Hi,
Try the following file which reflects your latest specifications.
Code in the Sheet MONITOR MEDICIENE Code Module:
Code in the UserForm Code Module:Please Login or Register to view this content.
Please Login or Register to view this content.
Code in Ordinary Code Module ModFindChanges:
LewisPlease Login or Register to view this content.
Thanks a lot Mr. Lewis.
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
Any one can help on the above error...
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:
In the UserForm Code Module:Please Login or Register to view this content.
Please Login or Register to view this content.
In the ModFindChanges Code Module:
LewisPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks