• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro with Text File

C

collinn

New Member
Hi,


Need help in this.


An excel form is opened by many users in a share folder. Usually the second person open the file, it will become read only. But there is case that 2 persons able to access in the same time.


So I am thinking to have a text file to add one more security. The text file is with "UNLOCKED". If the first person click on the macro button, it will change "UNLOCK" to "LOCK" in the text file. After everything is done, then change back to "UNLOCK".

The second person click on the macro button, it will not allow to continue as the text file is "LOCKED" now.


I tried to record Macro with text file, but it is not worked, and I am lost.

Appreciate if anyone can help on this. Thank you!
 
Hello Collinn,


Since the file is accessed by more than 1 person, it is advisable to change the Attributes of this file to READ ONLY. This way you do not need to write any separate code to keep track of Locking / Unlocking.


Every person who will open the file will get read only access to the file.


What you have not specified is whether the UserForm is being used to store data within the same file or not.


~VijaySharma
 
Hi VijaySharma,


User will fill in the form, which link to the "Admin" file which serve as a logbook as a record book. Admin file is updated by Macro to avoid manual entry. User is given 'write' access for "Admin" file to update the record.

The problem occurs when 2 persons open the files, and get access in the same time. Thank you.
 
Hi Shrivallabha,


The file is not in shared mode. To test for consistency, we tested to access with other users at the same time. Out of 100 attempts, 3 times are able to access together ;(
 
Hi, collinn!

Joining shrivallabha's sounding, wouldn't they be accessing to the "same" file but sent by mail, opening it from the attachment? Or were those Three Wise Men actually magicians?

Regards!
 
Hi Shrivallabha & SirJB7,


The file is parked at the company's G drive, user will open from there. Thank you.
 
This is strange. I've not had such an experience with the files we had kept in network drives. So it simply means I don't know a standard solution which will work.


1. First thing you can try is to check if there are some tmp files created and see if you can delete them.


2. Rebuild this workbook by copying pasting data into "fresh" workbook for the possibility that the current one might have got corrupted (partially) so it is acting weird.
 
Hi, collinn!

I don't know what does the files looks like, if it as simple .xls/.xlsx workbook with no links and no macros, if it uses external sources, if it's a .xls/.xslb/.xlsm with macros, so additionally to what shrivallabha wrote you could do this:

a) upload your (or a sample) workbook

b) repeat the same tests of opening from a hundred places with an empty, clean, new workbook.

Regards!
 
You must log in or register to reply here.
Back
Top