Thanks Carim - it worked like magic. :):)
Posts by saurab8
-
-
I am using excel 2016. I got a macro enabled file, which multiple users access and enter data.
Data entry is via a sheet called "New Entry". In "New Entry" Sheet user enter data and then press update button and data is saved in a separate excel file, called "DATABASE" for reporting.
Once the data is saved in the "DATABASE" file, "New Entry" sheet is cleared so user can fill the next set of data.
I have got a code which checks if the file is open in read only, and users closes the file - user do not get a message to save the file.CodeSub Auto_Close() ' To not give saving option when closing the file as read only If Workbooks("DATA.xlsm").ReadOnly Then ThisWorkbook.Saved = True Else End If End Sub
But I want to enhance this code.
I want the to check if there is any information entered in "New Entry" sheet, which user have filled but forgotten to submit, a message prompt is given to the user. Cell U21 is blank if all cells in "New Entry" Sheet is blank. If any cell in New Entry is not blank U21 = "Filled"Code
Display MoreSub Auto_Close() ' To not give saving option when closing the file as read only If Workbooks("DATA.xlsm").ReadOnly Then If Worksheets("NEW ENTRY").Range("U21").Value = "Filled" Then If MsgBox("Un-submitted information in New Entry Sheet. Do you want to close the file?", vbYesNo) = vbNo Then MsgBox ("Complete the entry and update before closing the file.") Exit Sub ElseIf MsgBox("Are you sure?", vbYesNo) = vbNo Then MsgBox ("Complete the entry and update before closing the file.") Exit Sub End If End If ThisWorkbook.Saved = True Else End If End Sub
The issue I am facing in above code is if I say "Ok" to msgbox "Complete the entry and update before closing the file.", I get an excel prompt asking user if they want to save "DATA.xlsm".
This excel prompt have 3 options, "Save", Don't Save" and "Cancel"
I want vba to auto select either "Cancel".I have tried "Application.DisplayAlerts = False" and had not success.
Hope someone can suggest something easy as a solution. -
Thanks for your help. The link did had some cool tips.
I actually changed the initial file which I was linking via Power Query.
Then I refreshed the Power Query as often I needed, that did the trick for me.
-
I had a formula in a table in excel =IF([@STATUS]="",[KEY]&"_"&COUNTIF(INDEX([KEY],1):[@KEY],[@KEY]),""), which showed me how often a value showed in the data. But the same is not working in Power Query
with the formula I use to get if the same value's position in a long data list, and then I use the same in index match formula to find and locate other relevant data
I got data in number of files.. and I want create a dashboard with this data in another file which is macro enabled..
as I get the data from number of files to the dashboard, excel is making me use Power Query to link data.. once the data is linked it becomes a table and I have to add a new column with formula to get the information I need... Plus I can't add the column in raw data files, as they are just downloaded from number of different systems and want users to not worry about formula's and stuff when they get the raw data.. hope I was able to explain my dilemma.
-
hi,
I got a number of sheets in my file.
In one of the sheet - I got a private sub worksheet_change(byval target as range), to capture timestamp.I also got a "All Clear" macro in a module.
"All Clear" - marco clear all the cell value which user fill in the sheet.The issue I am having is when I run "All Clear" macro after that the private sub stop working.
I have to close the file and open it again and it works file.I was thinking if there is a way to call "private sub worksheet_change(byval target as range)" written in a sheet from a Macro written in a Module.
Hope someone can help.
ThanksSaurabh