An 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 RepliesI 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 Replies View RelatedI 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.)
I 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?
I have a simple spreadsheet which requires a date (mm/dd/yy) in Column A. Columns B-F use formulas to break this date down into the month, quarter, year, etc. Users frequently need to add new rows at the top of the sheet just below the column headers.
To be sure the formulas in Columns B-F are included when adding new rows, I put together a simple macro which copies the top ten rows, inserts them at the top of the sheet, and then clears existing data from the new rows (keeping formulas intact).
I put a button on the sheet to run this macro. However, sometimes users insert new rows without using the macro. This results in no data in Columns B-F for these new rows because the formulas weren’t copied.
How can I prevent this from happening? If I protect the sheet and prevent users from inserting new rows, the macro won’t run. Is there something I can add to the macro that will allow it to run properly while the sheet is protected? Below is the macro code.
Private Sub CommandButton1_Click()
Rows("3:12").Select
Selection.Copy
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3:A12").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("G3:K12").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A12").Select
End Sub
I have tables on a spreadsheet and userforms created. Is there anyway to prevent users from just typing on the worksheet so they will have to use the userform?
View 4 Replies View RelatedHow can I prevent a user from opening another workbook in an Excel session "from the outside"?
I'm fairly new to VB. I'm developing (in VB 2003) a simple but SECURE Excel environment which will allow a user to update a hidden Master_Records workbook. I need to keep the user's Excel session secure...for example, I've disabled all Excel Toolbars and Command Buttons, effectively preventing the user from doing anything except filling in some cell values and clicking on a few custom buttons in the worksheet. But how can I prevent the user from opening another workbook into the active session from his desktop and introducing some malicious code into the session via that route?
One respondent in another forum (the only one, in fact) suggested that I look into "instantiating workbook level events" so that I can detect when other workbooks are open. I'm not sure what that means, is there someone here who could give me some guidance into that solution?
I have an Excel sheet that displays 50 rows and 20 columns when it is opened the first time. These rows and columns are the only ones that I want the user to be able to see. The user shouldn't be able to navigate the sheet by using the scroll bars or the wheel on the mouse. I have of course already removed the scroll bars but I don't know how to handle the scroll wheel problem.
View 2 Replies View RelatedI am using following code to effect a change which works fine:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Closed Flts").Unprotect "abcd"
Sheets("Faults").Unprotect "abcd"
[Code].....
But when the sheet is unprotected to effect this change the formula in cells ( row I and L) are visible to user and can delete them. Or they can even delete the cell itself or modify formula.
The formule in Row I is
Code:
=IF(A12="","",IF(G12=1,A12,IF(G12=2,A12,IF(G12=3,A12+1,IF(G12=4,A12+5,IF(G12=5,A12+28,"priority?"))))))
And in Row L is
Code:
=IF(A13="","",IF(I13
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
Is there a way to prevent a user from clicking on the days in the Calendar Control 8.0? I am trying to place 12 calendar controls on a worksheet for reference only. I am using a linked cell to reflect what date the Calendar is to display.
View 2 Replies View RelatedI want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.
View 9 Replies View Relatedwhat I want. It relies on an input box asking the user to enter a part code, which when found creates a duplicate copy. However, if the user enters a part that doesn't exist I get a run-time error. Can anybody point me in the right direction so when a user enters an incorrect part they get a message telling them it doesn't exist.
Private Sub SortTest_Click()
Dim s As String
Dim r As Excel. Range
Range("A2").Activate
s = InputBox("Enter the number you wish to find")
If StrPtr(s) = 0 Then
MsgBox "You must enter an existing part number!"
Else
Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Copy
Sheets("APL").Cells(r.Row, "A").Insert Shift:=xlDown
Application.CutCopyMode = False
Application.Goto Sheets("APL").Cells(r.Row, "H")
Selection.Offset(-1, -5).ClearContents
Selection.Offset(-1, 0).Select
End If
End Sub
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 feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.
View 5 Replies View Relatedfind 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.
I have a spreadsheet on an intranet drive, and many people need to see it and enter information. Sometimes, we have the file opened on a computer, and the user walks away, and their station is locked. That prevents anyone from updating or using the file! locate a macro that will count how long the file is inactive, and then quit?
I'm not at all comfortable coding in VBA, and this sort of thing is out of my range!
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 Related