We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x
Private

Performing certain activities on the worksheet at the time of Opening of File using MACRO

High Priority
71 Views
Last Modified: 2020-05-19
Hi Experts,

In the attached file I wish a macro which performs the following when the file is opened
I am using OFFICE 365.


BiltyDB Sheet
1. Active cell is Set to A1
2. Clear the Filtered items, if any
3. Turn the Auto Filter On if the same is not already done
4. Convert the entries in Column K and R to UPPER case
5. Update the entries in Column P (Where Validation is Active, but the Text Case might be different) with the exact entry available in the Validation List

TptrDB Sheet
1. Clear the Filtered items, if any
2.Turn the Auto Filter On if the same is not already done
3. Trim the extra spaces from Column C, D, E, F
4. Convert the entries in Column C, D, E, F to Proper case
5. Sort the Table when the
          1st Sort Key is TptrDB[Tptr Id]")
          2nd Sort Key is TptrDB[Owner Name]")
6. Where the code is not available in Column B against the value in Column C (Owner Name) and Column K (Transported ID), then paste the VALUES only from Column K to Column B
7. Convert the entries in Column G to UPPER case
8. Check whether the entries in Column G is validated using ValidPAN($G3)=TRUE and incorrect value is highlighted

Vehicle DB Sheet
1. Convert the entries in Column D to UPPER case

Regards
Kanwaljit
EEE-Activities-to-be-performed-when.xlsm
Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

Commented:
Hello Kanwaljit
I see the file you posted doesn't hv the latest version based on last question is this intended ?
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Yes, It is not the latest Version as some questions were going on parallel. I thought of merging or managing the macro as and when both the issues are resolved. I am going to post a new file here. 
gowflowPartner
CERTIFIED EXPERT

Commented:
Here are some questions:

BiltyDB Sheet
5. Update the entries in Column P (Where Validation is Active, but the Text Case might be different) with the exact entry available in the Validation List

Your validation is not working in 2010 hence I cannot check anything. Furthermore, validation are there to 'VALIDATE' data why you need a macro to revise the validation. I thought in your previous question we need to turn this col Proper is this enough?

TptrDB Sheet
8. Check whether the entries in Column G is validated using ValidPAN($G3)=TRUE and incorrect value is highlighted
I have put sometime on this one, and finally the most suitable way I found was thru the worksheet change event
1) do the coloring orange if not as per the formulas together with a msgbox telling the user wrong format. with a beep .. and the cursor stays there then the user can either correct or move to next cell, but it draws well his attention.
2) If ok with formulas do it lite blue and lighter version of the header
3) I removed the conditional formatting for that column that was interfering with this event and was doing black cells and falshing cells that I could not explain why.

PLease advise for these 2 as basically almost ready with the version.
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Your validation is not working in 2010 hence I cannot check anything. Furthermore, validation are there to 'VALIDATE' data why you need a macro to revise the validation. I thought in your previous question we need to turn this col Proper is this enough?
I need to revise the Validation because the case in such Validation List is a Mixed one. Normally it is proper case, but it included some names which contain Capital words e.g., Reliance Industries (INDIA) Limited. Here Proper Case would change it to Reliance Industries (India) Limited
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Okay I am providing a File with Validation List.
This Updated File Contains a Validation List which should work fine in Excel 2010

Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Please find this file.
Conditional Formatting on TptrDB sheet is updated.
EEE Activities to be performed when File is Opened.xlsm

Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
TptrDB Sheet
8. Check whether the entries in Column G is validated using ValidPAN($G3)=TRUE and incorrect value is highlighted
You may skip this portion, as it seems the same is working well through Conditional Formatting Formula itself.
It retains its structure even if we sort the data, enter a row or delete a row.

Kanwaljit
Partner
CERTIFIED EXPERT
Commented:
Hi Kanwaljit
It took 2 days+ full time working on your file as really challenging. I have to admit this one really got me puzzelled in many ways but finally got it all working fine and good and will need you to check all up and advise.

Let me make some comments on different issues here.

1) Let go back to the Add Rows routine that I noticed despite my solution proposed on the last question you still adopted the Autofill on the workbook you posted here. Let me tell you why I find this Autofill not appropriate in your case. If you are not using a filter while adding rows then your Autofill is fine as it will add new rows to the existing ones that the user can see. The problem is when you have a filtered data then all what the user sees is the result of the filter and do not see if there are blank rows. The danger in using the Autofill like you have and not my proposed solution is that the new rows added will be apparent and the user may mistakenly keep the filter and start inputting data in the new rows and when the filter is removed you will see a group of data then blank rows then an other group of data which is definitively not what you want as I recall you were afraid that the user would input data far down …
This is why I strongly recommend my routine that will add rows while filter is applied and they will not show so the user cannot add data elsewhere he is forced to remove filter and all data will follow.

2) I noticed that in sheet TptrDB you have a TABLE well for the table all what you requested was fine EXCEPT the sorting. For some reason as you have field in Col A that is filled and some rows in B,C,D etc.. are blank so when you sort by Col B and C the system puts the blank rows on top and I am sure this is not what you would like to see.

After long research and trials I came out to a routine where by once the routine for this sheet is invoked it removes the table (temporary) do all the calculations do the sorting then puts back the table and in this way I am able to sort only by the filled ranges in B and C even if you have lots of data in Col A.

3) I noticed that in Col K you have an Array formulas that is not needed furthermore it point to field in the table that once sorting gets messy again here I had to research a lot and after found the trick to remove references to the field table and simply do the formulas based on columns like you will see it in the file.

4) Last but not least, I added all the routines in the file you posted to this version that is supposed to be the full last updated version. I would strongly recommend you always work on 1 VERSION and not many as at the end you can be easily lost and have a challenge to incorporate routines from different versions into 1 final.

Please check this version attached try all routines and let me know your comments.
Regards
Gowflow
EEE-Add-Blank-rows-end-actual-V07.xlsm

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Hi Gowflow,

Apologies for delayed response !
Thanks a Lot for the detailed reply and the mammoth efforts put in by you. I myself am too highly dedicated to my work and you make me a bit jealous! Kindly allow me a days time as I won't be accessing my system for 1 day. I am eager to test the same and will get back to you ASAP.

Sincere Regards
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
Tks Kanwaljit I also take work at heart and no problem take your time.
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Any chance to have had a look at this ?
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Hi Gowflow,
I am testing the solution on actual scenario. Don't want to post reply in haste and it would be criminal to engage yours attention and energies, till I am sure myself. You work too hard to be engaged casually.

Regards
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
TKs no problem take your time.
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Thanks a Lot Gowflow !

It took much longer than perceived ! Sincerest Apologies for that (and Yes, I truly understand the feelings it brings)
Closing the question just to close it, would have been sheer insult to the efforts put in by you.
So I opted to give it the time it deserve and tried to understand the code, though I am not sure how much of it I understood. You code a bit differently than what I am used to read. Like an Expert sort of. And I am used to reading code like the macro recorder does :)
So my apologies if I took too much time than the usual required.

Tested the solution against the requirements and found that it is working nearly perfectly. A few exceptions
First issue
5. Update the entries in Column P (Where Validation is Active, but the Text Case might be different) with the exact entry available in the Validation List
In the Source Data List on Sheet ConsgnDB, the Values are CH and CA, whereas the Values in Column P in BiltyDB sheet are Ch and Ca. Off course the Data validation is not going to object in such a case as the source of the validation drop down is a named range list.
The code was expected to change Ch and Ca to CH and CA
Similarly abc (India) private Ltd was expected to change to ABC (INDIA) Private LTD

Second : For this issue I have found the reason and a workaround also, which I have reported it here for yours information. ( But unrelated to my issue and solution thereof, the code in the file uploaded strangely enough is not trimming the values in column C of TptrDB sheet. Whereas I have not made any changes to the code you wrote)
My issue was
6. Where the TptrID is not available in Column B against the value in Column C (Owner Name) and Column K (Transporter ID), then paste the VALUES only from Column K to Column B
Why I required it ?
I am using the following formula in Column K to automatically create a Tptr ID for the Values in Column C, so that any new name entered is given a code, without the user intervention and discretion.

=IF($C3="","","T"&UPPER(LEFT($C3,1)&TEXT(SUMPRODUCT((LEFT(INDEX($C$3:$C$22,1):$C3,1)=LEFT($C3,1))*1),"0000")))
If I use the formula in Column B itself, then it would create a situation where the user might sort the data and the Tptr ID's get changed. So I did the calculations in a different column i.e., column K and then pasted only the values in Column B. Now sorting would not impact the Tptr ID. Also Tptr IDs serial would not be affected as we are not going the delete values from column C.

Now suppose user entered a new value in Column B with leading 3 spaces like "    apci ltD". Column K formula will generate a Code which contains a space like "T 0001" instead of TA0005 So I have changed the formula to trim the values before generating the Tptr ID, like
=IF($C14="","","T"&UPPER(LEFT(TRIM($C14),1)&TEXT(SUMPRODUCT((LEFT(TRIM(INDEX($C$3:$C$22,1):$C14),1)=LEFT(TRIM($C14),1))*1),"0000")))
So my solution was to remove the leading spaces before generating the code.

The code pasted Only T 0001, as the generated Tptr ID was T 0001 and I found that in the macro Calculation was set to manual so after trimming the fresh values were not generated and T 0001 was pasted as it is. I can easily change the code to set it to automatic just before pasting values in Column B. But I feel change in formula was a simpler way.

Sub IniTptrDB()
Dim WS As Worksheet
Dim MaxRow As Long, MaxCol As Long, MaxData As Long, I As Long, J As Long
Dim sTitleRange As String, sTblRange As String

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With
Now the code in the file uploaded strangely enough is not trimming the values in column C of TptrDB sheet. Kindly check the value in Cell C14

So in lump sum, Other than the First issue, the code is working Superfine. Though it will take some time for me to customize it for the actual scenario, but it has served its purpose, PERFECTLY again.

I can fully understand, if you wish me to close the question here, as the connection with the question must be broken right now. No issues at all.

I must say ! Fantastic Job again !
EEE-Add-Blank-rows-end-actual-V07 - testing.xlsm
Salute
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
Hello after this long time.

Please in 1 or 2 sentence what is required from me ?
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Nothing Gowflow !
An unconditional apology and a Lot of Thanks !!!
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
Why you apologies. ? No problem at all. IF you want me to fix anything please let me know I have no problem. Your explanation was long but I was lost at the end.
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
I am not after point but rather after providing a workable fully satisfying solution for sure within the frame of the question. We can always improve and extend as much as we want. But a question remains a question.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
"Points"
I understood it a long ago that people at EE are certainly not after the points. It is the job satisfaction which keeps the spirits going.
I can understand. It is indeed frustrating to connect with the question after such a long time. I tried to be clear, so that time is not wasted again.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
To be brief
5. Update the entries in Column P (Where Validation is Active, but the Text Case might be different) with the exact entry available in the Validation List  ---> is not working.

and
4. Convert the entries in Column C, D, E, F to  Proper case
which was working when I opened the file, but when I entered the last value (as in file uploaded), it stopped working.


gowflowPartner
CERTIFIED EXPERT

Commented:
ok thanks for all your effort in taking care of making the point which is well noted. Please feel free to let me know any other question you may need help with I will be glad to assist.
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
To be brief
5. Update the entries in Column P (Where Validation is Active, but the Text Case might be different) with the exact entry available in the Validation List  ---> is not working.

and
4. Convert the entries in Column C, D, E, F to   Proper case
which was working when I opened the file, but when I entered the last value (as in file uploaded), it stopped working.
gowflowPartner
CERTIFIED EXPERT

Commented:
Oops our replies crossed.
Validation:
Coz your using 365 I cannot see them. So have no way to verify data. Let me look at the file you posted if I can see the validation
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Validation is not unique to Office 365, so it does not matter, which version do you use.
gowflowPartner
CERTIFIED EXPERT

Commented:
Well just tried it when I click on validation arrow in Col P of BiltyDB nothing happens I can't find data !
For 4. Convert the entries in Column C, D, E, F to   Proper case  of sheet All C D E F are proper in the sheet you posted.

Proper is the first letter of every word is Capital.
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Excel 2010
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Okay. Got the Point. Here is a new file. As per Excel 2010. EEE-Add-Blank-rows-end-actual-V07 - testing.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Changed the base of source list for validation. No dynamic formula reference as per Office 365.
gowflowPartner
CERTIFIED EXPERT

Commented:
Can we do this tomorrow please its 1:25AM here. Again Proper is first letter in Capital like
Joe Doe
and Col C D E F in the sheet are all Proper so I don't see where is the problem.

I will now wait to see your answer
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Strangely Proper Case is working now. Highly strange. Seems it needed excel to restart.
4:00 am in India. So No issues at all.
See you tomorrow.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Only the Validation issue remains.
Thanks.
gowflowPartner
CERTIFIED EXPERT

Commented:
Your validation is really weired !!! It is not doing the validation I can type anything there is just works. I cancelled it and redone it now it works and validate but it is also weired it piks data lower characters. I will look at this tomorrow.
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
ok I fixed your validation as follows:

I kept your original validation from Excel and when this does not fail as it is not case sensitive it will go in the worksheet change event and test if indeed the string that was entered is in the validation list but not properly written then it will write it EXACTLY as it is in sheet ConsgnDB Col C

Check it and let me know.
Gowflow
EEE-Add-Blank-rows-end-actual-V08.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
It is not changing.
 
Consignee Name
Ch
Ch
Ch
Ch
Ch
Ch
Ca
Abc (India) Private Ltd


Ch
Abc (India) Private Ltd
A


It is showing as above.
gowflowPartner
CERTIFIED EXPERT

Commented:
old data it will not change !!!! the validation is on input not on open workbook.
to change existing data simply select the cell press F2 then enter it will change it to whatever there is in validation.

Your production workbook has a lot of data that is wrong ?
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Yes Gowflow !
I am thinking of some other solution for such an issue.
So I would request you not to spend more time on this problem.
You are right about F2. If we press F2 and then press Enter, the value will automatically get changed to the actual value as per validation source list. I was trying to get that in macro. But I guess and assess that it is not any roadblock to normal working. It can be handled via separate macro.

What I trying to do is to encapsulate small utility macros so that they can be used wherever required, without disturbing the overall code.

Regards
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
What I trying to do is to encapsulate small utility macros so that they can be used wherever required, without disturbing the overall code.

Not clear what you mean ?
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Small modules to ConvertProperCase, ConvertUpperCase, TrimSpaces, TurnFilteron which can be used within other code to accomplish small objectives without having to create the same each and every time.
gowflowPartner
CERTIFIED EXPERT

Commented:
ok if you want post a question for eachone but one after the other and put the link in here and I will be glad to assit.
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Right Sir !
Thanks Again !
gowflowPartner
CERTIFIED EXPERT

Commented:
waiting for ur next question
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
I must admit that Office 365 is a far better and powerful tool than the earlier versions.
gowflowPartner
CERTIFIED EXPERT

Commented:
Well being developer is not like end user u need to cater for earlier versions.
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Definitely. Couldn't agree more.
gowflowPartner
CERTIFIED EXPERT

Commented:
I saw your 2 questions posted but this is not in the scope as what you asked here. You need my help there ? don't want to waste time reading and checking when someone else has already given a similar answer.
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Sure. I Understand. I am working on those solutions. Will get back to you if it doesn't work out.
Thanks a lot Gowflow !
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.