Free Microsoft Excel 2013 Quick Reference

Validation error message when data does not yet exist.

Hi,

Can you help me? I'm trying to put an error message on the look up field when I enter a certain employee and if data or employee number is not yet on the database. I'm working with validation and with this formula =if(c4<>range,1,0) and it does not work.
Hope you can help me with new formulat that could work.

Thanks in advance!


Post your answer or comment

comments powered by Disqus
I will attach a copy of a chart I use for flow testing fire pumps. The data series for "Previous" and "MFG" work fine but the data for "Current Test Data" has the X and Y value series backwards. When I try to edit them to reverse this I get the error message in the subject line, yet the data seems correct.

I'm sure I've run into this and solved it before but it's really got me baffled right now.Flow Test Example.xlsx

My macro (written in Excel 2003) opens a text file from the C: drive.

eg - Workbooks.OpenText FileName:=FilePath + FileName, etc .....

I'd like to add code to this macro to trap for an error if the file does not exist and then display a message to the user to say that the text file needs to be located in this folder.
Hope someone is able to help
Thanks in anticipation.

I converted to EXCEL 2002 and attempting to do file transfer from AS/400
machine. On my previous EXCEL 2000 version, I could read over a maximum of
50,000 records. With EXCEL 2002, I get a message" The data will not fit in
the worksheet" and stores exactly 16,351 records. What do I do to read the
maximum number of records ?

Created Pivot table/chart, password-protected and put onto website (ie NOt
saved file as Web Page). Click on link, get warning message concerning macros
and prompted for password. If attempt to change data button by rightclicking
on it and selecting format - get error message 'Pivot Table not valid'. Note,
accessing the file directly (ie not via the web) works perfectly Ok.

PS Also get visual basic error message ' Run time error 1004 :Unable to set
the default property of the Pivotitem class'

VB code

Sub All_Years()
CP = "(All)"
pf = "Year First Seen"
Call DeleteOldItemsWB 'this runs OK'
Call Hosp(CP, pf) 'this stops at line 3'
Call Tumour(CP, pf)
End Sub

Public Sub Hosp(X, Y)
Sheets("Hospital Chart").Select
ActiveChart.PivotLayout.PivotFields(Y).CurrentPage = X ' line 3'
Sheets("Home").Select
End Sub

Created Pivot table/chart, password-protected and put onto website (ie NOt
saved file as Web Page). Click on link, get warning message concerning macros
and prompted for password. If attempt to change data button by rightclicking
on it and selecting format - get error message 'Pivot Table not valid'. Note,
accessing the file directly (ie not via the web) works perfectly Ok.

PS Also get visual basic error message ' Run time error 1004 :Unable to set
the default property of the Pivotitem class'

VB code

Sub All_Years()
CP = "(All)"
pf = "Year First Seen"
Call DeleteOldItemsWB 'this runs OK'
Call Hosp(CP, pf) 'this stops at line 3'
Call Tumour(CP, pf)
End Sub

Public Sub Hosp(X, Y)
Sheets("Hospital Chart").Select
ActiveChart.PivotLayout.PivotFields(Y).CurrentPage = X ' line 3'
Sheets("Home").Select
End Sub

OK, mr mod, lets try again.

Hi, I'm a newbie so excuse me if you are nice or flame me if you are not, but I need to use an excel book (written in 2003v ) on my vista machine running office 2007. I know little about the settings etc but having amended the macro's it has been working for months.

Now it comes up with the "reference is not valid" error message and all I can do is wait for autosave and then crash out of it via task manager. This does not happen straight away but seems to be when I click on a free format notes section.

I saw mention of this message elsewhere on this forum but there was talk of vsb etc that means little to me.

If anyone can help I would appreciate it.

I have tried to use IsError and On Error in VBA to trap the error
message "Remote Data not accessible" when a DDE program is not active,
but I keep getting the dialog box. I want to clear the active cell if
the program is not running. Any ideas?

---
Message posted from http://www.ExcelForum.com/

When I opened my saved Excel file, I've received the error message "File
format is not valid". I've tried to open the file with another format, but no
luck. How can I fix this error?

Hello all,

I have MS Office 2010... within the macro, I create pivot tables and charts and everything works as desired... the problem is when I try to do the same on MS Office 2007 and here is the part of the code where I am stuck (.ShowAllFieldButtons =):

	VB:
	
ActiveChart.ChartArea.Select 
ActiveChart.ShowAllFieldButtons = False 
ActiveChart.SetElement (msoElementChartTitleAboveChart) 'Add Chart Title
ActiveChart.ChartTitle.Select 
Selection.Caption = "Risk Observations - Open vs Close" 'Add Chart Name

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
IN MS Office 2010, when you create charts from the pivot table, buttons on the charts are there by default but that same feature is not available in MS Office 2007 and that's why I am getting Compile Error: Method or data member not found
I have tried to add On Error Resume Next, but that doesn't work

Has anyone experienced anything like it/ does anyone know how to fix it?

Hi Ozgrid Community,

I've searched around and found numerous solutions to similar problems and have tried working them into my code, but it still isn't cooperating! In short, the code works perfectly for me, but there is a compile error ("Method or Data Member Not found") when I open the workbook.

Also, I have many combo boxes on my "Home" sheet and they all get executed when I open the workbook and I am unsure how to prevent that. The combo boxes are simply for using Excel's Autofilter functionality on the 12 columns I have on a different sheet, so when the combo boxes are executed, they reset the filters on the other sheet, causing Excel to flip between the two sheets for 10 seconds on start up. The CTD, APPR and REO are checkboxes that the users can select, unless 'Show All' is selected, in which case they are disabled. The error comes up on the first line of the checkboxes: "Me.CTD.Value = True".

Any suggestions will really be appreciated and being my first post, please let me know if I am breaking any laws and I will be sure to change anything in my post as you please.

The code is very simple:


	VB:
	
 ComboBox11_Click() 
    With Sheets("Home") 
        If .ComboBox8.Value  "Show All" Then 
            If .ComboBox11.Value = "Show All" Then 
                Me.CTD.Value = True 
                Me.CTD.Enabled = False 
                Me.APPR.Value = True 
                Me.APPR.Enabled = False 
                Me.REO.Value = True 
                Me.REO.Enabled = False 
            ElseIf .ComboBox11.Value = "Does Not Contain" Then 
                Me.CTD.Value = True 
                Me.CTD.Enabled = True 
                Me.APPR.Value = False 
                Me.APPR.Enabled = True 
                Me.REO.Value = False 
                Me.REO.Enabled = True 
            ElseIf .ComboBox11.Value = "Contains" Then 
                Me.CTD.Value = False 
                Me.CTD.Enabled = True 
                Me.APPR.Value = True 
                Me.APPR.Enabled = True 
                Me.REO.Value = False 
                Me.REO.Enabled = True 
            End If 
        End If 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you in advance,
Ghiva.

I have recently finished working on a monthly report to count our sales but have now been asked to make a new spreadsheet to measure the trends.

In short i want to create a worksheet that will add up these figures, etc.

So far i have only 3 reports - to March - called,

January 07
February 07
March 07

So my initial calculation begins as,

='[January 07.xls]PPC Stats'!$C$11+'[Febuary 07.xls]PPC Stats'!$C$11+'[March 07.xls]PPC Stats'!$C$11 This works great but when i add April - i get an error,

='[January 07.xls]PPC Stats'!$C$11+'[Febuary 07.xls]PPC Stats'!$C$11+'[March 07.xls]PPC Stats'!$C$11+'[April 07.xls]PPC Stats'!$C$11 Now i know that April 07 does not yet exist but it will do soon & i don't want to have to keep changing my spreadsheet that makes these calculations.

I need it to add data from all speadsheets even though some may not have yet been created,

January 07
February 07
March 07
April 07
May 07
June 07
July 07
August 07
September 07
October 07
November 07
December 07

Is there a way i can do this without getting errors?

Any help greatly appreciated,

Thanks

I apologise in advance for the length of this question!

I have 4 workbooks containing data that is linked to an external database using Microsoft Query. The data in each book is then filtered using functions to produce relevant information which are then displayed in embedded charts. The data retrieved from the database is not a massive amount and is set to update automatically

The idea is to display these excel charts in PowerPoint. I downloaded an add-in off the net which would update the chart information automatically after each loop of the presentation; the add-in does work.

This all works very effectively, but for ONE really significant problem, which has got my brain in bits.

Intermittently I get an error message stating: "Excel does not have enough resource to complete the tasks, please close other applications."
The one of the charts on the presentation looses its data and looking at the Excel chart, it has also gone.

I have reduced the resource on the machine to a minimum, I have changed the refresh rate in excel on the four sheets to stagger them as best I can, but nothing seems to work.

This only occurs during the presentation. I am running this on windows XP, Office 2003, and on a Pentium 4 with 2.8 GHz processor with 504mb RAM... I have checked the performance when this happens and the CPU is to the max, while the RAM seems ok.

I was wondering if the only option would be to use a machine with a better CPU?

I am sorry for the length of this post especially as it is my first, but I am at my wits end with this, and I would be grateful for any suggestions.

Thanks in advance,

Rob

7/21/2007

Thank you in advance for your assistance.

I have a two computer network. Files from Computer #1 are backed up to Computer #2.
On both computers I'm running XP Pro, with Office 2003. Excel on Computer#1 has 2 toolbars with macro buttons one for formatting routines I use frequently, the other has macro buttons that I've assigned macros that open workbooks on computer # 1 in the FILES folder. I need to use computer # 2 regularly. Excel on it has the same two toolbars. I want to open workbooks on computer #1 to avoid problems with updating(Do I update #2 to #1 or #1 to #2, in other words).

I've created a new personal.xls called, no2.xls, it's stored in the Office11XLStart folder. The formatting macros work fine on #2. I've recorded the Workbook opening macros so that the VBA would be correct. However, when I click a workbook button on #2 I get this error. Compile error: Method or data member not found. workbooks.open filename: _ is always highlighted by the debugger. and always the word open. This is a sample macro. I've use it with and without the ChDir statement. no change. I have re-recorded every workbook macro hoping to solve this, and Goggled for an answer
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub BUDGET2007()
'
' BUDGET2007 Macro
' Macro recorded 7/21/2007 by Big Bob Brazoo
'

'
ChDir "Felmore-15e49b7FILES (F)FILES$$$CORE"
Workbooks.Open Filename:="Felmore-15e49b7FILES (F)FILES$$$COREBUDGET2007.XLS", UpdateLinks:=0 [I would like to update links, but doing this produced an error also]
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Computer #1 is named Felmore, #2 is Robert. A pseudonym of mine has been Bob Felmore Quality Used Cars and Trinkets. Big Bob Brazoo is another pseudonym.
Why does the macro keep erroring out? How should it be written?
Thank you once again for your assistance

Brian Day

I have set up a userform on the sample worksheet I have attached.

I have an error coming up when I hit 'Add' saying ''Compile error: method or data member not found''

I'm not sure where I have made an error in the code as it was working until I added some new code for one of the combo boxes.

I have also set up a search box which searches for business names in the worksheets. The search is working... ideally, when the options show up in the list box, when I double click one of them, I would like the form to fill out with that particular business. I would then add an 'Amend' button when someone changes the information. Also, when the search results show up in the listbox, is there any way of showing up the 'Status' files alongside the business name?

Any help is much appreciated!

Cheers,

John

I have an Microsoft Office 2001 Excel file with multiple sheets and charts, running on a G3 Macintosh, using OS 9.2. I am using around 30 macros so users can navigate to charts easily. The file has been working fine, except lately, occasional opening of the file results in the following error message:
"File Error: data may have been lost"
I reverted to an error-free backup, and recreated some things I lost- but now this copy occasionaly gets the error message.
Any thoughts on this??
(I have re-installed Office once- I have also opened the file on a nother computer- and it gets the error message).
The file does eventually open up, but there seems to be some error in a couple of macros.

I have a routine as part of a large macro that takes the current open sheet and saves it as a text file. I included error handling in the event that the file already exists and the user does not want to replace the file so that a save as window will come up to select a new name. This code works perfectly unless the new name that is chosen is not unique and the user is once again prompted to chose to replace the file. If the user choses no at this point, an error occurs because it is already in the error handling statement. Is there a way i can make a loop so that no matter how many times the user choses a name and location that already exists, the save as box will continue to appear until a unique name is picked or they decide to overwrite another file. I included my code and perhaps there is a way without even using error handling that im not aware of. There is other code in there for other parts of the macro that you can ignore.

Code:
 
  Workbooks(sFilename).Activate
 
  Application.DefaultSaveFormat = xlTextPrinter
  On Error GoTo Incomplete
  ActiveWorkbook.SaveAs FileName:=Location & "" & sFilenametxt, FileFormat:=xlTextPrinter, CreateBackup:=False
 
  Application.DisplayAlerts = False
  ActiveWorkbook.Close
  Application.DisplayAlerts = True
  Kill "c:print.xls"
 
  ThisWorkbook.Worksheets("Input").Activate
  Range("b14") = Location & "" & sFilenametxt
  Exit Sub
 
Incomplete:
  ChDir (Location)
  FName = Application.GetSaveAsFilename(sFilenametxt, "Text Files (*.txt), *.txt")
  If FName = False Then
      MsgBox "Head File Not Created", vbCritical, "Head Create"
      HeadComplete.Incomplete
  Else
      ActiveWorkbook.SaveAs FileName:=FName, FileFormat:=xlTextPrinter, CreateBackup:=False
  End If
  Application.DisplayAlerts = False
  ActiveWorkbook.Close
  Application.DisplayAlerts = True
  Kill "c:print.xls"
 
  ThisWorkbook.Worksheets("Input").Activate
  Range("b14") = Location & "" & FName
 
End Sub
Thanks in advance,

Adam

I installed Adobe Acrobate 6.0 on our computer and now I get the following
error message when opening Excel. I posted this to the Adobe Acrobate
newsgroup with no answer so I thought that I would try this newsgroup and
maybe you can aim me in the right direction.

I get the following error message when opening Excel.
Microsoft Visual basic
Class not registered looking for object with
CLSID{AC9F2F90-E877-11CE-9F68-00AA00574A4F)
When I click on the OK button twice the error message leaves and my Excel
document will open. I have removed Adobe Acrobat 6.0 and then started my
Excel and I do not get the error message but when I re-install Adobe Acrobat
6.0 the error returns. I have run the detect and repair on both Adobe and
my Microsoft office program. I am using Microsoft office 2000. The error
message only show up in Excel when I open it.
Would appreciate any help I can get to try and solve this.
Lem

I have written a userform. My system crashed and had to be reformatted.
After installation, I started Excel checked ref under VB editor. I was
missing 2
"missing: microsoft windows common controls-2 6.0 (SP4)"
and also mccomctl.dll

I have copied the first file from another system and it seemed to like it, I
no longer got the missing on the mscomct2.ocx. I also reinstalled McAfee
(the supplier of the the second one.

I now have now "missing" entries, however I do get a "compile error" "method
or data member not found" and it pops to an entry with a DTPicker varible
..DTPicker78 = ActiveCell.Offset(0, 77)
This had worked before. I tried it back on the system I have been using for
a backup, and it is NOT working anymore either! I did NOT make any code
changes etc....

On a side note, when I go into the editor becuase of a trap, how can I get
to the referances? I have been exiting out of the editor and then get a box
that says "this command will stop the debugger" and then after returning to
excel, reenter the editor. How else can I do this?

Thanks
B

Hi,

I am get an "Unable to read file" error message when opening a Excel
file that contains a PivotTable report.
All the symptoms match microsoft support article 819853, except I am
using Office 2003 not 2002.
Is there a patch for the problem and how do I obtain it.

I do need to unprotect and protect the workbook and worksheets, and
not corrupt the file.

Regards
Tim

Hi

This seems simple but I cannot get it to work (or maybe it's just late)

I have a cell where data must be input. The data of that cell must match a list of 100 or so entries in another column. I would like the cell to show a message or color red if the data does not match that data set.

data set is in form
101-0012-NVY-10
101-0013-GOA-12
etc

Any ideas?

Thanks
Mat

Hi,

I am get an "Unable to read file" error message when opening a Excel
file that contains a PivotTable report.
All the symptoms match microsoft support article 819853, except I am
using Office 2003 not 2002.
Is there a patch for the problem and how do I obtain it.

I do need to unprotect and protect the workbook and worksheets, and
not corrupt the file.

Regards
Tim

Hi All,
I have three combo boxes in Sheet7. The moment I open the Workbook, the following error pops up;
"Compile Error:
Method or Data Member Not Found"

I have used the following codes to run the combo boxes.
PHP Code: 
Private Sub ComboBox1_Change()

    Me.ComboBox2.Clear
    Me.ComboBox3.Clear
    If Me.ComboBox1.Text = vbNullString Then Exit Sub
    Dim ListCell As Range
    For Each ListCell In Sheet1.Range(Me.ComboBox1.List(Me.ComboBox1.ListIndex))
    Me.ComboBox2.AddItem (ListCell.Value)
    Next ListCell
    End Sub
Private Sub ComboBox2_Change()
    Me.ComboBox3.Clear
    Dim ListCell As Range
    If Me.ComboBox2.Text = vbNullString Then Exit Sub
    For Each ListCell In Sheet1.Range(Me.ComboBox2.List(Me.ComboBox2.ListIndex))
    Me.ComboBox3.AddItem (ListCell.Value)
    Next ListCell
    End Sub 
How do I get rid of this error message.
Thanks
Lok

I'm trying to create a validation error message to prevent the user from entering numbers other then $10,000 units. I need it to have a minimum of $10,000 up to a max of $1,000,000. Therefor I want to prevent the user from entering numbers such as $19,999 as it is not a unit of $10,000.

I know how to create an error message I just need help on determining the settings or the formula to prevent other numbers other then units of $10,000.

FYI, I do not want to create a validation list of $10,000 units up to $1,000,000 as this would be a ridiclously long list for the user to scroll through.

Any suggestions are greatly appreciated.

Thanks

"Installation source has been corrupted" error message when you ...

... the functionality of programs and operating system functions ... Release Date: 8-26-2003 For additional information ... having administrative credentials the first time you ...


No luck finding an answer? You could always try Google.