Launching Macro Recorder Using VBA
Jun 9, 2009
I hide all Excel standard tabs and thus need a button on custom tab to record a macro. (I did this successfully with custom menu in Excel 2003.) In 2007 ribbon, I've reused the code for a button's OnAction, now going to callback that runs:
Application.CommandBars.ExecuteMso ("MacroRecord")
In Excel 2003, the stop button appeared automatically for me. But in Excel 2007, with hiding standard tabs, etc., I see no way to stop recording, other than running my button to return the Developer's tab, where the Record Macro button has changed to Stop Macro.
I'd like to either add a second button to my ribbon to stop recording the macro (but I can't find an idMso to use in a callback) or have my Record button change to Stop button, like Excel does. But I can't come up with correct code.
View 4 Replies
ADVERTISEMENT
Dec 17, 2008
I have a macro that launches word or excel files if the file address (e.g. C:Documents and SettingsHenryDesktop est.xls) is typed into cell A1 by looking at the last 3 letters of the file name.
I cannot find a way to enable it to launch pdf files as well (e.g. C:Documents and SettingsHenryDesktop est.pdf)
View 12 Replies
View Related
Jun 10, 2009
I used macro recorder, and this code was the result. Is there a way to shorten this code up? The more I use macro recorder, the more I'm beginning to realize that the code generated from macro recorder can be very sloppy.
View 8 Replies
View Related
Jul 7, 2009
Have recorded a Macro which is doing a great job, but when i try to run the same macro from other pc its not running,pops an search error.
View 9 Replies
View Related
Nov 15, 2007
Sub test12()
Set wbABC = Workbooks.Open("C:ABCwithMacro.xls")
Call abcMacro 'this macro is stored in ABCwithMacro.xls
'error - sub or function not defined on 'call abcMacro'
End Sub
View 9 Replies
View Related
Jun 22, 2006
Is it possible to use the Macro recorder in Excel to store user variables that they put in through a UserForm? So say when a textbox on a userform is changed, it records a macro of what is put into the textbox, which it can then call to retrieve the value.
View 4 Replies
View Related
Jul 25, 2006
The way i have been creating macros is by going to the tools menu.....macro....then..... record new macro.
I have a file which I have re-formatted using a macro as described above however because i receives files every month to do updates every time i open a new file and try to perform that same macro it either wont work or it wont format the correct rows.......is VBA the solution to this????
View 9 Replies
View Related
Sep 30, 2008
I used the macro recorder to create a pivot table, but to verfity that it works.
I get a "Add fields method of Pivot Table class failed"..
and this was highlighted:
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Warehouse" _
, "Product", "Description", "Data")
View 9 Replies
View Related
Sep 6, 2013
I have a macro to sort a very large list of materials by a variety of conditions.
The macro runs automatically when a cell on the master list spread sheet is selected and using If/ElseIf it sorts based on what cell they have selected.
Example
If ActiveCell = "ALUMINIZED" Then
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=2
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=4
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=1, Criteria1:= _"Aluminized"
Which is working perfectly. The problem I am having is that some of the cells are two lines so it looks like this:
BE
BEARINGS
I don't know how to get it to launch based on that cell content because of the line break in there. Can I make it launch based on the first two characters only?
View 5 Replies
View Related
Aug 24, 2007
I have recorded a simple macro ( copying a cell, and then pasting the formula into various others), and I get the following error
Compile Error: Argument Not Optional
I have highlighted where the error first happens
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/08/2007 by Michael Traynor
'
'
Range("K7:K8").Select
Selection.Copy
Range("K167:AJ168").Select
Range("AJ167").Activate
Range("K167:AJ168,K175:AJ176").Select
Range("AJ175").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184").Select
Range("AJ183").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184,K191:AJ192").Select
As I've said I didn't write this, it was recorded from Excel.
View 7 Replies
View Related
Sep 4, 2007
I've created VBA code in Access 2007 to create a column chart in Excel, and it is working. I need to change the rotation on my category labels to 270 degrees vertical. When I record a macro on the chart in Excel 2007. I only receive the following
Sub chartingRotation()
'
' chartingRotation Macro
'
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub
How can I get the macro recorder to record changes to chart properties?
View 4 Replies
View Related
Oct 11, 2012
I have used the macro recorder to generate the select range and clear part of the following macro.
Private Sub CommandButton2_Click()
Dim test As Worksheet
Sheets("Industry").Copy After:=Sheets(Sheets.Count)
[Code]....
I get the runtime error 1004 - Select method of range class failed.
I have used similar script in other macros without error.
View 2 Replies
View Related
Dec 6, 2013
Had 2003 now 2013. Trying to get the macro to select different printers for different doccuments. 2013 macro recorder does not record any printer info or path, all printers show the same wording just application print...
How can I get the recorder to acurrately record and diferentiate printers?
View 2 Replies
View Related
Oct 11, 2011
I am using office 2011 for MAC, and am trying to create a macro that changes the color of the selected cell(s).
if I start recording the macro
click the fill color button pull down and change the color on the edit section of the ribbon
Stop the macro
No code is recorded as below but the cell did change color to whatever I selected.
Sub Macro13()
'
' Macro13 Macro
'
'
End Sub
[code].....
The colorIndex is automatic no matter what color I choose.
When I run the macro there is no change to the selected cell, but the border is destroyed.
View 2 Replies
View Related
Sep 9, 2007
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code.
1) Macro Recorder Code to Copy One Sheet to Another
Sheets("Data Apple").Select
Cells.Select
Selection.Copy
Sheets("Banana").Select
Cells.Select
ActiveSheet.Paste
2) Macro Recorder Code for Replacement Purposes......................
View 9 Replies
View Related
Apr 14, 2006
I have a worksheet that is a graphical display of a call floor with its cubicles. I am able to pop up a box with many details about the workstation in the cubicle (see Display pop-up box when cell selected) for more details, and would now like to go further with this application.
Our practice is to use VNC to review the end users' screens when they call us for assistance; given that I know the machine name and IP address, how can I start a VNC browsing session for the target machine?
View 5 Replies
View Related
Mar 14, 2007
My company uses database software called Business Objects. At the moment few people are using the program and its reports as they can't find the key ones or workout how to run them correctly. As everyone is comfortable with excel. I am creating a series of forms to guide my users to the key reports. I like this method because it is very user friendly and I can add lots of descriptions. Unfortunately my programming knowledge is limited to excel so I can't write a standalone program as you might suggest I do.
The problem:
I want to launch the non-excel files using VBA. For example, a user will click on a "Customer" button to see a second form containing a list of customer reports. The user will then click on the button containing the name of the report they want to launch. I want the file to open. I tried using hyperlinks in an excel spreadsheet which works fine for opening the non-excel files, but it isn’t nearly as user-friendly or interactive.
View 4 Replies
View Related
Feb 21, 2007
I have recorded a macro that will copy and paste information from text files to two separate sheets in a workbook. Previous versions of this have worked fine but now I have a "subscript out of range problem" for this line: Sheets("Raakadata").Select
I'm doing this project to someone else and the weird thing is I don't see this error in my computer but they have it in theirs. The workbook has been recently renamed to oljy_vedesta.xls but it should be correct in the code. The syntax should be fine as macro recorder has been used but still this error comes up with the another computer (all the required files are there).
Sub Tulosten_haku()
ChDir "C:Makro"
Workbooks.OpenText FileName:="C:MakroOLJY_VEDESTA_QC.TXT", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:= Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1))
Workbooks.OpenText FileName:="C:MakroOLJY_VEDESTA.TXT", Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1))..........................
View 2 Replies
View Related
Mar 3, 2014
I have a non Microsoft program file that I can open in excel. I usually make changes to the file; save it to its native format, then open up the non Microsoft program and open that file that I just edited in excel.
If I had to simplify it, using notepad as an example.
I open the TXT in excel and make changes.
Then Save the TXT in a folder location.
Open Notepad.
Browse to the folder Location.
Open the edited file.
I did not know if there was a quick VBA that I could use to get this to work?
View 9 Replies
View Related
Mar 13, 2014
I have a sub procedure that launches a hyperlink, but i have some conditionality so that if a cell (J3) is blank it pops up a message letting the user know they need to populate cell (J3). My problem is that if cell J3 is blank it pops of the message, but it still launches the link.
Sub NEWShyperlink()
Dim s As String
If Range("J3").Value = "" Then
MsgBox "Make sure you have entered the ticker symbol into cell J3!", vbExclamation, "Can not get News/Press Releases"
[Code] .....
View 6 Replies
View Related
Jan 30, 2007
I have a list of URLs and I wrote a small VBA code to launch them when the user clicks on a button, however, it launches in Internet Explorer. Is there any way to get it to launch in Firefox instead?
View 2 Replies
View Related
Jul 7, 2007
how to launch windows explorer from a path that resides in a cell using the code below.
Sub LaunchWindowsExplorer()
Dim PID As Double
Dim strRootPath As String
Const strExpExe = "explorer.exe"
Const strArg = " " '" /e,/root, "
'// Change rootpath here
strRootPath = "J:MyDirectoryWilliams,Bob 7264"
PID = Shell(strExpExe & strArg & strRootPath, 1)
End Sub
The problem I have is that once I have done this a number of times (probably somewhere between 80-100 times then I notice windows explorer starts to play up as if running out of memory. A reboot soon fixes that, however, I was wondering if there is anything I can add to the code to keep it from hogging up memory...if that is even possible.
When I say 80 - 100 times, I don't mean opening up 80 - 100 instances of windows explorer. I mean launching windows explorer, moving some files then closing that instance of windows explorer. Then coming back into excel and repeating that process.
View 9 Replies
View Related
Aug 16, 2012
Each time I click on an Excel file the worksheet will open up within an existing Excel worksheet that I have open. This is frustrating when you are working with two monitors or screens (laptop screen and additional monitor) and want to view each worksheet separately in separate screens. What I end up doing, which is not efficient, is I'll open the second worksheet which will open up in an existing worksheet that I have open and then I'll close it, and then launch a separate Excel worksheet and pull open the recent file and I'll have two open worksheets (not within same Excel worksheet I already have open). This allows me to grab and pull one worksheet in a separate screen and leave the other as I need both open on two monitors.
Question: is there a way to default Excel when opening or double clicking an Excel file to automatically open in a separate instance of Excel and not in an existing open worksheet?
View 2 Replies
View Related
May 27, 2007
I am trying to launch windows explorer from within excel vba. The routine below is borrowed from another post on this site and modified for my purposes.
Sub LaunchWindowsExplorer()
Dim PID As Double
Dim strRootPath As String
Const strExpExe = "explorer.exe"
Const strArg = " " '" /e,/root, "
'// Change rootpath here
strRootPath = "J:MyDirectoryWilliams,Bob 7264" ..............
View 9 Replies
View Related
Oct 16, 2009
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
View 10 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Jan 15, 2014
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
View 9 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
Jan 26, 2009
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
View 14 Replies
View Related
