Free Microsoft Excel 2013 Quick Reference

VBA: Make Excel the active application


I'm having the following problem... Anyone can help me?

I have VBA code set in Excel that is triggered on Workbook_Open. The code
opens an instance of Word and opens a .doc file to get information from it
and enters this data in the Excel file in which the code resides. It does
that for all .doc files that resides in a specific path.

The problem is the following:
The Excel file is opened from a link in a Word file. Before executing the
full procedure from the Excel file, the code asks the user to confirm he
wants to proceed. This confirmation is not brought to the foreground. Rather,
an alert sound is heard and the user needs to activate Excel to see the
question. Word stays on screen as the active application.

How can I bring Excel to the foreground right after the link is followed in
the Word file?

Thanks for your help!

Post your answer or comment

comments powered by Disqus

I have a macro that takes the contents of a column of cells, opens a
word docuemtn, searches through the word document and replaces some of
the words that I specified in Cell A1 with the contents of A2.

The only problem is that the macro makes Word the active program while
the marco runs but I would rather have word as the active program for
while the macro runs...any way that I can force this excel to be the
program the user see's? or perhaps is there a way NOT to activate word
window as the excel window is already active?

First, hello to everyone. I've been using Excel for awhile now, but am just now realizing just how in depth this tool really is. Recently, I've made a workbook that pulls some data, compares it to the previous hours value, and if it's different it generates an automated email message with some simple calculations in there. The problem I'm running into is this, the thing works great as long as Excel is the active program running. If I switch to another application, the macro stops. I'm trying to figure out a way to make the macro run, even if Excel isn't the active application. I have come across some solutions, but they all seem to involve a timer, and I'm not sure if this is the solution I'm really looking for. Is there a simpler way to go about this instead of having everything refresh on a timer?

Secondly, as of right now, I have a IF statement that is pulling just the minute from the current time. If that time is greater than 50 minutes into the hour, then that is the only time the macro is able to run. Ideally (and I've seen bits and pieces scattered), what I'd like to have happen is all the workbooks refresh at 31 minutes into the hour every hour, and then run the macro if certain conditions are met. Any ideas?

Hi All
I need to run a VBA macro in Business Objects that saves an excel file under a certain name - based upon todays date (i.e. filename&todaysdate&.xls) then opens that same file, and does a few basic formatting things to it in the background (i.e. removes all grid lines, zooms out to 85% etc).
Whenever i try to do anything like this it seems to have problems with being an active application, or also when i record a macro and use the code from that to change zoom levels, it does that with the active window, and i dont know how to make excel the active window (id prefer not to have to make it active, and have it working in the background)
Any suggestions would be brilliant.


I'm using Excel VBA to control both Excel and another application.
However, there are times when I want to control whether Excel or the
other application is the 'active' application and the one displaying on
the screen. (I pop up a user form in Excel, but the user has to click
Excel in the windows toolbar to activate it before they see the form).

How do I return control back to Excel from the other application?


I have a procedure where I scan a list of dates for a specific date and, once
found, set the found date as the active cell. The only problem is that the
specified cell is not being made active. Below is the code.

Private Sub CommandButton1_Click()
' Locate CurrentDate within StorageDates and make it the active cell
LookUpVal = Range("CurrentDate")
On Error Resume Next
For Each c In Worksheets("Storage").Range("StorageDates")
If c.Value = LookUpVal Then
Exit For
End If
Debug.Print "c.Address = " & c.Address
Debug.Print "-------------------------"

' Copy temperatures variables & forecast MW to adjacent columns
ActiveCell.Offset(0, 1).Value = Range("MainAvgTemp")
ActiveCell.Offset(0, 2).Value = Range("MainMaxTemp")
ActiveCell.Offset(0, 3).Value = Range("MainMaxDewPt")
ActiveCell.Offset(0, 4).Value = Range("MainForecast")

End Sub

The sub works from a command button on the first worksheet named Main. I
get the same results whether executing it from the button or from directly
inside of VBA. The range names were defined in the spreadsheet. Everything
seems to work---almost. CurrentDate is retrieved from the Main worksheet and
passed to the do loop (tested this). The correct date is found in the range
StorageDates (reported out by the debug.print lines. The address of the
specified cell is stored in c.Address as it suppose to be. However, the
c.Address.Select doesn't activate the specified cell. Whenever I execute
this procedure, I'm left in the Storage worksheet (as I should be) but the
active cell is the same as when I left the sheet. The four lines outputting
various cells from the Main worksheet to the Storage worksheet work just
fine, putting the data in the cells to the right of that cell.

I've tried moving the c.Address.Select outside of the loop. Same result.

What am I doing wrong and how can I set the active cell?

hi. i would like to create 2 codes here and have bunch of questions in delevoping them cause i am a beginner. can someone help me here?

one is IF cell II4>II8, then the active cell is in IE1 if not place the active cell in IC11. another code is place the active cell below a cell that has * mark (or any mark i can modify mine subject to vba code convenient) in a range of cell given . that's all. Please help. Thanks.


I am working on a spreadsheet project that involves the use of several individual files that that will all be very similar. (ie once I get the "Master" completed, I can taylor each to it's specifics...

I also have a section in this file that will copy and paste special (value) to a file I have named "Database".

I have written the following code, and am hoping someone can help me with respect to setting the active file.

Specifically the following line of code:

Windows("Test 19 Only numbers Subs.xlsm").Activate - This is the file that "writes" to "database". However I am hoping to find a "generic" way of making this the Active file. As there will be about 75 of them when I am done and I would rather not have to hardcode a file name into the code.

Thank you and Regards

Sub Macro1()
Dim DestWB As Workbook
Dim SourceWB As Workbook
Dim NowFile As String
Dim Lcount As Long
Dim i As Long
Lcount = Range("Sheet4!B56") - Range("Sheet4!B55")
Range("Sheet4!B56") = Range("Sheet4!B55")
Application.ScreenUpdating = False
Set DestWB = Workbooks.Open("C:UsersRandyDocumentsDatabase.xlsm")
For i = 1 To Lcount
  Range("=indirect(Sheet2!A1)").PasteSpecial Paste:=xlPasteValues
  Windows("Test 19 Only numbers Subs.xlsm").Activate
  Range("Sheet4!B55") = Range("Sheet4!B55") + 1
  Range("Sheet4!B56") = Range("Sheet4!B55")
Next i
DestWB.Close True
End Sub


I tried to make the topic as clear as possible, but in trying to do so I probably made it vague..

Here is the deal. The spreadsheet with VBA sets up a few ini / txt type files required for another application.exe that people run. I want to make it hard for the users to know what is inside these ini files. I had an idea that is partially flawed:

- spreadsheet sets up the ini files and writes them to disk (I can do that)
- spreadsheet writes and runs a .BAT file that starts Application.EXE and after that removes the ini files (I think I can do that)

This is almost good enough, but the files only get deleted when application.exe ends and the BAT file continues. Users might Alt Tab out of application.exe and view the INI files which I would rather not have!

Would there be a way for VBA or BAT files to see that application.exe is no longer the active program and act accordingly? Since the users start application.exe from a macro in Excel, that would probably mean that Alt -Tab makes Excel the active program again which might be used to run a macro?

Any thoughts more than welcome!


I have found some good solutions to my problem, but not one that fits
the bill exactly.

I'm looking to format the active cell with a yellow background, but I
need to ensure that the cell's format previous to it being active
remains as it was after the cell is no longer active. All the solutions
I have seen remove the background after the cell is no longer active.

So, if I have a cell with a black background (for instance) and I make
it the active cell the cell will have a yellow background. After I
leave that cell, I need it to return to it's previous state - black
background, not none.

I have tried this code...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal Target As Excel.Range)
Static OldRange As Range
On Error Resume Next
Target.Interior.ColorIndex = 6 ' yellow - change as needed
OldRange.Interior.ColorIndex = xlColorIndexNone
Set OldRange = Target

End Sub

....and I think the answer may lie here "OldRange.Interior.ColorIndex =

Any help would be greatly appreciated. Thank you in advance!


Hello, I'm actually a new poster. I did some searching on previous
posts and couldn't find an answer to my problem. The broad scope of
what I'm trying to do is in an excel file, to be able to push a button
that will open the open file dialog box and then once that file is
open, copy the information on a tab and paste it onto a tab from the
original file. I've been able to do most of this. I've used the
sendkeys feature to prompt the open file dialog box. However, the
problem that I'm running into is that once the new file opens, I can't
figure out how to make that the active workbook. It keeps on referring
to the original file, which is the active workbook. Part of the reason
this is a problem is because the filename and path can be different
every time this is used, so I can't delcare the specific file name in
the code and activate it from there. It needs to be flexible and
that's why I am accessing the file through the open file dialog box.

Any help would be greatly appreciated.



I have the following code in an excel spreadsheet to send email. The sending
of the email works fine but it does not make excel the active window after
sending. Is there a way to make excel active again so I can run other code
without having to manually activate excel.

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Machine Hours - Labor"
.Body = ""
.Attachments.Add ("epfilesrvas400downloadsmachine-labormachine
hours - labor.xls")
End With

Set OutMail = Nothing
Set OutApp = Nothing

After making a selection within ComBoBox7 I would like to post data to the
row selected using Commandbutton1. Cell $a$4 contains the list index address

Code should look something like this

Private Sub CommandButton1_Click()
Dim rngcell As Excel.Range
If TypeName(ActiveSheet) = "Worksheet" Then
Set rngcell = ActiveSheet.Range("$A$4")
'Cell $A$4 contains a cell address
'make rngcell the active cell
End If
ActiveCell.Offset(0, 3) = TextBox13.Value
End Sub

Jim O

I am trying to get VBA to use a chart template instead of the 3DPie it is currently using. I am also wanting it to reference source data on the active worksheet instead of coding it as part of the macro.

Right now, this is what I have been able to cobble together but I am having to manually change the chart type and the source range.

        ActiveChart.ChartType = xl3DPie
        ActiveChart.SetSourceData Source:=Range("'On Your Six 032212'!$AA$13:$AB$15")
Any help would be greatly appreciated.



I am facing a very difficult problem with Excel 2003 under windows XP:

My vba sub calls a dll subroutine. Thus I use declaration: Private Declare Function myFunction Lib "c:u32.dll" in the vba module.
When I am calling my vba sub in excel sheet, everyting works fine.
The problem is when I close the spreadsheet, I get the message Microsoft Excel encountered a problem and needs to close...:

Error signature:
AppName: excel.exe AppVer: 11.0.6560.0 AppStamp:4296b6f2
ModName: ntdll.dll ModVer: 5.1.2600.2180 ModStamp:41109627
fDebug: 0 Offset: 0000eae0

Sometimes, this error comes also randomly before closing the sheet.

At first I believed that there was a problem in the dll but it is not the case: if I call GetSystemMetrics in user32.dll the same problem occurs: here is the vba code raising the message at sheet closure (c:u32.dll is the same file as c:windowssystem32user32.dll").

Private Declare Function GetSystemMetrics Lib "c:u32.dll"
(ByVal nIndex As Long) As Long
Sub AfficherResolution()
Largeur = GetSystemMetrics(0)
Hauteur = GetSystemMetrics(1)
MsgBox "La résolution de votre écran est de " & Largeur & " par " & Hauteur
Exit Sub
End Sub
Some additionnal remarks:
- when I replace c:user32.dll by user32.dll only, the problem disappears.
- when I put the u32.dll file in c:windowssystem32 and remove 'c:' in front of u32.dll in the vba code, the problem is still here.
- after call of the vba sub (which calls c:u32.dll) and before closure of the sheet, the file c:u32.dll cannot be deleted (acces denied) it is sill open.

Can anybody help me ?

Thanks a lot for your help.

My config : excel 2003 sp2, windows XP Pro SP2.

When using a macro, once in awhile I want to paste to a sheet that is not the active sheet within a workbook (and within another workbook as well). For instance, if I'm in Sheet1 and want to paste something from Sheet2 to Sheet3 I already know that it will work to write:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The question is, is there a way to paste to Sheet2 without having to make Sheet2 the active sheet? In addition, how do code it if I want to copy and paste to a different workbook?


I have a from with three different textboxs, I want the textboxs to be populated by a calendar. I am trying to figure out how to let the calendar know which textbox to put the date into.

I can not figure out how to pass the textbox name as the frmCalendar.Show is used to open the calendar.

So I was thinking to have the calendar populate the active textbox. but I have not figure how to set the text box active and have the calendar recognize it.

    Dim Thedate As String 
    Thedate = Format((Calendar1.Value), "yyyy-mm-dd;@") 
    Graph_data.ActiveControl.Text = Thedate 
    Unload Me 
End Sub 

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

I have a macro that does things in Lotus Notes using its front end. The last thing the macro does with the Notes app is to close a document and discard changes. The discard changes is done with send keys to close a dialog (anyone know a better way?). This action leaves Notes as the active app for the user, even though the macro finishes doing it thing in Excel. What command do I need to put in the macro to make Excel the active app for the user (equivalent to clicking the Excel tab).

I've got a macro that I want to run only on a single column, but that column might be different on different worksheets. I've got the code to select the cell that contains the correct data (and actually make it the active cell), but I can't seem to move from that to selecting the entire column it is in, so that my Cells.Replace commands only run on that selection.

Can anyone help?

Thanks, and once again my apologies for being so basic. If the boss would just *pay* for a pro, we wouldn't have these difficulties. . . .


Using a dual processor machine running windows 2000 using excel 2000, I run a
vba script that takes a while to complete. The task was taking exceptionally
long to complete, so I looked at the task manager to see how much CPU power
excel was using.

When the VBA editor is the active window, excel uses 50% of the processing
power (1 full processor), however, as soon as another non excel window is
activated, the cpu usage for excel drops to 0% (ie it stops running).

How do I get excel to run VBA scripts when it is NOT the active application?

Thank you

Hello Newsgroup,

Went searching for this and haven't found it anywhere so I thought I'd post
this for the benefit of all Excel developers.

Coming from a VB (for Windows), C++ background and now C# (and Excel
developer for many years and moons now), I've always loved the use of
collections. The main (and only) thing I had against VBA (for Excel) is that
I haven't ever been able to create my own custom collections for (obviously)
my custom classes. For all those Excel-VBA developers out there who would
very much like to make your own custom collections, here's a work around.

From the VBA IDE in Excel make shore that OLE Automation is selected in
references, it is by default anyway, it's just that paramount. Create a
class for instance called

CMyClasses, and obviously the object class for this collection

Open up your object browser, right click somewhere and show hidden members.

Construct CMyClasses (very) basically as follows:

=====The Collection Class==========================
Private mcolYetAnotherCollection As VBA.Collection

Public Property Get NewEnum() As stdole.IUnknown

Set NewEnum = mcolYetAnotherCollection.[_NewEnum]
End Property

Private Sub Class_Initialize()

Set mcolYetAnotherCollection = New VBA.Collection
End Sub

Private Sub Class_Terminate()

Set mcolYetAnotherCollection = Nothing
End Sub

'And the rest of the Add, Item, Remove, Count functions-properties...

Take note of **Public Property Get NewEnum() As stdole.IUnknown** property.

Export your collection class(es) as typical *.cls files. Open these *.cls
files in a text file editor that will easily open them, Locate your NewEnum
properties and right under the function's name type the following;

Attribute NewEnum.VB_UserMemId = -4

so it all looks something like this;

Public Property Get NewEnum() As stdole.IUnknown
Attribute NewEnum.VB_UserMemId = -4

Set NewEnum = mcolYetAnotherCollection.[_NewEnum]
End Property

Exactly as it is. Save the file and import it back into your Workbook
development projects. For those of you from VB 6 backgrounds will know
what's going on as the Object Browser in VB 6.0 allows the setting of the
attribute for this function contained in custom collection classes. You'll
quickly notice that you have a custom collection of your very own and, such
things like...

For Each myClass In myClasses

MsgBox myClass.TheRestOfIt
Next myClass

....are indeed possible.

For anyone out there that knows VBA better than they know themselves I'd
like to ask;

1) Is there anything I haven't considered or more realistically, out right
don't know why I should not develop with this in mind given any limitation
in VBA for the reason attribute settings for class members isn't possible?

2) Would this be a legal infringement to Microsoft's disfavour since I have
made an alteration to it's development language? As I'm about to take this
to work and my boss will kiss me 6x10^56 times as where looking at
developing the backend to our Excel applications in a different language in
order to achieve collection classes in MS Excel 2003.

Thanks you very much Newsgroup(s) hope this helps some of you and I'm able
to do this.

- Metamexcel

Hello and thank you for your time, I scraped together some VBA code that imports several Access tables into Excel. The code works perfectly, it loads 12 worksheets with the contents of 12 tables. It does not matter if the MDB is open or closed, the code works either way (nice!). After I run the code, the MDB opens in read only mode. If I close the Excel workbook (which is host to the macro) then the MDB opens normally. Here's my question: How can I close the ODBC link (with VBA) without closing the Excel workbook? Below is an except of the code I scraped together with the help of the macro recorder (code between EZ3) and code that a co workhed provided (see code between EZ2).

Any help would be greatly appreciated. Below is an except of code used to establish ODBC connection and to load a worksheet.

Fetch data
Application.StatusBar = "Fetching data from " & GSN & " table..."

'Reset wrksht
EZ1 = 1: Sheets(GSN).Select: EZ1 = 0

'This block courtesy of Robert.....
EZ2 = 1
   Debug.Print PathAndFilename;
   strConnection = "OLEDB;" & _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Password=""""; User ID=Admin;" & _
        "Data Source=" & PathAndFilename & ";" & _
        "Mode=Share Deny Write;" & _
        "Extended Properties="""";" & _
        "Jet OLEDB:System database="""";" & _
        "Jet OLEDB:Registry Path="""";" & _
        "Jet OLEDB:Database Password="""";" & _
        "Jet OLEDB:Engine Type=5;" & _
        "Jet OLEDB:Database Locking Mode=0;" & _
        "Jet OLEDB:Global Partial Bulk Ops=2;" & _
        "Jet OLEDB:Global Bulk Transactions=1;" & _
        "Jet OLEDB:New Database Password="""";" & _
        "Jet OLEDB:Create System Database=False;" & _
        "Jet OLEDB:Encrypt Database=False;" & _
        "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
        "Jet OLEDB:Compact Without Replica Repair=False;" & _
        "Jet OLEDB:SFP=False"
EZ2 = 0

'Refresh BackgroundQuery. This block courtesy of macro generator.....
EZ3 = 1
Debug.Print strConnection
    With ActiveSheet.QueryTables.Add(Connection:=strConnection, Destination:=Range("A1"))
        .CommandType = xlCmdTable
        .CommandText = Array(GSN)
        .Name = "787_AI"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = PathAndFilename
        .Refresh BackgroundQuery:=False
    End With
Sheets("MacroDashboard").Select: Cells(NAL, 3) = "Access table '" & GSN & "' has been
successfully imported.": NAL = NAL + 1
EZ3 = 0


I'm a newbie to VBA and just wanted to ask if it can do something before I look into using it in more detail.

I want to create a Wizard type thing to produce a report in Excel.
The report is made up from another report that is made from another software package.

Can I pull things through into my VBA wizard, like the login screen for the other (non Microsoft) software application, rather then getting VBA to open the application itself?

Hope that makes sense. Any advise, or assistance would be greatly appreciated.


Hi all, here is what I'm trying to accomplish:

1) automatically check the active row to see if it contains specific text (i dont' care if there is other stuff in the cell or cells), such as "truck 1 blahblahblah".

2) if the row does have a cell containing that text, then I want it to copy ALL the cells in the active row EXCEPT Column A (which contains the date), and then paste them twice, one 21 rows down from the active row, and one 35 rows down from the active row.

3) Once copied and pasted, modify the pasted cells slightly. The first pasted cells need to say Truck 2 blahblahblah in Column C and be in blue font (instead of the original Truck 1 blahblahblah in Green Font), the second needs to say Truck 3 blahblahblah and be in yellow font. Everything else that was pasted will be the same EXCEPT they will be in blue or yellow font.

Here is the code i have so far, for some reason i keep getting error 468 from excel 2007.

Public Sub CheckActiveCell() 'This routine checks the active cell (in  the current row you are typing in) to see if it
contains the desired  text "Truck 1 Trellis & Interior" and desired font color (Green).
     'If it does, then it will call (activate) the subroutine CopyAndPasteRow()
    Application.EnableEvents = True 
    If ActiveCell.Value = "Truck 1 Trellis & Interior" Then 'The  cell may have other strings of characters in it, but we are
checking to  see if the text "Truck 1 Trellis & Interior" is in it, we want it  to give us a hit regardless if there is extra
stuff in the cell besides  "Truck 1 Trellis & Interior".
    End If 
    Call CopyAndPasteRow 
End Sub 
Sub CopyAndPasteRow() 'This routine copies Columns B,C,D,E, and F (NOT  COLUMN A THOUGH, OTHERWISE THE DATE OF SHIPMENT WILL
BE CHANGED) in the  same row as the active row, and pastes them into 2 different locations,  one which is 21 rows down from
the active cells row, the next 35 rows  down from active cells row (this pastes the active row 3 weeks after and  5 weeks
     'After it has copied the cells, it calls on subroutine ModifyFirstPastedCells()
    NextRow = Worksheets("Sheet1").ActiveCell.Row + 21 
    Cells(ActiveCell.Row, 1).Resize(2, 6).Copy _ 
    Call ModifyFirstPastedCells 
End Sub 
Public Sub CopyAndPasteSecondRow() 
    SecondRow = Worksheets("Sheet1").ActiveCell.Row + 14 
    Cells(ActiceCell.Row, 1).Resize(2, 6).Copy _ 
    Call ModifySecondPastedCells 
End Sub 
Public Sub ModifyFirstPastedCells() ' This routine changes the cells  that were pasted 35 rows down from active row slightly.
First it changes  the font color (from green to yellow), then it changes the text in  Column C to say "Truck 2 Trim & Beams"
instead of "Truck 1 Trellis  & Interior"
     ' After modifying, it calls on ModifyFirstPastedCells()
    If Cells(ActiveCell.Row, 3).Value = "Truck 1 Trellis & Interior" Then 
        Cells(ActiveCell.Row, 3).Value = "Truck 2 Trim & Beams" 
        Cells(ActiveCell.Row, 6).Font.Color = vbBlue 
        Call CopyAndPasteSecondRow 
    End If 
End Sub 
Public Sub ModifySecondPastedCells() ' This routine changes the cells  that were pasted 21 rows down from active row
slightly. First it changes  the font color (from green to blue), then it changes the text in Column  C to say "Truck 3
Cabinets" instead of "Truck 1 Trellis & Interior"
    If Cells(ActiveCell.Row, 3).Value = "Truck 1 Trellis & Interior" Then 
        Cells(ActiveCell.Row, 3).Value = "Truck 3 Cabinets" 
        Cells(ActiveCell.Row, 6).Font.Color = vbYellow 
    End If 
End Sub 

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

Any ideas? Thanks in advance,


I know that VBA code can use alt-F11 with SendKeys to toggle between Excel and the VBE, but is there a way of detecting at runtime which of the two is the active window?

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