Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Test/Check If Shape Exists On Worksheet


I'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook)
When the users insert a new picture he has to set a name for each of them.
A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.

But I have a bug if a shape doesn't exist when I try to select it :

Sheets("fiche de controle").Select
'select the sheet with the pictures
ActiveSheet.Shapes(Item_old).Select
'select the shapes "Item_Old"
Item_Old is a variable corresponding to the picture name.

If the user made a typo, I have a bug.
Someone knows how to test if the shapes exist to display a meesage if not ?
Or somethig to avoid this kind of bug ?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Check If Shape Exists
how do i check if a shape exists?

i have a shape created by a macro.
sShape

so when i get rid of this shape i use

View Replies!   View Related
Check If A Worksheet Exists
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.......

View Replies!   View Related
Conditional Check To See If A Worksheet Exists
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

AddSheet:
Sheets.Add before:=Sheets("LOOKUPSHEET")
ActiveSheet.Name = "MONEYSHEET"

Data:

It also does not delete the sheet if it already exists, just edits the existing one.

View Replies!   View Related
Check If Value Exists In Range For Worksheet Filtering
I'm looking to use the value from a series of dropdowns (made via data validation lists).

Cell AM5 is a dropdown of named ranges made form a seperate sheet
Cell AO5 is a dropdown that uses '=INDIRECT(AM5) to lookup the values in the named range.

I need cell AO5's value to be used to filter rows in the current worksheet. The current problem is when i use the first dropdown in AM5 it still displays the last value, untill i use the dropdown to select a new one. This value typically will not be found and i do not want my code to execute in these cases.

View Replies!   View Related
Test If List Exists
is there a way to test if a list exists on a sheet? I'd like to do something like this:

View Replies!   View Related
Test If Sheet Exists ..
After going though the archives, I could not find how to test for an entry that does not have a sheet to be pasted into. I have a series of worksheets with the month-year for tab labels. The format for these tab labels is ("mmm-yy"). My code will place the new entry into the first available row of the sheet with the same month-year as the entry. I can enter any item with any date in any order and as long as there is a sheet with the same month-year, the entry will be placed into the correct sheet. Temporarily, when I need to add a new month, I click on a CommandButton that uses this code (located in a general module) to make the new sheet:

Public Sub AddNextSheet() ...

View Replies!   View Related
Sheet Exists Test Not Working
Got most of this code from the web and I can't get it to work. The part I added was the array and loop bit. As a test I specifically renamed one of the sheets in the file to be something NOT in the array, but it still goes through like it exists (I.e. shexist=True). What did I miss?

Dim wsname As String, shexist As Boolean
myarray = Array("Statement of Values", "Vehicle", "Driver Info.", "Revenues by Discipline", "Revenues Geographically", "Employee-Payroll Info. CDN & US", "U.S. Payroll", "Employee-Payroll Info. FOREIGN")
For i = 0 To WorksheetFunction.CountA(myarray) - 1
shexist = False
On Error Resume Next
wsname = myarray(i)
shexist = CBool(Len(ActiveWorkbook.Sheets.Item(wsname).Name))
On Error GoTo 0
If shexist = False Then
MsgBox "The worksheet '" & wsname & "' does not exist in this file or has been renamed." & _
vbCr & "Please check the file and try again.", vbExclamation, "Consolidate"
GoTo THEEND
End If
Next i

View Replies!   View Related
Test If Sheet Exists Syntax
I want to check if a worksheet exist, and if it does I want to skip the part of a macro that creates it, else I want to create it.

Here's what I have so far:

View Replies!   View Related
Test Whether Or Not An External File Exists?
Is there a way to test (from Excel 2002) whether a specified file (not necessarily an Excel file) exists on my computer? I could specify the complete path.

View Replies!   View Related
Test If Named Range Exists
Is there a way to check if a named range exists before I run a piece of code? I created a new file that has need for all the old file's ranges plus a couple more, and I want to use the same macro for both. So on the first file I just want to say, if these other named ranges are there, go ahead and do his other thing.

View Replies!   View Related
Check If Value Exists In An Array
Is there a way that I can check for the occurance of a specific value in a collection object like an Array or a Range in excel through VBA code?

For example I would like to check programatically if the value "orange" is present in an array by name Fruits(), where the array Fruits (3)=("mango", "banana", "apple").


View Replies!   View Related
Check If Sheet Exists
I have a userform that copies a sheet in the workbook, renames the sheet & creates a hyperlink to that sheet using the following code.

Private Sub cmdEnter_Click()
Application. ScreenUpdating = False
If ActiveCell.Column <> 1 Then
MsgBox "Go to column A to before inserting a row"
Exit Sub
End If

The problem I have is I can't figure out how to incorporate error checking if the sheet already exists.

What I would like is a message stating that the sheet exists and allow the user to make required changes on the userform.

View Replies!   View Related
Check To See If A File Exists
I have an excel spreadsheet used to populate word documents based on a template file that is loaded like this....

View Replies!   View Related
How To Check If Range Exists
Hi all, this might sound really easy but I have a Named Range in excel 2003.

Now I'd like to write a VBA code to check if this range exists before carrying on futher computations.

View Replies!   View Related
Formula: Look/Check If Value Exists
I got a pivot table that when selected will give me a value (actually its an ID number) I want in H3. I then got a list of values outside of the pivot table in column J. If H3 matched any of the values in column J, I need a “Warning”, if not “OK”. I have been using the formula =IF(H3=J3,"OK","WARNING") to match selected cells (H3 with J3) but cant get it to work to look down the column list. The OK or Warning is in cell F34 out of arms way!

View Replies!   View Related
Check If Workbook Exists Before SaveAs
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 Replies!   View Related
Check If An Entry Exists In A Woksheet
I have a huge database consisting of zipped file names which i created by importing the directory listing into excel. The zipped files contain two files each an mp3 and a correspondent cdg file (karaoke files).

I continue to add new disks (as I acquire them) to my collection. the trouble is that new disks have some songs already in my collection and I do not want to waste space in hard drive with duplicates. The existing data is arranged in a worksheet by columns as follows:

Discnumber | track number| artist | song name | path | complete file name

what I would like to do is to check the ** listing after import it to excel in the same format (i can do this part ) and then check it against the 10,000 plus entries that i have if the artist-song name combination already exists in the data. If so return the row in a separate worksheet or pivot table so that i can check it. this way I would only save the songs I do not have in my hard drive thus avoiding duplicates and saving precious space.

View Replies!   View Related
Check If Folder Exists, If Not Create It
I need to check if a folder exists and if not then create it. not sure how to go about this

View Replies!   View Related
FileDialog Check File Exists
I can use a FileDialog to have a file selected. But I still need to check if it really exists before continuing. How to do?

(I know there is a function FileExists but do not know how to use it with FileDialog if this is the way to do it)

View Replies!   View Related
Code To Check If ADODB Exists
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.

View Replies!   View Related
Check Workbook Exists Before Opening
I 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 Replies!   View Related
Check If Textbox Entry Exists
iam trying to get a messagebox to notify the user that the text he has entered into the userform textbox is already in use in a sheets column. this is what i have been trying to get to work

Private Sub txtID_Change()
If Sheet3. Range("a8:a1000") = "B" & txtID.Value Then
MsgBox "Text already in use, Please use different text"
End If
End Sub

View Replies!   View Related
Check If Holidays Exists Between 2 Dates
I have it like this

In column A is start of holidays
In column B is end of holidays
(one date written in A and one in B, and so on)

In C and D columns are start and end dates

Now I would like to check if start and and end dates of holidays, are between those dates in C and D

22/05/2006 24/05/2006

23/05/2006 24/05/2006

Check if first range of dates is between second one. Actually if holidays are between start and end date then check how many days are actually holidays in range of start and end dates

View Replies!   View Related
Check If Directory Exists Before Save
I am looking to see if a directory exists, and if it does, to save a file in it, otherwise create it, then save the file. My IsFileThere function works fine on files, but does not detect directories. The len(Dir(Filename)) statement always returns a 0, with the Dir statement always simply populating with "". It works fine on filenames - so can anyone tell me why it does not pick up directories?
Shouldn't the Dir function on a directory name return "."? I have excerpted some of my code to include here: Global Const StoredJobsSubdirectory = "Jobs"

Dim AppPath As String: AppPath = ActiveWorkbook.Path
'other code in here - activeworkbook changed, hence previous line
If IsFileThere(AppPath & "" & StoredJobsSubdirectory) Then
ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _
"" & NameString & ".xls"
Else
MkDir AppPath & "" & StoredJobsSubdirectory
ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _
"" & NameString & ".xls"
End If............................

View Replies!   View Related
Check If File Directory Exists In Code
I have some VBA which currently creates a directory when it produces an error. the code is as follows


Function create_year()

On Error Goto makenew
ChDir "I:Reports" & Year( Date)
Goto skipmakenew
makenew:
MkDir "I:Reports" & Year(Date)
skipmakenew:

End Function

What I would like to do is have an IF statement which checks if the directory exists.

View Replies!   View Related
How To Check Whether A Text Exists In A Group Of Cells
What I want is to find out whether a value in a node can be found from a group of other nodes. I've tried lupike this, but it doesn't seem to work properly.

=IF(ISNUMBER(SEARCH(C1;$A$2:$A$200));"WAS FOUND"; "NOT FOUND")

I guess one solution could have been using concatenate function, but why on earth can't I use the function like this: concatenate(A1:A5)? Why do I have to put each term individually?

View Replies!   View Related
Check If Named Range Exists Before Delete
I'm trying to check if a named range "ActiveCells" exists before deleting it in VBA but can't seem to get the syntax right, I have tried: If Range("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete

AND

If ActiveWorkbook.Names("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete

Can anyone point me in the right direction? I have tried searching but I can only find threads about checking for named objects or about using the toolbars to add/delete named ranges.

View Replies!   View Related
Conditional Formatting Look At The Cell Contents And Check If The Value Exists In A Range
I am trying to set up a conditional formatting which will look at the cell contents and check if the value exists in a range.

The range to compare will be over multiple columns and multiple rows.



View Replies!   View Related
Add Shape (Arrow) To Worksheet
This one has me baffled: I recorded a macro to add a shape to a worksheet, very simple. When I try and run it it comes up with a "The specified value is out of range" error. I've searched the net and it seems to come up where the file has been converted from an earlier version of excel (which this file has) but I haven't been able to find a work around for it. The relevant code is

With ActiveSheet
.Shapes.AddShape(msoShapeLeftArrow, 205.5, 312#, 144#, 23.25).Select
End With

View Replies!   View Related
Hide & Show Shape On Worksheet
I've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?

Sub DoIt()
Application. ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle 1to show while code is running
Sheets("RST").Select
Sheets("RST Pivot").Select
End Sub

Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?

View Replies!   View Related
Test For Grouping On Worksheet
I don't want a macro to run if grouping is already present on a particular worksheet.

View Replies!   View Related
Determine Position Of Shape/Button On Worksheet
I have written a small piece of code that handles clicks on buttons on an excel spread sheet. When a button is pressed, i need to know the row number where the button resides in.

I have searched and read about this issue on several forums, and they all indicate that i need to use something like

application.caller.topleftcell.row

to get the row number and

application.caller.topleftcell.column

to get the column number.

However, when i use the row variant, it ALWAYS returns row number 1. When i use the column variant, it returns the column number it is actually in.

i have added the full code that is creating the buttons below, and also the part that displayes the rownumber that is incorrect.

Private Sub Workbook_Open()
Dim name
Dim time As Integer
time = InputBox("Typ the hour you are checking" & vbCrLf & "eg: 7, 10, 13, 14, 15, 16, 17, 18, 19", "Which Check")

View Replies!   View Related
Determining If A Worksheet Exists
I try to do the following I get an error 'Subscript out of range'

View Replies!   View Related
Delete Worksheet If It Exists
Does anyone have SIMPLE code for this that can be run in a normal Sub. My worksheet will always be named "Cleaned". I have seen some posts regarding Functions and other things that seem more complicated than necessary.

View Replies!   View Related
Change/Modify/Add Text In Shape On Non-Active Worksheet
I have a button on say, sheet1 with text that I want to change after a certain action takes place; however, I was trying to change this text without switching sheets. (switching sheets isn't a big deal, I'm mainly trying to do it my way for the practice). I'm able to select the button on the other sheet without actually switching sheets, but when it comes to changing it's text I get an error. Here is the code I tried:

Sub macro1 ()
Worksheets("sheet2").Select
Worksheets("sheet1").Shapes("Button 2").Select
Selection.Characters.Text = "Done"
End Sub

Problem with this is it gives cell A1 on sheet2 the "done" text. I also tried this...but it gives the error.

Sub macro1 ()
Worksheets("sheet2").Select
Worksheets("sheet1").Shapes("Button 1").Select
With Worksheets("sheet1").Shapes("Button 1")
.Characters.Text = "Done"
End With
End Sub

View Replies!   View Related
Delete A Worksheet If Exists Or Present
How do I delete a worksheet but ONLY if it exists, if it doesn't exist, ignore the command. I'm afraid to mess up the other code in the macro/sub, so here it is...

View Replies!   View Related
Copy Rows To Worksheet IF Exists
I have a spreadsheet with columns set up like this: RowName, Date, Value1, Value2, Value3 etc. The date is in format YYYYMMDD. What I am trying to do follows, in some sort of pseduo fashion.

View Replies!   View Related
Open Workbook Only If Worksheet Exists.
I have a macro that 'opens all' workbooks in the specified folder. I've copied it over, but need to only open all the wbk's if a specified worksheet exists w/in the wbk..I know there is a dim ws statement that can be used, but how do i use it w/ the current macro?

Option Explicit
Sub recTestOpenAll()
Dim x As Integer
Dim WB As String
Dim wbk As Workbook
For x = 1 To 100
WB = "G:Rule Test FilesREC " & x & ".xls"
On Error Resume Next
Set wbk = Workbooks.Open(Filename:=WB)
On Error Goto 0
If Not wbk Is Nothing Then
End If
Next
End Sub

View Replies!   View Related
Macro To Work Between My TEST.xls File And Another Worksheet
Below is the macro to work between my TEST.xls file and another worksheet which is opened within the same workbook with the TEST file. If there are more than 2 files, the code ActiveWindow.ActivateNext won't work.

The number of files received from my suppliers varies depending on how busy the volume is. It can be as many as 20 files a day.

ActiveWindow.ActivateNext
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[5]

View Replies!   View Related
Change Shape Text Without Selecting Shape
when i run the below code i get an error 438 'object doesnt support this property or method'

View Replies!   View Related
Delete Row If Value In Cell Exists In Another Worksheet Column
I have working data on list sheet and added data on compiled sheet.

I need to match duplicates and delete these rows from compiled sheet.

I've tried coping to the list sheet and use "Remove Duplicates", but it rids me of the data that has been processed and has fills applied.

I'm looking for a macor that can do this work for me without losing data on list sheet.

This data reaches into the thousands and I'm looking to run this off the CASE column.

View Replies!   View Related
Displaying Colum Where Data Exists In A Different Worksheet.
how do I automatically add beside the name the colum title where it previously existed. see the attached.

View Replies!   View Related
Count IF Look At A Column C In A Worksheet A And Return How Many Time The Word 'on Test' Occurs
I am using a count if to look at a column C in a worksheet a and return how many time the word 'on test' occurs. This is then returned to a table in another worksheet. I have 5 worksheets in total with the same columns, how can I do multiple countif - so that it will count all the 'on test' in column C for all 5 sheets.

View Replies!   View Related
Activate Worksheet: The Retro Is Also Suppose To Test Cell H12 To See If It Is Blank Before Running The Msgbox
I do not know if I have this written correctly, I would like to have the sub - Retro run whenever some one opens this worksheet - "FORM". The retro is also suppose to test cell H12 to see if it is blank before running the msgbox.

View Replies!   View Related
Using ChDrive And ChDir: Check If "PPG" Exists In "C:MEASURE-6000"
I can not figure out why the ChDir command doesn't change to "C:" as programmed, instead it defaults to (I'm guessing last active folder) on my "D" drive (Which is "D:Test")? My goal is to check if "PPG" exists in "C:MEASURE-6000", if not use "C:" as a default.

View Replies!   View Related
Add A New Or Check For A Worksheet
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?

View Replies!   View Related
Check Value Of Checkbox On Worksheet
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.

View Replies!   View Related
Check For Same Data In Another Worksheet
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.

View Replies!   View Related
Lookup Function: Compare Every Part In The All Parts Worksheet To See If The Part Number Exists On The Active Parts Sheet
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

View Replies!   View Related
Code To Check For Presence Of Worksheet
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........................................

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved