I have a workbook with macros that run on my laptop, but when I email the workbook to another user and the macro is executed I get a subscript out of range message?
Both Excel versions are the same.
Environments are the same.?
I have used the following piece of code to stop error checking in my excel spreadsheet:
Option Explicit
Private Sub Workbook_Open()
' Application.ErrorCheckingOptions.BackgroundChecking = False
End Sub
The problem is - works fine on my computer, but when distributed on the server the folloeing error is returned:
Run-time error '438':
Object doesn 't support this property or method
I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?
Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True
End Sub
I tried testing, msgbox displays the correct last modified file "MMO Activity Report 09-29-06.xls" in path D:MMOWorkfile but I was not able to open the workbook.
Sub testGetNewestFile()
MsgBox NewestFile("D:MMOWorkfile", "*.xls")
Workbooks.Open (NewestFile("D:MMOWorkfile", "*.xls"))
Error:
Run-Time Error: 438
Object doesn't support this property or method.
Two things to note: This code was placed in the worksheet, something new I am trying and two the error line is not highlighting in yellow (as it normally does) indicating the line w/ the infraction.
Option Explicit
Sub QuickView()
Dim Wss As Worksheet 'Source Worksheet
Dim Wsd As Worksheet 'Destination Worksheet
Dim LRow As Long
Set Wss = Workbooks("TGSItemRecordMaster.xls").Worksheets(" Record Creator")
Set Wsd = Workbooks("TGSItemRecordMaster.xls").Worksheets("Quick View")
LRow = Wss. Cells(Rows.Count, "w").End(xlUp).Row - 4
Wss.Range("W3" & LRow).Copy Wsd("A3")
End Sub
[/code]
I'm having a hard time with this piece of
Private Sub Assign1Combo_Change()
If Range("ComboVisible") = False Then Exit Sub
Assign1_Download
End Sub
Symptoms:
- As soon as this is used, Excel/VBA can't select any range on the worksheet. For example, the following code (within the sub Assign1_Download) no longer works:
Range("firstdata").Select
Excel/VBA doesn't select that Named Range or any other range I try (ex. A1). - This problem only happens in Excel 2003. In Excel 2002 everything works fine (can make any selection).
I wrote this code so that someone else can keep track of a certain bond. It will provide him with returns and a chart between 2 periods. I wrote it on Office 2007 and it works for me, from the begining I wrote the code in an office 97-2003 compatible file. The worksheet is attached.
Sub Grafico()
Dim FechaGraficoInicial As Date 'Fecha de referencia para el rango
Dim FechaGraficoFinal As Date
Workbooks("Julius Baer.xls").Activate 'Para evitar un error, NO BORRAR
Worksheets("Precios").Activate
Columns("G:G").Select
Selection.ClearContents
Workbooks("Julius Baer.xls").Activate
Worksheets("Rentabilidades").Activate
FechaGraficoInicial = Worksheets("Rentabilidades").Range("B22")
FechaGraficoFinal = Worksheets("Rentabilidades").Range("B23")
i = 0
Condicional = 0 'Determina si el grafico se hace o no..........................
I have the equivalent of Vlookup code(I couldn't get vlookup to work correctly) I am using Excel 2007. I have an array 2 cols x 1652 rows. I have another column of 6264 items. If a text item in the first column of the array matches a text item (minus 3 characters) in the longer column, I place the matching row from the array next to the item in the longer column. Whats not working is that the text never gets put in the cell.
Code:
Sub Macro1()
'
' Macro1 Macro
[Code]....
if i run my macro that saves a sheet as a certain name and then prints it, it works, but if i run my macro and the file already exists it asks me to overwrite it. If i select yes it prints it and carrys on with the rest of the macro, (good). If i select no the macro fails. How can i get it so if i hit no, it runs the rest of the macro after the print command?
View 2 Replies View RelatedI have a macro that sends a sheet via email when the engineer has completed it and clicks a button. I added a filter to some of the results to remove blank lines and now the macro will not work. The problem is that if I remove the filter so that the macro does work, I end up with blank lines again.
View 4 Replies View RelatedHow can I let the Sub carry on to next instruction if Macro1 fails?
Private Sub Workbook_Open()
Call Macro1
' If Macro1 Fails go to next macro
Call Macro2
'If Macro2 Fails go to next macro
Call Macro3
'If Macro2 Fails go to next macro
Call Macro4
End Sub
I have a workbook which I need to give shared access.
The workbook has macros which make sheets visible and VeryHidden and also give certain sheets protection. Also a filter runs on one sheet.
When I give the workbook shared access, the macros error?
how I can give me workbook shared access but still allow the macros to hide/unhide sheets, filter and protect worksheets?
I have a protected worksheet containing an SQL query. I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query.
Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below
The problem: My macro won't unprotect the sheet before it calls the SQL.it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify queryit won't run the query / return the data as the sheet is still protected Comment: I have had to remove sheet protection in order to allow the sql to execute and return data. If protection is removed, the query does execute and return data ( changes in the "parameter" cell contents do cause the query to be correctly modified)
I could comment out the debug info but the query still doesn't return data if the sheet is protected.
Activesheet And worksheets(ndx) where ndx has been defined As ActiveSheet.Name
I apologise If my post Is difficult To follow - especially the code.
Sub AgedStockParameters()
nmb = ActiveWorkbook.Name
ndx = ActiveSheet.Name
Workbooks(nmb).Activate
Worksheets(ndx).Activate
ActiveSheet.Range("A1").Select
WkshtQryCon = Workbooks(nmb).Worksheets(ndx).QueryTables(1).Connection
ActiveCell.Value = WkshtQryCon
Range("A2").Select
WkshtQryFld = Workbooks(nmb).Worksheets(ndx).QueryTables(1).CommandText
ActiveCell.Value = WkshtQryFld.......................
I've been using a macro successfully for the last couple of years, but this morning when I went to use it, it decided to fail. I have a workbook which contains various spreadsheets. The macro that has failed performs the following tasks:
1. It copies a list of email addresss from an external workbook to a sheet in the current workbook (still works)
2. It copies the referral sheet I want to send to a new file, and saves it with an appropriate filename to an appropriate folder (still works)
3. The macro then creates an email with a standard subject line, attaches the new worksheet and emails it to each of the addresses as above (broken)
The error message is from Microsoft Visual Basic. "Run-time error '1004': Mail system failure. Check your mail installation."
I'm guessing there is a setting somewhere in Excel that has changed as part of an update.
I've been through a number of the options in the developer menu to remove any obvious restrictions (& reopened excel afterwards), but so far it hasn't resolved the problem.
For what it's worth, here is the macro code.
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Run "PullInSheet1"
Dim oldbook As String
[Code] .......
In case you're wondering about the pullinsheet code, I'll add it below - but I probably grabbed it from this forum a couple of years ago (like some of the above) & just made some changes.
Code:
Sub PullInSheet1()
Dim AreaAddress As String
'''''' Sheet11.UsedRange.Clear
Dim ClRange As String
ClRange = "= 'L:ADMINEMPLOY SERVICES" _
[Code] ........
There are a couple of things I've wanted to do to improve the macro, but I couldn't justify the need to spend time working it out (since writing spreadsheets isn't really my job). Since it's broken at the moment, I can...
1. I'd like to create a subject line that reflects the name of the person being referred. For some reason though, anything other than text in the cell reference caused an error for me. eg, I tried using concatenate to create my subject line, but it didn't work.
2. I'd like the copied sheet to contain all of the formatting of the original sheet. Presumably there is a paste option that will do this and I just picked the wrong one.
The mailsystem we use is Groupwise 8. I couldn't find any settings in that program that have been changed, or that I could change.
I should change the extension from xls to xlsx in the code since I'm using Excel 2010 (but changing it doesn't fix the problem).
This is a shared worksheet. When I look at it I can manipulate the calendar, but for others it is just a non-interactive image.
I'm sure this has something to do with them not having this control in their computer, but is there a work around?
So I have this code I'm working on for my deptarment that goes to a website inputs data , clicks run and downloads the csv file to the worksheet. It works just fine on my PC and on my profile on the computers the other department uses. We are both using same versions of windows, excel , and IE. When i have someone from the other department run the macro it opens the website but never enters the data into the fields despite the site being the exact same coding as when i'm logged in.
[Code].....
When this code is ran by a member of the other department it just opens the website inputs nothing and doesn't press the RUN button on the website.
What setting or anything. I verified that both PC's VBA references in are there and no "Locations are missing paths" .
I bought 32 laptops out of my own moeny (used) to get my 5th grade students into the 21st century. I have several programs working on these laptops which output data in text and xls files. I would like to either:
1. Poll the files on each workstation at the end of the day and bring the data into excel (I have worked in VBA). Computers are named Comp01 to Comp33.
2. Better: have the workstations save the files to my NAS device so they are on one hard drive.
I would like to keep it simple (I'm a teacher not a good programmer.) Is the "Import External Data" the way to go for the text files? Can a macro go out and loop through the 32 laptops using that or some other add-in?
I have been limited lately to only working on this project at work because I have added a few lines here and there throughout the workbook that make a reference to the directory the file is located in. for example:
View 2 Replies View RelatedI have a timesheet I created in Excel. I want to hit a button and have it save on the current computer. I also want it to copy the file to another computer on our network. I would like it to overwrite the exsisting file on this other computer automatically without a message saying "Do you want to overwrite exsisting file". The other computers dir is \OfficeTimesheet
What code can I write to make this happen?
[url]
Private Sub Workbook_Open()
****'** MsgBox Environ("ComputerName")
****If Environ("ComputerName") UCase("Authorized PC Name") Then
********MsgBox "Sorry No Go!" & vbCrLf & "Wrong computer!"
********End
****Else
********'** Do your thing here
********MsgBox "Proceed"
****End If
End Sub
Reading the thread in the link above, It seems as if one could limit access to a workbook. I tried this code on a dummy workbook, I have a few question...
1. Why am I getting "Sorry, No Go. Wrong Computer" when I just put the code in the dummy worksheet for the first time?
2. Can this code be modified to capture my clients computer information to restrict the workbook useage only on one computer and prevent unauthorized distribution to other computers?
3. Can this code run automatically everytime the workbook is open?
Private Sub Worksheet_Calculate()
frm_document.Show
End Sub
A userform is called up upon a calculation change in the spreadsheet on two computers and has for years. We just hired a new employee and the userform is not pulling up for her. Instead she gets an error: Runtime error 75...Could not find the specified object.
I've created a userform that will be used by multiple users. I'm not finished coding it yet but when I tried to work on it on my computer at home it did not look the same as it did at work. I tried it on several other computers and experienced the same problem.
It appears that the inserted company logo is zoomed in and the some of the label text has wrapped and is not visible.
I've attached a screen shot of the form on my work computer and the way it looks on other computers.
I have a workbook with sensitive company information. I need a way to validate the computer the workbook is being opened on and then close the workbook if it is not a confirmed workstation.
The file is already password protected on opening, each worksheet and the workbook is protected, and the VBA code is protected. We just need to take it one step further.
Is there a way to check a registry entry or the computers MAC address on workbook_open?
I have found code to close the workbook without saving which I would use after a message box stating "You do not have permission to access this file."
If someone has their macro security level set to high or very high, that prevents my code from running. How can I make this work on any computer no matter what their macro settings are?
I currently have a worksheet opened on excel 2010 using windows XP. When I open the exact same file on another computer using excel 2010 using windows 7. The spacing and formatting is different.
View 3 Replies View RelatedRunning excel 2002, windows vista (some machines on network are running xp). I have a spreadsheet that I want to be able to edit from all computers on the network so that it shows real time changes on all PC's.
It would be like using google docs, I can't use google docs because my file size is 2.5MB and it is too much for it.
Is there a formula or VBA script available which, when executed, will find the IP address of the computer on which the code / formula is run (obviously - through Excel)?
View 9 Replies View RelatedI have a problem i have written an excel macro in VBA and it is working on some computer but on the others it is not working. It says "compile error in hidden module"
View 3 Replies View RelatedI have designed an excel spread sheet for my staff to fill in when completing care plans - there are a lot of drop down boxes etc., on the form - when it comes to printing 3 computers on the office print it fine but the other 3 the margins seem to move and the sheets come out totally wrong - I have checked the setting and we are all using the same setting etc., but it is still happening - we are also all printing to the same printer.
View 1 Replies View RelatedI have a technician who sits a desk over, has the same brand of computer running the same Windows 7 and the same Excel 2010, but when he opens a particular file the page layout or print area seems to change so that the sheet is partially cut off. All his options appear the same. I can open this file on other computers and it seems to be fine...
I notice that the Margins under Custom Margins are different and this would explain the problem but not why it occurs. It's a file I created and saved and he opened. Why would the margins change? Is this one of the Microsoft cross platform / program deals where if he changes the print set-up or page layout in WORD for example, it changes them in EXCEL?
Is it possible to protect an excel file such that it will open up only on designated computers (identified by the computer name or some unique hardware identification like MAC address etc)?I was wondering if the VB editor can be used to do the same.
Let me put my requirement in detail:
I have an excel file "123" created in one computer (named=A). On this computer this file can be opened by anyone.I write a code such a way that, this particular file when copied on to other computers say (B,C & D) would open up as usual. But on computer E or any other computer, it should not open.
I cannot use password protect feature on the file as "n" number of users will be accessing this file on those designated computers. I was finding few of the clients copying the files on their personal drives or email without proper consent.If its possible, I would like to employ the same on few of my word (.doc) files as well.