I have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
View 3 RepliesI've got a couple of user-defined functions that I coded in via one workbook (wkb A), but would like these functions to be accessible to any workbook (wkb B...Z). How is this done so that when I start a new blank wkb I can use these user-defined functions there as well?
View 9 Replies View RelatedAn Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it
View 1 Replies View RelatedI'm using Excel 2010. I have a workbook with only 1 worksheet in it and it will be sent to several people. I want that excel file to open in Full View when the end user opens it. Is this possible without any VBA codes?
View 2 Replies View RelatedI have a workbook that has a list of different depts. I need to design a way to enter a login of sorts to the excel and allow the user of that department to edit their entries only and lock the rest of the entries for the rest of the departments and do not show them in the result list after the user logs in. Once all department manager fills in his/her entries, the administrator is notified via email.
View 1 Replies View RelatedI have been tasked by the company I work for to come up with a simple to use interface that can be used by someone with little understanding of excel to set the autofilters to certain settings.
So here is what I have:
2 sheets (each with the same number of columns but different data which corresponds to good and no-good)
Each column has an autofilter but only two on each sheet need to be set.
One filter controls valve type (of which there are 10) and the other controls running condition (startup and normal)
Now my employer wants something that is basically idiot proof so that someone can simply open up the excel file, input the valve type and running condition, and be rewarded with only the corresponding data and charts. Now the filters are already in place, the charts already exist and the only thing left is the user interface.
I use the vba to open excel file. like workbooks.open filename:=abc.xlsm but I found that if the file is opened by other. Excel still open the file but it will not notify user that it is opened as read only.
Would you add more line to notify user if the file is opened by other?
I have a template excel file that is loaded with macros. When the file is opened, a userform opens and the user makes several different selections. Based on the selections, the excel file is populated with information and pictures. I want to make sure that the user does not accidentally save over the template file when he/she is finished with the document.
My original solution was: when the user opened the excel file, it would automatically save under a different name in a temporary location. This would stop the user from being able to overwrite the source file. The users were not happy because the excel file is very large and takes a long time to save.
on another method to stop the user from overwriting the source file? using code? or another method?
Is there a way to protect the conditional formatting of accessible cells in a protected sheet.
Detail: I have a protected excel sheet. However some of the cells are made accessible so others (my secretary) can fill them up. The accessible cells have conditional formatting which become nonfunctional during pasting from other sources if "special paste" is not used.
how to get informazion with VBA code about the name of the user who opened already a workbook on the network.
Usually if somebody wants to open a file that is opened by another user, office applications give informations that the file is open and about the name who opened that file.
Is it possible to get this information with VBA code, too?
I have a pretty simple sheet where users need to enter 8 cells in a single row. Issue I have is that they often ignore certain cells or enter data in incorrect format. I've toyed with data validation but that doesn't help if they simply ignore that cell.
What I'm wondering is, is it possible to create a user form that displays as soon as data is entered in first cell (Col B which they cannot avoid), where the user form then dictates the remaining cells that MUST be completed or the whole row will be emptied if cancelled.
Hoping someone would be able to help me with this. I have a sheet (example attached) and this sheet has a number of varying description types in the W coloumn (usually approx 10,000 rows). This field is manually input so there could be spelling mistakes and/or non standard descriptions.
What I would like, if possible, is a macro that would look at the D column and if this is 'GENERAL LEDGER', it would then look at the W column.
An input box would come up, and would list the different descriptions it found in column W, and number them. It would only list each different description once.
e.g.
1. Bank charges
2. Bank charge
3. Cash
4. Fund Custodian Fees
5. Fund Manager fee
6. Interest income cash account
7. Interest income cash acc
8. Miscellaneous expenses
9. Miscellaneous income
10. Other income
11. Sec lending comm
12. Sec lending commission income
13. Tax Reclaimable - Dividends
14. Withholding tax dividend
The user would then be able to type in the corresponding numbers, if possible seperated by a space, comma or semicolon and the macro would then run through the sheet and delete the entire row if D was GENERAL LEDGER and W was the selected description.
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
find attached zip file . if you open the file then you can understand the problem.
I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)
Kindly open attahced file i putted the note inside the file.
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
View 2 Replies View RelatedI use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
I have a workbook that is used to track overtime hours worked through the year. I've received some help from users on this forum, and others, to create the necessary macros and formulas to allow a supervisor to make use of the form; however, the workbook is also used to track those that wish to volunteer to work overtime each weekend. The sheet has a list of names for the employees on each shift, then a column to allow a "Y" to be placed under the date - designating them as a volunteer for any overtime available. I've found this spreadsheet (attached - from this site) that will allow individual users to access the workbook and have unique passwords for each. I don't completely understand how it works, but I feel it may be a start to getting what I want.
Essentially, once the user selects their name and inputs their password, I'd like the workbook to be opened and then allow them to only manipulate the row associated with their name. That row will change from week to week, so specific numbers will not be able to be used, but their name will be the same as the name used in the logon.
I feel like I'm asking for a lot here, so just some initial guidance on whether this type of thing is possible would be an acceptable answer.
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub
VBA code Identify which user has a particular file open. This file is on our shared drive and recently we've had people open the file and leave their desk. They haven't input an Excel username so frequently we cannot tell who has the file open to ask them to close it.
I'm trying to write a macro that will tell me if the file is open and if it's open, who has it open (using their computer login name).
I have written a sub to open a txt file, but I want to then save it as Excel, with a filename (a variable). The variable is public, and is set by other subs. I have added the standard "save as..." button to my custom toolbar to use for this and the following code (taken from previous thread)
Public Sub SaveFile(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
Application.EnableEvents = False
Application.Dialogs(xlDialogSaveAs).Show MyVariable, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Cancel = True
Application.EnableEvents = True
End If
End Sub
But it comes up with the original filename in the save as dialogue and txt file type. Any help would be great, this is used by inexperienced users and I don't want the original file saved as it's prevoius name and type
I Want make a code to open (OPEN File Dialog)
and it works good I can't change the Diroctory!
Sub OpenFile()
fileToOpen = Application. GetOpenFilename("Execl Files ( *.xls), *.xls")
ChDir "C:" ' Not work good
If fileToOpen <> False Then
Workbooks.Open Filename:=fileToOpen
Else
Exit Sub
End If
End Sub
check this code.
Does VBA support any type of "browse for file" interface? I'm thinking something similar to an inputbox, but instead it gives you the standard looking windows explorer window to find a file (think adding an attachment in outlook).
The function would return a string.
I am writing code that requires data from another workbook. Instead of hardcoding the directory and filename (which changes daily), how can I have the code prompt the user to do this for me?
I want to open the file with:
links updated
read-only
Also, is there a way to have an initial directory that appears so the user need not go through a dozen directories every time to get to the file?
I have code that prompts the user to open a specific excel file. However, with the following:
Filepath = "..."
name = Application.GetOpenFilename(FileFilter:="Excel Files *.xls* (*.xls*),",)
Workbooks.Open name
the default directory you start out in in the window that prompts the user to select a file is NOT ...
How can I properly assign which directory the user starts in so they need not go through 10 sub-directories everytime they run this code?
I want to do is prompt the user for an Excel file and then insert it as a new worksheet as sheet 2. Also, if possible I would like the imported file to insert to the right of sheet 1. Anyone know how to do this or at least know some parts of this problem.
View 9 Replies View RelatedI have a excel Product database and a Data Entry Userform. I can gte this to work fine but need to make some improvements to make it look professional.....here's what I am looking to do...:
1) When a user add a new product using the Data Entry form, I want the user to be able to add a hyperlink to the datasheet of that product and this hyperlink will then be saved in my database in column M of that product alongwith all other details in other columns. The DataSheet files are PDF files that are stored on the public drive. So I am hoping to use a browse function or similar to select the file to hyperlink to...
My current hyperlinks look like this which I have created manually directly into the column M (not through userform):
V:PublicCOMBData SheetsData SheetsXXXXX.pdf.............
We have several salespeople who maintain a workbook tracking their workflow. I want to compile the customer account number, account name and deal number for each rep to another workbook. A new sourcebook is started each week. This code is in the target book
'Option Explicit
Public sourcebook As Object 'the workflow workbook
Sub GETDATA()
Dim wsc As Double 'the number of pages in the workflow
Dim col As Long 'the column in this workbook for returning values
Dim IntEndRow As Double 'the end row in this book
Dim SrcEndRow As Double 'the end row in workflow
Dim findval 'the column workflow where the sourcedata is found
My code allows the user to select a workbook and then loops through each sheet in that book searching for the column which has the info I need, this changes on each sheet as the reps each deal with different types of customer as so take different details. I solved this by having column headers in the target book and searching for them in the sourcebook.
This code works when I step through it but falls over at the "Set sourcebook" line when I run it where it returns a breakpoint error.
how to alert and force second user to quit (i.e. even cannot open the file as "read-only) if the excel file is being opened by other?
View 3 Replies View RelatedI have an Excel VBA routine which opens another Excel file as a part of the routing. For example:
' Beginning of the code.
Workbooks.OpenText Filename:= Range("log_file").Value, Origin:=xlWindows
' Rest of the code.
The problem I run into is when the user does not have access (via Windows Active Directory) to the file located in the "log_file" range. They usually will get the old runtime error 1004. I can easily trap that error but err 1004 is used so many other times, I don't want to mistakenly trap it as something else. Does anyone know of a way to verify and/or check if the user has read/write access to a file (or folder) before opening it?
I want to open a user defined file name in excel and then activate it , to run a macro on it.
View 3 Replies View Related