Worksheet Before Save - Getting Debug Error?
Feb 12, 2014
I am trying to take the template I have created and after the information is entered, if all required is not filled in, it will highlight the cells that need filled in. I get a debug error on
VB:
Cell.Interior.ColorIndex = 6
And the file does not save elsewhere. It goes into never never land. Here is my whole code:
VB:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Start As Boolean
[Code]....
View 5 Replies
ADVERTISEMENT
Jun 22, 2009
I have a working Excel 2007 macro that accesses two worksheets. I then added a third worksheet and want to access it from the macro. I get a debug error 13 Type Mismatch when the macro tries to access the third worksheet. I know I need to add the third worksheet on a pop-up but I don't remember how to open it.
View 14 Replies
View Related
Jul 6, 2006
The code thats generating the error is the following:
For Y = 8 To 131
TmpDate = Empty
Range("A1").Value = "=" + Path + Sheet + "B" + CStr(Y)
TmpDate = Range("A1").Value
For Langd = 1 To Len(TmpDate)
TmpChar = Mid(TmpDate, Langd, 1)
If Not TmpChar = " " Then
TmpComp = TmpComp + TmpChar
End If
Next Langd
TmpDate = TmpComp
TmpComp = Empty
If Len(Dag) = 1 Then
Dag = "0" + CStr(Dag)
End If
Macro continues before the Next-statement...
(it might not be good programming, but I think it should work).
A little explanation to the code.
I have a spreadsheet located on the intranet that has values I need in my spreadsheet. These figures are sorted by date, so I search for the date to find the right figures.
The line Range("A1").Value = "=" + Path + Sheet + "B" + CStr(Y)
works prefectly. I can see the value in my spreadsheet in the cell A1. its the next line that causes the error "Type mismatch".
The value I get from the intranet spreadsheet looks similar to this:
"1 Jul 2006 " (note all the spaces).
What I do is just run it through a loop and cut of all spaces so it will look like this: "1Jul2006" to be sure I dont miss a space or anything.
Now to the funny part.
The error only happens at runtime on the first go.
If I chose to END the macro then and there, and then run it again from the top it works perfectly.
If I set a stopsign to debug the code, it works perfectly on the first go too.
Can anyone explain to me why that happens??
I know enough about programming to handle my syntax errors and so on, but I cant see anything wrong in this one...
Please note that this isnt the the entire code.
I'm building a report on 12 diffrent spreadsheet. The main macro is almost 2000 rows of code, and it call other Subs too (because of limitations in the VBA-editor. A macro cant to be too big), so its impossible for me to post the entire macro...
View 9 Replies
View Related
Jan 23, 2010
I am trying to run a macro that will export the 'Results' WS & ask the user where he/she wants the .xls to be saved, though when I click 'Save' nothing happens.
In additons in the save as part it has the WB's name(and full extention) is there a way to make this blank or to have something in it? I.e the WS's name?
View 10 Replies
View Related
Sep 25, 2009
I created a long macro as follows with 2 Select Case structures and the macro works fine.
View 14 Replies
View Related
Mar 19, 2012
I have below code which I was thinking is very simple to select variable worksheets. However I get Debug error when running:
Code:
Dim rng As Range
Dim cl
Set rng = Sheets("Budgetted sku's").Range("A2:A24")
For Each cl In rng
Sheets(cl.Value).Select
Next cl
View 5 Replies
View Related
May 1, 2009
I have code that selects and copies data from one sheet onto another sheet. the sheet that is getting data copied too has borders defined. When i run the code i get a debug error. Here is the issue.... when i remove all the borders it works fine. I have tried everything i can think of to solve the problem but have had no luck. anybody have an idea what is causing this. i attached the workbook file so you can see what is going on.
View 2 Replies
View Related
Jun 19, 2009
i m working on an excel 2007 workbook with ms project functionality. in order to be able to use ms project from within excel i m using early binding. for those who dont know what early binding is: http://www.dicks-clicks.com/excel/olBinding.htm
this works fine if the user has ms project installed on his/her pc.
if the user does not have ms project installed i remove the broken references. this works fine.
but my problem is that when i remove the reference to ms project, my vba project will not compile correctly. thus each time when a user opens the workbook the user gets a "compile error in hidden module" , since the functions of ms project are not available.
for example i get a compile error in the following source
Public Function getResID(ResName As String, ActiveProject As Project) As Integer
Dim res As Resource
For Each res In ActiveProject.Resources
If res.name = ResName Then
getResID = res.ID
Exit Function
End If
getResID = -1
Next
End Function
i just want to know what i can do to prevent the compile error? would the use of late binding remove the problem? actually i dont want to use late binding since my source code is already very complex and it would be a huge effort to change it.
View 9 Replies
View Related
May 29, 2009
The two code extracts below are associated with two separate worksheets in the same workbook. They work fine. They simply are used to assign either a PO# or a Temp Unit #, located in a third sheet, in their respective sheets.
The problem is that when I'm in either the Master or the Work Orders sheets where these two codes are used, and I do something simple like drag down some values or paste something in a cell, I get the "Run time error "13" type mismatch error" in the popup debug window for my VBA code. And in each case, it highlights the codeline I've color coded below:
View 2 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related
Oct 10, 2011
I have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.
The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.
Code:
Private Sub cmdImportData_Click()
Dim sFName As String
'On Error Resume Next
PrepData
CopyData
FormatColumns
'prompt the user to save the file in "*.xls" format
sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls")
End Sub
View 3 Replies
View Related
Aug 16, 2014
I got an error message that I cannot save while debugging. But I have repeatedly hit the stop icon to stop debugging!
I have minimized all the windows (except the spreadsheet as it does not allow me select it and just beeps) to make sure there are no hidden message boxes.
I do not want to force exit and lose my work but I cannot get out of debug mode for some reason!
View 6 Replies
View Related
Dec 7, 2009
I'm trying to create a macro that will take a worksheet from an Excel workbook, and save it as a text file, with the name determined from user input, and the location being the desktop of the currnet user's computer.
First I use a function, (provided by RoyUK), to get the name of the current desktop, then I try to use that name, along with my user input, so save the file. Obviously, it's not working, or I wouldn't be here. I get a "Run-time error '1004': The file could nto be accessed". I think it's just a syntax issue with the file name, because debug takes me to the "SaveAs" portion of the macro.
View 2 Replies
View Related
Jan 12, 2010
I have got the code below, I am trying to save as file name with it ending with upload, however I am getting run time error 91,
'Worksheet save as upload name
Dim Wk As Workbook
ActiveWorkbook.SaveAs Filename:=FileDrive & Wk.Name & "Uploads" & ".xls", _
FileFormat:=xlNormal
View 9 Replies
View Related
Sep 21, 2006
Here is my existing code...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application. ScreenUpdating=False
' Cancel user prompted save procedure
Cancel = True
' Code removed... changes some values and settings before saving
ThisWorkbook.Save
' Code removed... return the original values and settings
Application.ScreenUpdating=True
End Sub
For some reason, the workbook is not being saved when the above code is executed. It seems the problem lies with "ThisWorkbook.Save"... in that it just isn't saving! If I delete "Cancel=True", the workbook will save (obviously!).
I have spent an hour trying to work this out and have come to the conclusion that it is something other than my code... has anyone experienced this problem before?
View 9 Replies
View Related
Jun 13, 2008
When I use this code in the workbook I want it gets as far as creating a new workbook and pasting the data, but then an error comes up saying nothing but "400", and does not complete the saving part.
Private Sub Workbook_Open()
Sheets("Open").Activate
End Sub
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet
myPath = "K:3. DesignSupply OrdersLog"
For Each sht In ThisWorkbook.Worksheets
If sht.Range("D1").Value "" Then
sht.Copy
With ActiveWorkbook
.SaveAs myPath & ActiveSheet.Range("G11").Value & ".xlsx"
.Close
End With
End If
Next sht...................
View 9 Replies
View Related
May 12, 2009
I've got some code that reformats a file that a supplier sends us. They seem to randomly change the structure, so I'm trying to make the code a bit more robust.
Part of the code uses MATCH to look for specific column headings, so I've got some error handling around this of the format:
On Error GoTo NoIncCol
Line using MATCH
On Error GoTo Exiting
where Exiting is my standard error handling (Close without Save) and NoIncCol is the same, but with an error message saying that the term the code was trying to MATCH can't be found. This works well for me.
In another part of the code, I need to check for another column heading, which so far has turned up in two different forms. At the moment I'm doing this as follows:
With Sheets("Sheet1").Range("1:1")
Set rFound = .Find("Term1")
If Not rFound Is Nothing Then
a = "Term1"
Else
This feels a bit clunky, and I don't really like having what is essentially error-handling in the middle of my code. But the only other way I can think of to do it is to use a GoTo, and I believe that's not really considered good practice either.
View 9 Replies
View Related
Mar 1, 2007
I need to:Turn off "save" and "save as" command barsPrompt user for a unique filename Save file to a specific directory on the network common to all "p:dataprc"Requery user if filename exists and if they DON'T want to overwrite.Return to worksheet (there is only one) if the user cancels from the message box.Turn commandbars back on The macro runs from an on screen button
I get bug errors on NO or CANCEL when clicked in the message box. This is my current
Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False
End Sub
Sub SaveMe()
'
' Keyboard Shortcut: Ctrl+z
'
Dim BaseDir As String
Dim NewName As String
BaseDir = "p:dataprc" ...........
View 7 Replies
View Related
Apr 18, 2009
I have a command button that runs a save macro to which I added cell validation the message box comes up and what I want it to do is go to the cell that is blank
View 2 Replies
View Related
Mar 30, 2014
I am executing code in a workbook that has been working successfully previously.
Suddenly I get a run time 1004 error in any macro in the workbook that tries to execute a activeworkbook.save command?
View 1 Replies
View Related
Jul 21, 2013
Im struggling to get a workbook to a specific location. I have copied 3 worksheets to a new workbook, which im trying to save to an archive folder. Here is my code.
Code:
Sub atest()
Dim strFileName As String
Dim Archivepath As String
[Code]....
The problem is i keep getting an error (runtime 1004) saying the file could not be found?? well, im trying to create it !..
View 2 Replies
View Related
Sep 23, 2009
Why does this give me a debug error?
View 4 Replies
View Related
Jun 5, 2008
Does anyone have a workaround for the following error "Excel Could Not Save All the Data and Formatting" Error Message (http://support.microsoft.com/kb/215783). I am applying validation to many, many cells
View 4 Replies
View Related
Jul 25, 2009
I was able to get some code to save an existing worksheet to a new workbook. However, when a duplication file name exists it prompts me to save as and if I click 'No' I get a debug error message. I'd still like the 'save as' message to come up but is there a way I can get eliminate the debug error message if 'No' is clicked?
View 3 Replies
View Related
Apr 2, 2014
I have created a user form (UserForm1). When I click the submit button I would like (after it has completed various other commands) it to save a worksheet "Invoice" as a PDF.
I know this is possible, however, I would like the filename to be pulled from:
A field from the UserForm1 we will called "PlotID", and a cell I have created that displays the current year and quarter, on the "Invoice" sheet "U12".
So the file created would be called "Invoice - [PlotID] [U12].pdf"
View 8 Replies
View Related
May 9, 2008
I have a problem with a HUGE macro project I'm working on. The macro itself isn't huge, but it's being applied to about 10,000 files. The macro is updating information on three spreadsheets in each workbook, but the problem is that the password protection (and Macro in general) fails to unlock when the password was entered in UPPERCASE. So the password is "king" and/or "KING" depending on the sheet. There is no way of predicting which sheets will be caps and which will not, but it's frustrating when my macro stops every 5 files with an error because of a wrong password (even though it's always one of those two).
Is there an IF THEN statement or something that I can do so that the macro doesn't stall every 30 seconds to 2 minutes... I have 10,000 files to crawl through.
View 10 Replies
View Related
Dec 5, 2008
I am trying to put together an automation. I am having problem debuging the code. I am trying to have the automation autofilter with "Interior" and "Exterior" at column P. I am using a statement as follow to pick the cell that is showing at at second row each time after the autofilter ran. (the first row of Exterior and Interior are different) However, when i pick Exterior, the automation was able to locate the second cell after picking Exterior, but when I try Interior, then i will have a debug meesage.
Range("Q2:Q" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(12)(1).Select
The code that I used is as follow:
Sub Macro4()
Range("L1").Activate
Selection.AutoFilter
' Selection.AutoFilter
Selection.AutoFilter Field:=15, Criteria1:="=*Fixture*", Operator:=xlAnd
Selection.AutoFilter Field:=16, Criteria1:="="
Range("P2:P" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(12)(1).Select
ActiveCell.FormulaR1C1 = _
View 9 Replies
View Related
Dec 15, 2009
When I debug this script I get: division by zero!
In 3 lines over the debugging line, the script are writing out the variable "prosentref" as 1. Whatt can be wrong?
(the value in cell T8769 is 1)
Option Explicit
Sub optimaliseringDrift()
Dim Pgm1 As Integer
Dim Pgm2 As Integer
Dim PL As Integer
Dim lamda_gm1 As Double
Dim lamda_gm2 As Double
Dim deriv1 As Double
Dim deriv2 As Double
Const Pgm1_max As Integer = 200
Const Pgm2_max As Integer = 300
Const Pgm1_min As Integer = 45
Const Pgm2_min As Integer = 40
Const deltaP As Integer = 5
View 9 Replies
View Related
May 8, 2014
I have a sheet full of data.All this long I had written the code to save the selected cells as a PDF.But now I want it to be saved as a separate file. In this case, I believe, first the VBA will have to open a new workbook. Then copy paste this sheet onto one of the sheets in the workbook . then save the .XLM file.
[Code] ..
View 7 Replies
View Related
Dec 30, 2008
Is it possible to have the worksheet save itself automatically every 5 minutes or so?
I don't want the user to have to click on a macro or do anything. Basically they will not even know that it is saving.
View 2 Replies
View Related
