I want Excel VBA to check for named worksheets. Based on the return, I will have VBA either (1) delete the named worksheets and replace them or (2) refresh the pivot tables on the named worksheet. how to make VBA check for the presence of the named worksheets. My efforts are below and comments at the end of the Sub detail what I want to have happen.
Sub EnterProgram()
Dim Current_P As String, New_P As String
Current_P = Range("data!C2")
If Current_P = "" Then
New_P = InputBox("Which program?")
Do While New_P = ""
Prog = InputBox("Try again... Which program?")
Loop
Range("data!C2") = New_P
ElseIf Current_P = New_P Then
If MsgBox("Use the current program (yes or no)?", vbYesNo) = vbYes Then
Range("data!C2") = New_P
End If........................................
I have 3 columns, C1, C2, C3. I have to return a value for the following conditions
1. Check for the presence of a particular text in the C 1 (duplicate values can be present) 2. Check for the maximum value in C2, if the second for the particular text from the first column (duplicate values can be present) 3. If the text is found, I need to return the Maximum value from column 3. Else I need to return 0.
My formula has been returning zero despite the fact that I have a larger valid value in column C3.
The formula(s) I have been running is 1. IF((AND(C1 = "E", Max(C2))), (C3),0) 2. IF((AND(C1 = "E", Max(C2))),MAX (C3),0) (In case of duplicated in C3) 3. IF((AND(C1 = "E", Max(C2))),MAXA(C3),0)
If I have a named range ("colors") which contains a bunch of different colors, and in a separate cell, I want to test whether a particular color is present in that range, how would I do that? For example:
I want to create some code that checks all the cells in a row to find if any cell has a background color and if it finds any colored cell, to then set the first column of that row to that color.
I am trying to apply a conditional format to an entire row based on the presence of any value in the first cell of that row to format to a certain color. If there isn't something present in the first cell, I don't want it to change.
Everything I keep trying ends up highlighting the entire workbook.
I would like to apply the rule to the entire sheet but only have it highlight the rows which have a value/text in the first cell of each row.
I am assuming I should use the formulas selection for conditional formatting but I'm unsure of what formula would apply to this type of request.
I have a userform with two check boxes and four textbox... and i have a command button... i want that when i check checkbox1, the value in textbox1 and textbox2 will appear on cell A1 and A2... and when i check checkbox2, the value in textbox3 and textbox4 will appear in cell A1 and cell A2.
I would like a macro that adds a worksheet with the name say XML_JB to a workbook. I have ran the record button as shown in the code below but when I re-run the Macro the line tries to add the worksheet in Sheets("Sheet5").Select and I get a debug error. I realise the issue is around the fact that the count needs to be set some how as a variable and not set as Sheet 5 as the macro can't find sheet 5. The Macro also needs to check if the worksheet “XML_JB” is there and if so throw up a message like XML_JB already exists Do you want to continue? And if yes skip in adding of the worksheet and run my first macro. But as this is my second macro I don’t know how to go about it?
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 use a userform to make sales data entries into a database. The userform contains textboxes for:
Invoice No.Invoice DateGrossVatNet The Invoice No. is the first entry box on the userform and it is important that users enter the correct format (which is alphanumeric).
It is also important that a checking is carried out to ensure that the Invoice Number being entered has not already been used.
I use the following code to ensure that the numbers are entered in the desired format:
Code: Private Sub txtInv_Exit(ByVal Cancel As MSForms.ReturnBoolean) If txtInv.Value = vbNullString Then Exit Sub If (Not UCase(Me.txtInv.Value) Like "ST###") And (Not UCase(Me.txtInv.Value) Like "ST####") Then MsgBox "Non Valid Invoice Number.†Cancel = True End If End Sub
And this code is pasted lower down the VB editor window. And it seems to be working fine!
For the checking to ensure that the invoice number being typed into the textbox has not already been used, I use the following two codes combined together:
a) to check for invoice No.:
Code: check for Invoice Number
If Trim(Me.txtInv.Value) = "" Then Me.txtInv.SetFocus MsgBox "Please Enter Invoice No." Exit Sub End If
b) And to it I add the following additional code for CHECKING that the no. does not already exist in the database (the invoice nos are located in Column A of the database):
Code: Dim x X= Application.Match(Me.txtInv.Value, Columns(1),0) If Not IsError(x) Then MsgBox Me.txtInv.Value & "Invoice Number Is Already Used" Me.txtInv.SetFocus Cancel = True Exit Sub End If
My problem with the second set of codes in (a) & (b) (for the Checking of already existing No.) is that the code allows the user to key in the Invoice No. and go through filling in the other textboxes. It is only after clicking the ‘Add’ button that a msgbox comes up to indicate that the ‘invoice no. is already used’.
I need a tweaking of the codes so that the moment an invoice no. that has already been used is typed into the textbox and the TAB button pressed, a msgbox will show up at that stage to indicate that the ‘Invoice no. is already used’. This will reduce time and avoid filling in the other textboxes and clicking the ‘Add’ button before the checking is done.
I need a vba code to run a macro when a specific word entered in a cell. i.e. when a word "Duplicate" entered in a cell O2, the macro run automatically.
I have some code that will Kill a DB if it already exists, but I want to check if it exists and warn the user before this happens.
I am not that familiar with ADO, so I was fumbling through the Help topics trying to learn about ADO type names, etc. b/c i thought I could use something like:
If TypeName(MyDB) = "ADODB" Then . . .
but even if that ran, the argument in parentheses would be a string and not the actual DB object, so I am at a loss.
I have a macro that creates a spreadsheet on a weekly basis and have been using it for quite sometime. Due to the large number of spreadsheets I would like to incorporate some code that would create a folder every month. I would assume that the code would need to include a check routine to see if a folder for that month exsists.
i want to improve some of my application by testing if a folder is accessible to the user... i've got some code to check if a file is already is use which is useful... but before that we have folders in work that are only accessible to certain users for reporting and if the person doesn't have access to it within a macro it Errors out... i'd like some kind of macro to display a message box like windows does... Directory is not accessible, Access Violation... it has to check directly for the Accessible part and not just an error...
I want to create a user form in excel that auto calcs the discount depending on customer type and no. of books purchased. There are two types of customer’s one individual and the other schools, library etc. The discounts are below
I have created a form with option and text boxes to capture data but am really struggling with this as in my head I understand a set calculation will need to take place once relevant conditions are met but am really struggling to put it into code (yes I am new to this).
i would like to know whether a worksheet exists or not... say, the worksheet name is sheet1, i need to check whether the worksheet exists... the point is, i need to delete the worksheet if it exists and execute a code... if the worksheet doesn't exist also, i need to execute the code... so im getting a little confused with this... i need something like this...
if sheet1_exists then delete the sheet end if my_code.......
A workbook contains two worksheets. The first sheet is a membership list (Mem06) containing adresses and telephone numbers. The second sheet is a list of activity groups (Groups) with members details who are attending. There are 28 activity groups whose venues are scattered over 20 miles, each has a Leader. It is difficult for the Leader to know if those attending are paid up members, we know that some are lapsed. The solution is to get those members attending to complete a form with their name and 1st line of address. The membership secretary enters the names in the Group sheet (1st line of address only required when people have the same First and Surname).
We would like a formula to check this entry against the list in Mem06. If the entry corresponds the Telephone number for that entry would be returned to the Group sheet. Col A in both sheets has a formula to Concatenate First Name and Surname. The Telephone number in Mem06 is at Col I. The required formula would be in the Group sheet in the Telephone number col.
I have a problem to check the value of a checkbox when I write the code in Module1. I have several worksheets, and in each worksheet there is a CheckBox1. I want the macro (in Module1) to perform a given code when the CheckBox1.value = true and visa versa.
The code I'm using is as follows:
Option Explicit
Global ws As Worksheet
Application. ScreenUpdating = False
For Each ws In Worksheets
If CheckBox1.Value = True Then 'Do code1 Else 'Do code2 End If Next ws
Application.ScreenUpdating = True
End Sub
When the Macro is run, an error msg is showing "RunTime error '91'.
Does anyone know how to make this macro function? I've searched the forum, but couldn't make any of the codes suggested to work.
I have a file I created which works fine for me, on my machine at least but there are two issues I'm trying to sort out.
The first issue is I need to share the file with others who may not have the same access rights. I have something loading in the Workbook_Open event which I want to bypass for users who do not have access to a specific folder. So I was trying to check their access using Dir(path) = "" at the top level folder to see if any files are present (which there are). My assumption was that if it finds files it's safe to assume they have permission and it's ok to continue, otherwise they don't in which case I want abort the rest of the code. This seems to work fine for me but I tried testing with someone I know does not have access and it gave her a compile error.
Part of the code in macro utilizes FileSystemObject which seems to be the line where the code bombs on my coworkers machine. The FSO seems related to appears related to the Microsoft Scripting Runtime reference, but it's not enabled on my machine or any of the others. I vaguely remember having to do something on my machine awhile back for the life of me don't remember what it was.
The second issue is an MS Forms error: "Could not load some objects because they are not available on this machine." appears when opening the file on three of the machines I tested but it does not appear on my machine. I've tried looking at the references and could not find any differences between the machines. I pored through various postings online and it appears it might be worth re-registering the DLL/OCX files but we need admin rights to do that so I will need to open a request with our support team.
I'm trying to write some code that will check if specific files in a folder are present before continuing code.
I've looked on-line and found various code that checks if a single file is present whereas my requirement is slightly more demanding.
Some files might be present and others not. The code must check ALL necessary files are there then run main code. If even any single file from say 5 files is missing, code must exit.
I was thinking of using an array to store all files names and then use a "For Each XX In XX" to loop through the array and check each file is present?
This is what I have so far:
Sub XX() Dim strPath As String, LinesDelete As String, Applepnfile As String, MpnStock As String Dim myArray() As Variant, myElement As Variant Dim myArray() As Variant, myElement As Variant
I hope some one can help me to create code to do the following :-
1) if have last purchase date , but no last issue date = New Product. 2) if no purchase date , also no last issue date = None stock issue. 3) if have last purchase date , also have last issue date = cal the date between and put the month value .
For example :-
if last purchase date = Oct 07 if last monment date = Nov 07 then the stock duration = 1
Is there any way to use formula or VBA to highlight cells which contain the correct spelling?
For example i have columns A filled with words in each cell but need to highlight which ones have the correct spelling, as there are more with incorrect so i need to visually see the correct spelling.
I have a macro that is intdended to run after the user has selected a cell in column A:A (any position except A1)
Once the user has selected their desired cell, they press a button and the macro runs.
Occasionally a user will press the button without selecting a cell in the proper column.
I would like a message box to pop up to tell the user "Please select an account in Column A" and bump the cursor (active cell) to A2 so at least it's in the right column to help get them started.
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?
I am writing a macro which first needs to look and see if a certain worksheet exists. If it doesn't then I want it to create it but if it does I first want it to delete the sheet then create a blank one with the same name. At trhe moment I have this but it is scrappy and doesn't work very well:
On Error Goto AddSheet If Sheets("MONEYSHEET").Activate Then Goto Data
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
The following code displays the message box and loads a userform if the Active cell offset 1, -1 when within G column equals "LAST ROW".
[Code] .....
How can I change it so instead it performs those same actions (displaying the message box, and loads the userform) if the number of rows between the last data entry in G and the last data entry in F is equal to 0?
