Macro Has Errors If Visual Basic Editor Is Not Open
Nov 12, 2009
I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet".
I have got the whole thing figured out and running to satisfaction, except...
The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range"
Any ideas what could be casing this? The errors occur when attempting to add sheets.
View 2 Replies
ADVERTISEMENT
Apr 26, 2007
I have created macros before, but it was a step by step, easy to follow, guide from out textbook. Now I have to create one on my own from these directions.
So my first task was to create a macro that went to a specific worksheet, and made A1 the active cell. I created that macro.
Now it comes to where I have to open the macro in Visual Basic Editor and I am completely lost. I know absolutely nothing about this. This is what it looks like now:
Sub Show_Report()
'
' Show_Report Macro
' Macro recorded 4/25/2007 by *******
'
' Keyboard Shortcut: Ctrl+w
'
Sheets("Jeff").Select
Range("A1").Select
End Sub
So now these are the next directions. "Edit the Show_Report macro in the VBE so that the macro prompts the user for the name of the report to view (indicating that the user should enter "Jeff", "Sally", "Jane", "Jim", and "Harry") and then displays that worksheet. (HINT: You should run the edited macro to make sure that it runs correctly.) Include an If-Then-Else control structure to detect an error if the user enters an incorrect report name. If an error occurs, the macro should display a message box informing the user of the error.
So what Im thinking is I need a code so what when I hit my macro hotkey, it opens up a pop-up box and then I get to type in a worksheet name and it takes me to that worksheet.
View 9 Replies
View Related
Apr 17, 2007
'The following code creates a new commandbar name Visual Basic Editor, which on event delete the VBE module1. How can I tie the built in visual basic editor commandbar to this.
Sub bar()
Set mybar = CommandBars("Tools").Controls("Macro")
Set mycontrol = mybar.Controls _
.Add(Type:=msoControlButton)
With mycontrol
.Caption = "Visual Basic Editor"
.FaceId = 1
.OnAction = "MySub"
End With
mycontrol.Visible = True
End Sub
Function mysub()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With
End Function
View 6 Replies
View Related
May 15, 2007
I have a problem with my VBA Editor. I have the Projects Window at the top left of the screen, the Properties underneath it and, normally, the Userform/Code window fillint the rest. However, The userform/code window is not behaving as before. When you try to get one or the other they open in another window whereas I'm pretty sure that they just opened in the same window.
View 4 Replies
View Related
May 21, 2007
I am having trouble entering all the text in a single line in the Microsoft VB editor (the application that opens when "view code" is selected in Excel.
The code I am trying to enter is:
vUser3 = Array("Login", "CLREL Client Relations Sum.", "CLRMGT Client Relations Mgmt.", "Client Relations Admin", "Sales Support", "Admin Officer", "Admin Support", "Trade Show Mgmt","Client Rel Prof Serv", "Client Rel Imp.", "Sec. Mkt & Inv. Clients", "SALES - Sales Summary", "Sales Admin", "Cross Selling", "Loan Fulfil. Service Sales", "Channel Management", "Corp Div. Mgmt.", "Strategic Sales", "SALN - Sales Northern Region", "SS-Gleason", "SS-Collins", "SS-Hodum", "SS-Pluchino", "SS-Robbins", "SS-Sandbach", "SS-Johnson", "SS-Conner", "SALW - Sales Western Region", "SS-
I realize that this is very long, but the VB editor does not see it all as the same line, and therefore I get a Compile error: Expected: list separator or ). Is there a way to have this all on the same line?
View 4 Replies
View Related
Aug 6, 2014
I'm having difficulty integrating two nearly-identical codes into one with Visual Basic Editor. Here is the coding that is leading me to trouble.
[Code] .....
View 2 Replies
View Related
Oct 10, 2008
how to run a userform with a macro that opens the visual basic editor and runs the userform1 in it; rather than using userform1.show.
View 9 Replies
View Related
Feb 26, 2008
Is there any way I can display line numbers against the code in the Visual Basic Editor?
Or can I see in the current line number in the status bar?
or GoTo Line number?
View 13 Replies
View Related
May 26, 2009
Is there a way to make a macro fire when you close the VB editor. For example,
I would like one of my pre- recorded macros to start as soon as I close the VB editor.
View 9 Replies
View Related
Aug 4, 2008
I only want to change some Visual Basic Editor (VBE) font settings on the active workbook, but whenever I do so within the Editor tab of the Options dialog it changes the VBE font settings for all workbooks.
Is it possible to change just the active workbook VBE font settings or do these settings reside in some *.ini file that once changed, globally changes the setting(s).
View 3 Replies
View Related
May 1, 2013
I have been running a macro on a continuous loop to collect data for months. Yesterday I added something small to the macro and now it does not select cells. I tried closing excel, opening a new page and have a simple macro
Sub Please_Work ()
Range('A4').Select
End Sub
If I have VBA open and step through it (F8), it does NOT work. If I run the sub (play button), it does NOT work. If I close VBA, and run the macro it DOES work.
View 9 Replies
View Related
Jan 10, 2007
I am Generating Excel file with Macro using my asp.net (c#) application.
I am able to generate Excel file in development environment, but in Production it gives following error:
"Programmatic access to Visual Basic Project is not trusted Line: Microsoft Office Excel"
I did googling a bit and found that I have to open Excel file physically make few security related changes in macro as below.
1. Open the Office application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.
Can i do above changes at runtime (using some code)?
View 4 Replies
View Related
Jun 3, 2006
I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic?
Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet.
Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............
View 5 Replies
View Related
Jul 10, 2007
I am trying to open a closed workbook, auto update and save. Here is the code i have so far:
Dim xlBook As Excel.Workbook
Try
xlBook.Add()
xlBook.Open(FileName:="C:Old Tanglewood Numbers.xls", UpdateLinks:=1)
Now, the xlBook.Add() line is something I just added. I am not sure if it works but i dont think it does. Everytime i try to use Workbooks.Open it tells me that i need to decalre Workbooks, which i thought i did with xlBook. Anyway, I cant get it to work, something is throwing it out of whack.
View 9 Replies
View Related
Aug 8, 2012
I want to create a series of hyperlinks in excel to pdf's stored on a local drive/CD as a kind of database. This much I can do with a nifty bit of code I saw someone put up here as follows;
Sub Run_Local_PDF()
MyPath = "C:Program FilesAdobeReader 9.0ReaderAcroRd32.exe"
myFile = "C:Documents and SettingsmynameDesktoppdffilename.pdf"
Shell MyPath & " " & myFile, vbNormalFocus
End Sub
Which works great and is sitting snug in a module. However, the document I am linking to has various anchors in it which exisit as sectionheaders. One of which is "Contents".
Is there a quick way I can alter my exisitng VB code to open at one of these anchors?
View 2 Replies
View Related
Sep 21, 2013
macro visual basic and trying to do a simple report wherein i can send an email auto matically.
View 2 Replies
View Related
Nov 19, 2007
I have a excel file that was created by someone else 7 years ago - I still want the formulas that were created but I have updated the information on the spreadsheet and now the macro will not work
Sub DataSort01()
'
' DataSort01 Macro
' Macro recorded 11/18/00 by Terry Schiesser
'
' Keyboard Shortcut: Ctrl+t
'
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
End Sub
when I do the ctrl+t it wants to debug I select the debug option and then it goes to the screen above but the "Selection.Autofilter" is highlighted.
View 9 Replies
View Related
May 19, 2008
I have a query in Excel 2003.
My question is:
I have a macro created for my Excel. I have a short cut key to run this macro. When i run the macro, the VB editor is opened where i have written the code. My requirement is, I don't want the VB editor to be opened when i run the macro. I want the macro to be executed but the VB editor should not be visible to the user.
View 9 Replies
View Related
Jun 23, 2009
I want to create 3 command buttons (active X) on a worksheet to toggle between showing rows which only contain the below text in column L (range L9:L30) and showing all rows containg the options (However, I also have some blank rows in this range and i always want them to remain hidden.)
My text options are:
High ‚
Medium ՠҪ
Low ’á
The text arrives in the cells via a VLOOKUP
Is the chinese text a problem? i can't type it into VB.
I've been using the following macro to hide and unhide rows with a command button in the same sheet:
Private Sub CommandButton1_Click()
Toggle_Hide_Unhide
End Sub
Sub Toggle_Hide_Unhide()
Dim rngCell As Range
Dim TakeAction As Boolean
If ActiveSheet.CommandButton1.Caption = "Hide" Then
TakeAction = True
ActiveSheet.CommandButton1.Caption = "UnHide"
Else
TakeAction = False
ActiveSheet.CommandButton1.Caption = "Hide"
End If
For Each rngCell In ActiveSheet.Range("I9:I30")
With rngCell
If .Value = 2 Then .EntireRow.Hidden = TakeAction
End With
Next rngCell
End Sub
View 9 Replies
View Related
Apr 18, 2007
Can a event be triggered when the Visaul Basic Editor command button is clicked in Excel?
View 9 Replies
View Related
Jan 23, 2008
how I can get to the visual basic toolbar in excel 07. In previous versions there was a visual basic toolbar and it allowed you to add buttons, ect. Where is this in 2007?
View 9 Replies
View Related
May 6, 2006
Is there a code you can type in to VBA to allow a picture in a spreadsheet to change when a parameter is specified. For example i have a picture of a timber beam i drew in excel. and i wondered if i could set it up so that when the user inputs the length of beam they are using, the diagram alters to show the length specified.
View 2 Replies
View Related
Aug 4, 2007
My company "makes widgets". Each "type of widget" is associated in our internal database with a record locator (five alpha characters). Our company website can also use that record locator to pull up a webpage specifically dedicated to that widget. In addition to the webpage itself, the website also creates an expanded URL which contains additional information about that widget.
I'd like my macro to take a list of record locators, open up the company website related to each specific widget, pull back the expanded URL, and do some text management with the URL to populate other parts of the spreadsheet.
For example, if my record locator is "ABCDE", my macro would go to the website, "http://www.widgets.com/ABCDE". The site then redirects to an expanded URL: "http://www.widgets.com/whole-bunch-of-data-about-the-ABCDE-widget". I'd hope to write a macro which pulls back the expanded URL and does some magic with the "whole bunch of data" part. I've been told that the MSHTML library would be useful for a project of this type. I've been able to connect my spreadsheet to that library, but then I'm not sure what to do with it.
View 4 Replies
View Related
Sep 9, 2008
Is it possible to have Excel tap into an Access database and call up a function?
I have an Excel sub that looks like so
View 15 Replies
View Related
Oct 24, 2008
Have a project going and have had a disturbing trend develop. I'm working in Excel 2003. Upon opening the file and enabling macros, I started receiving File/Path errors, at which point it would shut down the application. When opened, I have a splash that would come up, but it wouldn't go to the next userform. At this point I would get the error message and the file would be closed. If I then reopened the file, disabled macros, saved under a different file name, and then reopened with macros enabled, everything worked fine, until I shut it down and then tried to reopen, at which point the same error would occur.
Lately I have simply been getting the Microsoft Excel dialogue box telling me an unexpected error occurred and it had to be shut down. I'm at a loss for what is causing this. The last time this happened, when Excel recovered the file, it told me a repair had been done, and the repair was to basically delete all of the VBA code from the file.
View 14 Replies
View Related
Nov 23, 2009
I am encountering a situation wherein each line of VBA code is essentially compiled as I type on that line, instead of when I move to a different line. For example, if the code I entered is incorrect, the compiler waits about half a second and then turns it red. Also, if I enter a space, it deletes it immediately.
View 4 Replies
View Related
Jan 6, 2010
I'm sure this is something I've accidently pressed while using VB. My text cursor has changed from the usual 'I' shape to a black box shape. Now instead of adding text where I'm typing it now types over previous work. Is there an easy fix for this.
View 2 Replies
View Related
Sep 26, 2013
How do you pass a variable value in Visual Basic?
I have a variable called LastRow
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Would like to pass the value in my Do While loop
Option Explicit
Sub IncValue()
Dim MyCell As Range
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Do While MyCell
View 2 Replies
View Related
May 2, 2007
My code here...
Sub ClearSheet()
Worksheets("Summary").Activate
If Range("CustInfo") = False Then
Range("ICompany, IPhone, IFax, IContact, ICell, IEmail, IAddress, IPOBox, ICity, IState, IZip").Select
Selection.ClearContents
Else
Range("IJobDescription").Select
Selection.ClearContents
End If
For I = 1 To 5
Range("Qty" & I).Select
Selection.ClearContents
Next
End Sub
keeps giving me an error 400. I have reinstalled xl just to see if it was related toan error I recieved earlier relating to VBE6.dll. This still works in another workbook but it doesn't work in this one. All I did was change the Range names and cleaned it up a bit.
View 9 Replies
View Related
Oct 19, 2009
I have a VB problem, and it's driving me nuts.
Whenever I run a macro on excel the Microsoft VB editor opens and shows me what the macro looks like. It also runs the macro.
Now, I would very much like it to keep running macros, but I really don't need to see my code every time. It gets really annoying, and I need to run about 40 or 50 different macros on one sheet. How can I make it go away?
PS! It's Excel 2007 for students, Norwegian version.
View 9 Replies
View Related
