I have a workbook where there are around 10 worksheets. All of them are build the same, but only different sheet name (country name)
What I would like to do is use the column A to check if no one did put a duplicate in a differnt sheet. Now I have a formula in each sheet, but it would be great if it would also look in the rest of the sheets if there is no duplicate.
I have created a userform that inputs employees names in to a sheet called employee list. It works well, now I want to take it a stage further and check to see if the employee being entered is already in the list and if so let the end user decide whether enter the new data or save the data already in the list.
Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Employee List").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtLast.Value
ActiveCell.Offset(0, 2) = TxtNum.Value
ActiveCell.Offset(0, 3) = cboCourse.Value
If optIntroduction = True Then
ActiveCell.Offset(0, 4).Value = "Operative"
ElseIf optIntermediate = True Then
ActiveCell.Offset(0, 4).Value = "Team Leader"
Else
ActiveCell.Offset(0, 4).Value = "Manager"
End If
Range("A1").Select
End Sub
I have 3 columns A,B and C and at the end of each column i have the row with total values of A, B and C. So I need to make sure that only one value is posted in one of the 3 column values and also if in the current row if the values are properly entered( that is only one of the 3 values is entered) then insert a blank line just before the Total row. I have this code to insert the new row which is working fine. but i need to validate before i could run the code. that is i need to make sure in the selected row, only one value is entered. Note: I want to validate only for the rows that has the cursor present in it.
View 6 Replies View RelatedI have a distribution list which has names in columns G, H & I (titles in row 2 & 3) data starting row 4. A persons name may appear in any of the 3 columns and may appear only once or many times in the same or multiple columns BUT should not happen in the same row, (rows are cost centres while the columns are types so a person can only get one type per cost centre).
What I need is a formula that will check that there are no duplicate names on a row in columns G:I.
If there were duplicates, is there a formula that would tell me where it is?
The formula could return a result in say G1 which I could format using Conditional Formatting, or would a routine be better for this?
The data could be moved down if more space is needed above the title rows or a separate sheet could be used as a check and query sheet.
I have a simple spread sheet, as shown below. Each line is on it's own row, and all the info on each line is in the first cell. (A1,A2,A3 etc....) What I require is a macro that will look at the "Code" line, take note of the code and if it finds a duplicate code number, mark the duplicate code with (1)....say up to (5).
In the case below, it would flag the second 07edr code and change it to 07edr(1). If there was a third 07edr code it would mark it 07edr(2) etc.
It needs to look at all the codes as it's possible there will be more then one set of duplicates.
Description = item 1
Code = 07edr
Quantity = 1
Description = item 3
Code = 08geo
Quantity = 2
Description = item 78
Code = 08cpm
Quantity = 1
Description = item 1
Code = 07edr
Quantity = 2
Description = item 39
Code = 16blf
Quantity = 1
I need to check if value entered in a textbox in a userform, linked to cell in the worksheet was not entered already in other textboxes in the same userform.
View 9 Replies View RelatedI am trying to improve my expense report template and need to check on the load if expense report number has been loaded correctly as well as if this report has been previously loaded. Expense report number format looks like this: AAA-BBBBBB-CC
Where:
AAA – Employee ID #
BBBBBB – End of the week date
CC – Weekly expense report number
For instance, 023-122008-01 means: Employee number 023, week ending date 12/20/2008, weekly expense report number 01. I would like to prevent/give warning of loading incorrect expense report number format, check for possible duplicates, and check if trying to load expense report belongs to the right person (by simply matching previously loaded in different cell of the same sheet employee ID and first three digits of just loaded expense report number. I think I know how to do all of these separate, but have no idea how to combine all three checks for one cell.
Obviously there is the Remove Duplicates option but I want something a bit different plus this option won't be available as the workbook is on complete lockdown.
There will always only be 214 rows to check in column B and the data cannot be sorted or it will mess a lot of things up!
Just wondering if there's some code that can check for duplicate entries because for the workbook to work, each row must have a different entry.
When the user selects this sheet a message box pops up telling them they can't have two or more entries the same but I feel this isn't enough as there's nothing actually stopping them doing this.
I currently have the code below, and it checks if there are duplicates of the entered value on all sheets. I would also like to prevent users from entering a value that is not within a certain range. How to add this to my code? I've tried to add another If, but it just ended up looping.
Maybe it would be good to make something like:
1. first check if value is within range (between 1000000 and 3999999). if not within range: msgbox and delete entry.
2. then check for duplicates
Here is the code I have so far:
[Code] ......
I need macro that will check for list of duplicates in sheet1 and place the duplicate data in sheet2.
I know how to check duplicates in only one column.But now my sheet having lot of columns.
I am trying to highlight duplicates across a workbook of 12 sheets. Essentially we are using it to track ID#s and I want to highlight the cell if this the value in cells A4:A100 matches that of the same sell range in the other sheets. I was able to come up with the below code, but even when I use in the "This Workbook" it only applies it within a sheet, and not across the multiple sheets.
VB: ...........
Formula: ..........
I have a code that close a csv workbook after use. But sometimes the book is not opened from the start of, and then I get an error trying to close it.
How can I easily determine if the workbook wbOBX is open or not?
Code:
If Not wbOBX Is Nothing Then
wbOBX.Close False
End If
I have to files and a macro which copies data from one file to the other. The code goes as follows:
Sub test()
Range("A2").Activate
ActiveCell.Copy
ChDir "D:"
Workbooks.Open Filename:="D: est2.xls"
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Everything is ok as long as the second file (test2) - the one data is copied to, is closed the moment the macro starts acting. The problem is: when test2.xls was opened before the macro was activated a window pops up saying that all unsaved data in test2.xls would be lost.
The best thing to do is to write a piece of code stating that : if the target file is closed then the macro should open it and do its stuff, but if the target file is already opened than the macro should simply switch to the already opened workbook without trying to reopen it. All I need is a piece of code regulating that
Y have files that are stored as .xla files because they are standalone applications that just contain the code.
Y need to test if one .xla file is open and if so close it, so i'am able to open another one. Otherwise the APP withevents from both APP get all mixed up.
I'am actually doing it from vb.net 2005 and i 'am using latebinding.
But the code would be similar y guess.
I have a small group of excel files that I've tried to code to update a master file. I have borrowed code snippets and adapted the rest (still trying to learn). My appologies to those I borrowed from! I would give you full credit if I could remember who did what. Anyway, The files are on a network share folder and everything works perfectly for me, but when one of the people who this was disigned for tries to update it always says "File already open, please try again later." This message is built in to the code.
All the files are in the same shared folder (to make it easier for me) along with the master file.
Here is the code:
Private Sub CommandButton1_Click()
Const strMaster As String = "PMT_Master.xls"
Dim wbMaster As Workbook
Dim srcrng As Range
Dim destrng As Range
How do you check to see if this is the only workbook open. Basically, I want something like:
If only workbook open
application.quit
else
activeworkbook.close
I just noticed that if you already have a workbook protected and then run code that will protect it, it will become unprotected! (I have a line of code to protect the workbook in the workbook_open event; incase the last user unprotected the book on the last use). Apparently it doesn't work like protected worksheets, where if the sheet is password protected and you have code that tries to reprotect it with a different password nothing will happen. I can not find a way to check if the workbook is protected (and if so skip, but if not then protect). Worksheets have "activesheet.protectcontents" to tell you True/False.
View 3 Replies View Relatedhow to check if a workbook is open or not, and then do something on the resualt. What I would like to know is, how can I make the program check if the file is open on another on the server where others can access it? I'm reading this back, not sure im explaining it right... If i have a macro that checks if a file is open, is it checking if its open on just my PC, or is it checking if its open else where?
This is the function Roy did for me: [code].........
I have some code setup to create hyperlinks to all the excel files relevant to the current project a user is working on - it details when each file was last modified so it's possible to see how the project is progressing - and whether or not they need to review any changes.
I could do with including a flag detailing if someone else currently has the file open (prior to them attempting to open the file - as they're more than likely to open as read only and end up saving under a new filename - which I don't want happening) - as many of the projects are shared (one user might be in Edinburgh, the other in London for Example). It would be handy to display the user's excel User Name (as I've persuaded everyone to update this). I've added messageboxes identifying Last modified by user x , dd/mm/yyyy hr:min to auto_open's in the past, but am experiencing brain-freeze today
Private Sub Workbook_Open()
Dim vFileName As String
Dim sLastSaveTime As String
Dim sLastAuthor As String
vFileName = ThisWorkbook.Name
Application.Volatile
sLastSaveTime = _
ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
Application.Volatile
sLastAuthor = _
ThisWorkbook.BuiltinDocumentProperties("Last Author")................
I have an array that opens a workbook containing close to 100 worksheets, and copies specific sheet names into there own individual workbooks. The problem I have now, is that I assume the worksheet exists in the workbook, but often times it does not Is there a way to add some sort of "catch" that will 1st verify the worksheet exists instead of my code crashing?
View 9 Replies View RelatedIm looking for a way to check all charts in a workbook, and resize them.
There are two charts per tab, and i would like the first one to sit on cell A1, and the second to start from a25.
So each chart about 25 rows, by 15 columns if possible.
I am trying to ensure that m work book keeps the same name even when I email it to others.
I have been told on this forum that it is possible for a workbook to check its own name once opened?
I have two different names that this file can be called 1st is the one that is in use all the time and the 2nd is a archive version where the name changes each month.
The file name I wish to keep the same for the no archived version is Toolbox and a predetermined number I have a workings sheet called workings and in a cell i have a formula =TEXT("Toolbox Application " & Cover!E10,) this will give me the file name that i need to check for example it could be Toolbox Application 00089 or Toolbox Application 00967.
I also have a cmd that archives the workbook and saves it as Toolbox and the Month it was for Toolbox for Jan for example
I also need this to open if it is an archive
if neither of the names match i need the workbook to close and a msgbox to tell user to rename the workbook correctly.
I have written a routine that automatically saves the file to a specified folder as the name of a cell from one of the worksheets. I am trying to use the following code to see if that filename already exists and stop the routine if it does. If I manually enter an existing filename the routine works well, however I cannot get it to recognise the automatically created filename, here is my .....
View 9 Replies View Relatedhow to design a piece of code that would allow me to check whether a workbook which is not currently opened and shall not be opened at all, contains a certain value. Here my developement which failed to prodeuce the desired result:
Sub test()
Dim wBook As Workbook
Set wBook = Workbooks(Filename:="D: arget.xls")
If wBook. Sheets("AAA"). Range("A1").Value = 123 Then
Range("C3").Value = "Good stuff"
End If
End Sub
Is there any way this can be done without opening the target file??
I am looking to check if its open, if it is, then copy the row, if not, then close it.
I am still new to vb, so learning as I go, but looked at many other posts, and there are so many ways i have seen to open one, I'm not sure which is the best, and none that I have seen show me if they check if its open.
When a user adds a new record to a simple tracking log, I want to enforce entry of the minimum required data. Whatever the new row number, cells in columns "B" and "D:G" must be completed. This is to serve as a gateway to accessing a related form. If all entries are not made, then no form.
Upon completing the entry into the last cell in the range (say G100), the form (in a different workbook) is to open. How do I check that none of the cells within the range (say "B100" and "D100:G100") in the new row are not empty?
I've experimented with the following code in, which opens the form file as needed after the data is entered into the cell, but I'm not sure how to check for empty cells within the range of the new row, prevent opening the form file if any of the cells are empty, and restrict this event trigger to just the range of cells on the new row.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").End(xlDown) <> "" Then
Workbooks.Open Filename:="X:file pathfilename.xls"
End If
End Sub
Code to check if a workbook is open, and close it, if it is.
View 4 Replies View RelatedI know this is my third thread, but I have made sure that I have trawled through other threads before posting. I have created some VBA to collect data from multiple workbooks and paste them into one workbook, Basically the copy and paste script runs according to how many tabs are in the summary workbook because each tab ( sheet) is essentially a condensed version of a workbook. Unfortunately some workbooks I am getting the info from don't have the right data that the copy and paste program is looking for or sometimes the document doesn't exist, is there anyway of telling my VBA to pass this tab if the document doesn't exist instead of throwing up a VBA debug error?
View 2 Replies View RelatedMy company used Excel with VBA as a sales tool. We recently updated to new application all together. The sales force, being familiar with Excel and most of them finding it much easier is still giving old pricing quotes based on some of the built in product forms and calculating tools from the Excel files. I want them to still be able to view the excel files they made in order to see old customer data and projects they built but I needed to disable all the features so they can’t save as just build another quote using the tool
I've locked down a copy of the tool how I want it locked down but now I need to figure out a way that anytime excel is opened by anyone in the domain; I need to check if certain criteria exists on that workbook (Sheet1. Name = “xxx” And Sheet21.Name = “zzz”) etc. f it meets the criteria I need to import my revised modules (module1 and module3) and form (frmsplash) over the existing copies of the modules and form. If not I need to allow the intended workbook to open as intended.
They employees initially got the document by downloading it under the same name, but have since saved the document as many other names.
I have 400 source files containing (among others) 8 sheets with daily results: "Fri 23", "Mon 26", "Tue 27", "Wed 28", "Thu 29", "Fri 30", "Sat 31 (if applicable)", "Mon 2".
Each sheet contains also:
State - D1
Role - D2
Staff ID - D3
Date - D4
Activity group name in column A (starting from row 8)
Activity type in column B (merged with C and D) (starting from row 8)
Activity time in columns E:GV (starting from row 8). Usually, there is none or only one value in whole range (e.g. E8:GV8). But sometimes there are two values.
Customer ID in row 6 (value appears only if time was reported in E:GV range)
CC Number in row 7 (value appears only if time was reported in E:GV range)
It's all about transferring values from all daily sheets in all files (.xls) sitting in folder C:WADFinal to one simple table (WAD_Consolidation_file.xls, sheet "Consolidated") consisted of 9 columns: Staff ID, Role, State, Date, Activity Group, Activity Type, Minutes, Customer ID, CC Number.