Free Microsoft Excel 2013 Quick Reference

How can a macro end another running Results

Can you help me?

I have a workbook containing approx 24 sheets.
All sheets are formatted the same way.
I want to create a single table that contains relevant info from all of the other worksheets. Note - the information in these sheets is not in tabular form, but are laid out in the same way - i.e. data is positionally identical.

In searching the forum I found the following macro:

Sub consolidate()
Dim i As Integer
For i = 2 To Sheets.Count
Sheets("Sheet1").Cells(i, 1) = Sheets(i).Range("F31")
Next i
End Sub

This works the first time I run it, but I need to select another cell to consolidate, and re-run the macro to create a new field in column B of Sheet1.

Does anyone know how to make this work?


HELP! :o
I have an excel workbook with several sheets that contain performance data for a changing
number of empolyees. Each employee remains in the same row and all the data on the following sheets is lined up. The data grows horizontally (each week) and the employees grow vertically.
I created a sheet with graphs pertaining to each performance metric (graph sheet) and related to an employee.
My first attempt was to have an extra sheet for each employee but excel ran out of characters. Also I wanted to have the option to change the
date range, which I had to do manually (a ton of work).
So I added another sheet that would automate that process.
On this sheet I wanted to have the option of choosing a specific employee and a specific date range for each performance metric.
I figured out how to operate with drop downs and extract with =CONCATENATE("=attend!$",C22,"$",D4,":$",C24,"$",D4)
the actual string =attend!$J$18:$Q$18
that is required for the chart values and xlabel.

That's where I am with my little macro

Sub AgentData()
Dim label As Variant
Dim CSATval As Variant
Dim CSATlabel As Variant

'name of the agent
label = ActiveSheet.Range("b3").Value

'graphs variables: values and x labels

CSATval = ActiveSheet.Range("C18").Value
CSATlabel = ActiveSheet.Range("C19").Value

'copy the template and changing the name of the sheet
ActiveSheet.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = label
'first chart: CSAT
ActiveSheet.ChartObjects("Chart 6").Activate
'here starts the grief
ActiveChart.SeriesCollection(1).XValues = CSATval
ActiveChart.SeriesCollection(1).Values = CSATlabel


End Sub

and when it runs it can't change the value for the x and the label for the chart.
"unable to set the xvalues property of the series class"
Can somebody please help me with that?

This message has 3 parts - *INTRODUCTION - *READ CODE - *WRITE CODE

I have had a long-held belief that Excel/VBA methodology is very suited to solving this problem. I have investigated several possible methods noted below and picked one that gives a "quick win". I offer my results so far as a step towards the goal, as well as giving an example of how to manipulate a non-MS Office application using VBA. In this case Windows Explorer - I have used similar code on corporate applications like Oracle and SAP. My 'Write' method uses Sendkeys. Luckily I have been able to do the job without having to use code to simulate mouse functions or using API calls to simulate key presses. Ideally I would like to use something more stable, but there is the bonus that it is simple, as well as changing WMA and both versions of MP3 tag (see below) if present. Perhaps on reading this someone else may have a better method.

Being a ballroom dancer I have a large collection of CDs as well as software to rip to hard drive and enhance the sound quality. Many are quite old. A big problem has been to get a consistent view of the file properties - especially Genre, (eg. Waltz, Foxtrot .. etc.) which, from my own CD burning or external sources, is missing, or incorrect from using the now obsolete ID3v1 tag standard list. Applications such as Windows Media Player and RealPlayer allow functionality to edit tags but become very tiresome when it comes to making bulk changes - such as after burning a new CD. When viewing properties of the same file in various other applications they often show things like Title & Artist switched, and Genre not at all. I have tried software to change MP3 file tags, but find them over-complicated, confusing, and difficult to make the bulk changes I need. With Windows Explorer we can only change 8 properties - but I find these sufficient.

My method is :-
1. READ : Run one macro to put data into a worksheet.....
2. MAKE CHANGES : Make manual changes to the Excel worksheet in the normal way....
3. WRITE : Run another macro to read the worksheet and update the file properties in Explorer.
Part 1 is very simple and robust. Part 3 is difficult because when we use Sendkeys to mimic keyboard entry the code runs too fast to allow time for things to happen on screen, so we have to put Wait statements *depending on how fast the computer runs*. So Slower is better - up to a point.

Properties are added to MP3 files by using a "Tag" - additional bytes of information which form part of the file. WMA files are a Microsoft invention using a similar, but different structure. Interestingly, using my code to make changes via Window Explorer updates BOTH MP3 Tag versions as well as .WMA files. I moan about Microsoft less and less.

MP3 *ID3v1* consisting of 128 bytes always at the end of the file is now 'obsolete' - despite being still in use. This is very easy to read/write using the same code as for Text Files eg. Code:
 etc. It is, however, limited to 4 text fields of 30 characters max, 'Year' =4 characters, and 'Genre' is a single character,
the Asc() code of which is a lookup to a standard list which contains 125 items - none of which is any good to me. Could have
my own lookup I suppose.

MP3 *ID3v2.3* is in the process of being superseded by ID3v2.4. The big problem here is that there are several different versions and the code required is extremely complicated - mainly due to the use of variable length fields. So we not only have to find the property, but read the field length before getting the field contents. Writing would need to change the coded field length. This is further complicated by there being the option to use an "Extended Tag" - *or not* ! Version 2 tag can be at the beginning or end of the file (before ID3v1 if it exists) - or both. The MP3 files on my computer all seem to have both versions - v2 at the beginning and v1 at the end. It is further complicated by the ability to have User Defined fields. I see some of my files have a user defined 'Genre' field, despite having the standard one 'TCON' too. The tag also needs a form of "encryption" so that the mp3 player does not treat it as audio data. Visit here for detailed information .

To view an audio file in its raw state open it in a Text Editor. I use 'TexPad' which is very fast and gives a choice of Binary (with Text 'translation' in a column) or Text view. Notepad gives just a Text view with empty space for non-text/binary characters. There is a large number of Null characters Asc(0) in proportion to the overall file length - mainly for "future development" I believe.

I would be interested to hear of any comments, suggestions and code improvements.


Hi there, I am trying to open an access database from excel by clicking a button. I have assigned the following script and modified it for my own use as according to the microsoft kb.

Private Sub CommandButton2_Click()
'Opens Microsoft Access and the file nwind.mdb
Shell ("c:Program FilesMicrosoft OfficeOFFICE11MSACCESS.exe T:TSD - UKProjectsSteve's ProjectsT3FCRs.mdb")
'Initiates a DDE channel to Microsoft Access
Chan = DDEInitiate("MSACCESS", "system")
'Activates Microsoft Access
Application.ActivateMicrosoftApp xlMicrosoftAccess
'Runs the macro "Sample AutoExec" from the NWIND.MDB file
Application.DDEExecute Chan, "ImportData"
'Terminates the DDE channel
Application.DDETerminate Chan

End Sub The problem I have is that although this does not debug, it does not do what I want it to.

I want this to launch Access, then launch the file FCRs.mdb, then launch the macro ImportData.

What it actually IS doing is the following:


Remote Data not accessible.
To access this data Excel needs to start another application. Some legitimate applications on your computer could be used maliciously to spread viruses or damage your computer. Only click yes id you trust the source of this workbook and you want to let the workbook start the application.
Start application 'MSACCESS.EXE'?

Even though access has by now already started anyway, I then click yes.


Microsoft Office Access can't find the macro 'DataInput.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.

I then click ok, knowing the macro exists, but soon realising the application has opened, and the macro is trying to open, but the file isnt opening at all.


The command line you used to start Microsoft Office Access contains an option that Microsoft Office Access doesn't recognize.
Exit and restart Microsoft Office Access using valid command line options.

I click ok, and get ERROR 3 again.... and again.


Microsoft Office Access cant find the database file 'T:TSD.mdb'
Make sure you enterred the correct path and filename.

So now at the end of the cascading messages, i eventually find what seems to be the problem. The macro couldnt be openned because the file wasnt open, because it was looking for T:TSD.mdb, when it is supposed to be looking for T:TSD - UKProjectsSteve's ProjectsT3FCRs.mdb.

My big guess is that this has something to do with spaces in names, because if i change it to T:TSD-UKProjectsSteve'sProjectsT3FCRs.mdb, i get the same problem, but with the full path name, so how do i resolve this problem?

I have the following refferences attached:

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Access 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library

Please help

Hello all;

I was looking for macro code to copy worksheets within a workbook. I use XL97 and Windows 2000. I have tried this with XL97/Win 2000 and XL2000/Win XP. No success.

I found the following code on this web page, copied it, and it does not run. The macro stops at the following line:

Sheets(LastSheet +1).Name = ThisTerr

The entire tip/macro is pasted below. Any ideas why this will not work?

Past Tip of the Week

"Stuck in NJ" asked this week's question. I am creating a workbook that contains charts of sales figures. The first worksheet corresponds to the first sales territory in the company (eg. FL01.) The worksheet's tab name (as well as cell A1) is the sales territory number FL01. The data that drives the charts is to the right(outside of the printable range that I set) and is populated from another excel workbook using VLOOKUP with the sales territory number FL01 in cell A1 as the lookup key. I need to add to the workbook 76 identical worksheets (one for each sales territory) such that each sheet's tab name and cell A1 equals successive sales territory numbers (eg. FL01, FL02, LK01, LK02.) How do I do that in an automated way?

This is easily accomplished with a VBA macro, but let me cover the tip for doing this manually. Once you have a sheet set up with formatting and print ranges, you can easily duplicate the sheet by right-clicking on the tab for that sheet. From the menu that pops up, choose Move or Copy. From the Move or Copy dialog box, select "(move to end)" and check the "Create a Copy" box, then click OK. A copy of your sheet with formatting will be added as a new worksheet in your workbook. You will usually want to rename the sheet. Right-click the new tab, choose rename, and type a meaningful name.

For the Excel macro to work, it needs a list of territories. Before you write the macro, insert a new sheet in this book named Data. In Cell A1, enter the 2nd sales territory (FL02 in your example). Continue entering all of the sales territories down column A. Do not leave any blank rows.

Insert a new macro and copy this code:

Public Sub CopyIt()
' Determine how many territories are on Data sheet
FinalRow = Range("A65000").End(xlUp).Row
' Loop through each territory on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
ThisTerr = Range("A" & x).Value
' Make a copy of Fl01 and move to end
Sheets("FL01").Copy After:=Sheets(LastSheet)
' rename the sheet and set A1 = to the territory name
Sheets(LastSheet + 1).Name = ThisTerr
Range("A1").Value = ThisTerr
Next x
End Sub

The macro makes use of the Sheets().copy command which does the same thing as clicking Move or Copy manually. Before doing each copy, I find out the number of sheets by using the Sheets.Count property. I know if there are 56 sheets and I insert a new sheet, it will be referenced as Sheets(57). I find it somewhat unpredictable to guess how Excel will name the new sheet, so I use numeric index numbers to identify the sheet. Once I rename the new sheet with "Sheets(LastSheet + 1).Name = ThisTerr", I can then switch back to my preferred method of referring to the sheet by using its name.

Note that the list of territories should not contain FL01 in the list. Trying to make a rename a copy of FL01 with the same name will cause an error in the macro.

Hello all,

I was wondering if anyone has experience with the BlueZone terminal
emulation program. Some companies have made the switch from Extra! to
BZ. Anyway, I have a question as to how the OpenSession command works
in an Excel macro (or is supposed to work). And here's my problem...

After I've established the host object with:

Set Host = CreateObject("BZWhll.Whllobj")

I then am working with up to FOUR different sessions at the company I
work for. We'll call these sessions A, B, X, and Y. Typically though,
only two of these four are open at any one time. Should the user not
have ANY sessions open when the macro runs, the following commands then
open new session windows and name them accordingly:

Host.OpenSession 0, 1, "ASESSION", 30, 1
Host.OpenSession 0, 2, "BSESSION", 30, 1

Where the variables above are Host.OpenSession [SessionTypeVal],
[SessionIdentifierVal], ["ConfigFileStr"], [TimeoutVal],

SessionTypeVal - 0 - Mainframe; 1 - iSeries
SessionIdentifierVal - 1 for S1, 2 for S2, 3 for S3, ... , 99 for S99.
ConfigFileStr - Name of the BlueZone Configuration File.
TimeoutVal - Number of seconds before returning with error.
WaitPaintsVal - Number of screen paints before proceeding with script.

However, the problem comes in where perhaps someone ALREADY has the
sessions open on their desktop that are something else. I.E., someone
clicked on XSESSION and that got assigned to (S1), and then clicked on
YSESSION next which got assigned to (S2).

Even though in the command I specified I wanted to OpenSession 0, 1,
"ASESSION", when the macro runs, it brings up the Session 1 (S1) window
(which in this senario is XSESSION), and STOPS mid string, without
continuing to the "ASESSION" part to rename the window.

I so I guess my question is this... is there a way to tell what
sessions are currently open (if any), and if there are any open, what
the names of them are and what session number they are currently
assigned to? That way I can tell if Session 1 (S1) is what's it's
supposed to be, etc, etc.

OR, regardless of what Session numbers are open (S1, S2, S99), is there
a way to tell if the name is already assigned to one of those, so the
macro doesn't try and open a new window. Because if it does, an error
comes up that the LU / Pool Name it's trying to connect to is already
in use by another sesssion, bla bla bla.

Something maybe like (and don't laugh at this coding):

For All.Open.Sessions
If Session.Name = "DESIREDSESSION" Then
SessNum = Session.Number
OpenSession 0, 1, "DESIREDSESSION", 30, 1
End If
Next Session

OK - I know, I know - I should be using a UserForm - and I do - but sometimes, just sometimes, there are instances where I still find using Dialogsheets best. I like the fact that they retain the data typed into them, and that all data controls are effectively like 'cells' in a worksheet.

Anyway, I have a dialogsheet which contains a 'date find' function.
To do this, on part of the dialogsheet I created a 7x5 grid of TEXTboxes (not EDITboxes or labels) - the top row of which is populated with Mon, Tue, Wed etc. and the other rows with the numbers 1-28,29,30 or 31 (depending on how many days there are in the month). The month and year is specified and edited by means of a dropdown (for the month) and an editbox/spinner (for the year), which causes the 'calender' grid to re-populate and re-draw.

Each date box would have an OnAction macro attached to it, which would use the value in Application.Caller to determine which 'date' had been clicked, and this value would be passed on to a search routine.

When I first wrote this routine in Excel 97, it seemed to work nearly all the time - however I found that on occassions that these text boxes would refuse to 'get focus' - it was normally only after selecting another month and these boxes were getting re-drawn.

On successive versions, I have found that even on first call, these boxes refuse to get focus, although in certain situations (and I can't work out a pattern to how to get this) - the boxes then accept focus again. At all times, it is possible to 'tab on to' the text boxes from other controls - the focus marquee appears and pressing space will select the text box and run the OnAction event routine.

I can't seem to find a way to get the thing working consistently anymore - is there something simple that can correct this behaviour? I know it's an obsolete way of doing it, but it used to work - so it must be an internal bug to the dialogsheet component in later versions of Excel (maybe not making the transition from the .DrawingObjects. collection to the .AutoShapes. / .Shapes. collections).

BTW The reason I used text boxes rather than buttons was so that on activation the colours of each box could be changed - the way it works is that the date first clicked is set as the 'start date' and the next date clicked (if a later date) is set as the 'end date'. The range selected then highlights by changing the colour of the text boxes to indicate the date range selected.



I have basically read all the posts on similar subjects and have tried for a few days to make it work but my solution is far from good enough. So now I am asking for you help.

My project: I have a Master workbook that contains the complete sales for the entire business. This is based on input from three different departments. These three departments have their own Excel workbook that they enter information into. The four files are all located in the same directory on a network folder.

What I want to do is to automatically gather all the entries from the three slave workbooks to the master workbook whenever the macro I am trying to create is run. There is no way of knowing how many new entries each workbook will contain and they have to be added so that that they don't overwrite eachother and so that they are put in sequence after the last row in the master workbook.

Now the information to be gathered is located on the first worksheet in all the three workbooks. The destination sheet in the Master workbook is also the first worksheet in the workbook. The structure of the information that I want to copy is equal on all the worksheets meaning that one sale is entered as a new row in the sheet. It is this row that I want to copy to the mastersheet.

What makes it a bit more complicated is that I want to extract certain information from one sheet and different information from other sheets. From one sheet i want to copy the entire row and from another sheet I just want to copy certain figures like Order Number, Customer, Price and so on. Meaning that I will have a different set of what I want to copy depending on what workbook I am copying from.

Here is what I have so far, it is not working by far and I tried to aim for something simple to start with since my vba experience is limited. So far I cannot copy anything into my Destination master file.

    Dim i As Integer 
    Dim Source As Workbook 
    Dim Destination As Workbook 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.EnableEvents = False 
    On Error Resume Next 
    Set Destination = ThisWorkbook 
    With Application.FileSearch 
         'Change path to suit
        .LookIn = "C:Documents and SettingsnochhauDesktopExcelprosjekt" 
        .FileType = msoFileTypeExcelWorkbooks 
        If .Execute > 0 Then 'Workbooks in folder
            For i = 1 To .FoundFiles.Count 'Loop through all
                 'Set Paste target first
                With Destination.Sheets(1) 
                     '''''' Here I would then have to make an if to do a different selection based on what input file I have
                    Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) 
                End With 
                 'Open Souce file
                Set Source = Workbooks.Open(.FoundFiles(i)) 
                 'Copy and paste to destination
                Source.Sheets(i).Range("A1").Copy Tgt 
                 'Close source file without saving
                Source.Close False 
            Next i 
        End If 
    End With 
    On Error Goto 0 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Application.EnableEvents = True 
    Application.CutCopyMode = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So if anyone can help me I would be very grateful!

#1 Using virtual machines
As a developer you always face the problem with different versions of MS Windows as well as different versions of MS Excel. As a non-english developer you also face the problems with "international issues". The situation can sometimes be both cumbersome and complicated.

So the main question is how to deal with it? The only answer is to have access to the most frequent versions of both the operating systems and Excel.

We have several options to choose between. However, no matter which solution we decide to use we need to buy legal copies of every software we intend to use, unless the license rules explicit state otherwise.

The options we have are:
1) Using several computers. Usually we save the outdated computer(s) and use them as test-enviroments.
2) Dual boot on one cumputer, at bootup we can choose which operatingsystem we would like to run.
3) Virtual machines.

"Virtual machines" means that we have one operating system which acts as the host and where we have several operating systems as virtual guests (e.g both Windows and Linux) on one computer.
The solution requires more RAM and more hard disk space but nowadays both these two items are considered as inexpensive.

Over a year ago I decided to use virtual machines and I was a little bit skeptical in the first place but changed my opinion along the road.

In addition to the "usual" requirements: you have at least 512 MB RAM, but I highly recommend using 1 GB for best performance. Each
operating system need a minimum 4-6 GB. If You plan to use databases or other softwares that require space then you need about 8 - 12 GB available free space for each guest-operating system.

At least two software packages exist that do the job in a nice and reliable way:

vmWare Workstation 4.5

This is the #1 software for working with virtual machines, which is also reflected in the price...
vmWare works excellent with Windows and outstanding with Linux. If You plan to run a mixed enviroment, i.e both Windows and Linux then this software is the one and only.

With Linux, you should consider not using the latest version, unless it's officially supported by wmWare.

I have no problems with USB-devices, shared folders, drag & drop and accessing other network-resources. Neither with Windows or Linux as guest-operatingsystems.

Microsoft Virtual PC 2004

Virtual PC only officially support Windows and as such this limits its use with other operatingsystem like Linux. However, unofficially, Virtually PC works with most of the Linux-distribution, although not as good as vmWare does.

In general, Virtual PC is a good option, especially if you plan to only use Windows-systems and are not prepared to take a loan to get a copy of vmWare.

#2 Format textfield to currency in userforms
Below is a code-example which shows how we can easily format
a textfield's value to the local currency that is used by Windows (via the settings).

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
    With Me.TextBox1 
        If Not .Text = Empty Then 
            Cancel = CheckNumeric(TextBox1) 
            If Not Cancel Then .Value = Format(CDbl(.Text), "Currency") 
        End If 
    End With 
End Sub 
Private Function CheckNumeric(TxtBox As MSForms.TextBox) As Boolean 
    Dim stNumber As String 
    stNumber = TxtBox.Text 
    If IsNumeric(stNumber) Then 
        Select Case stNumber 
        Case Is < 0 
            MsgBox "The number must be greater then one.", vbExclamation 
            TxtBox.Text = Empty 
            CheckNumeric = True 
        Case stNumber - Int(stNumber) = 0 
            CheckNumeric = False 
        End Select 
        MsgBox "Only numerical values like 100 or 100,25 is acceptable.", vbExclamation 
        TxtBox.Text = Empty 
        CheckNumeric = True 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
#3 Kudos to Microsoft
On public forums, people usually express their disappointed about vendors & manufactures. Sometimes the critism is fair and sometimes not. But we rarely give them any credits or do we?

Microsoft have done great work over the last 1 - 2 years by providing and sharing more information and knowledge about their software. We can all benefit from it and leverage the use of their products in our daily work. In financial terms, we can express it as a higher return on investments (ROI) .

Iīm very pleased with it and I can only hope Microsoft, will in the future, continue to support the global community in this way.

The following URL are starting points and I highly recommend to visit them:
Microsoft Developers Center:
Microsoft Developers Library:
Microsoft Excel Developers (Check out the technical articles and the reference documentation):
Microsoft Knowledge Base:;EN-US;KBHOWTO
Microsoft Office:
Office Update:

#4 Amazing!
The first really large project I got involved in was in the late 80's where I delivered a solution in the old macro-language, XLM. Last week I got a call from the client who asked if I could add some additional features to it. The solution is still running on a daily basis 12 months per year.
Last time I made any changes must have been 1993 or 1994 (at least what I can remember!) so no improvements have been made over the years.

The amazing thing is that itīs still in use after more then 15 years, irrespectively of the rapid developing of technoloqy and softwares. Itīs a great feeling that a solution you have created is still in use year after year both local, nationwide and sometimes, also worldwide

The client has no intention to "upgrade" it as, "if it ain't broken don't fix it"!

The less amazing thing is I need to recapitulate the old language but thatīs another story...

#5 The End
We are, more or less, all slowly dying due to age while some of us are doing it a little bit faster for other reasons.

If you have family members, friends and/or working colleagues who are facing the later situation, give them your time and friendship as much as possible. It's the most valuable contribution we all can give no matter who we are, where we are, or how much money we have.

Finally, by living, you put yourself into a high-risk project but the benefit of living can't be measured, so turn off the d... computer(s) and take part in life!

I've read many posts in the archives and I've managed to figure out how to
disable shortcuts in various spots, but disabling all regular XL2K print
options is eluding me. I'm only being partially successful. I also can't
seem to pinpoint a reference for any code to this in the archives so far.

My question is how do I grey out the regular print icon from the toolbar as
well as the print reference in the FILE menu. The second problem deals with
my customs icons on the toolbar, as well. I do the ^p covered; it's calling
a print macro for this workbook.

Here is what I've managed to make up. I've put this in the workbook module
with the referenced macros in a regular module (though not shown here):

Private Sub Workbook_Activate()
On Error Resume Next
'Disable shortcut(s) on STANDARD toolbar, EDIT menu and cell SHORTCUT menu
With Application
.CommandBars("Standard").Controls("PRINT").Enabled = False
.CommandBars("Edit").Controls("PRINT").Enabled = False
.CommandBars("Cell").Controls("PRINT").Enabled = False
' Disable keyboard shortcuts
.OnKey "^n", ""
.OnKey "^p", ""
' enable shortcut keys to run macros
' new shortcut #1
.OnKey "^n", "AddNEWrecord"
' new shortcut #2
' enable Shift+^+R to run macro: "InsertROWS"
.OnKey "+^r", "InsertROWS"
' new shortcut #3
.OnKey "^p", "PrintSheet"
End With
End Sub
Private Sub Workbook_Deactivate()
' CommandBars disappear (whilst the workbook remains open, but another is in
On Error Resume Next
'Re-enable shortcut(s) on Standard toolbar, EDIT menu, and cell SHORTCUT
With Application
' these lines with TRUE are needed if you have ones with FALSE above
.CommandBars("Standard").Controls("NEW").Enabled = True
.CommandBars("Edit").Controls("NEW").Enabled = True
.CommandBars("Cell").Controls("NEW").Enabled = True
.CommandBars("Standard").Controls("PRINT").Enabled = True
.CommandBars("Edit").Controls("PRINT").Enabled = True
.CommandBars("Cell").Controls("PRINT").Enabled = True
' Re-enable NEW keyboard shortcut
.OnKey "^n"
' Re-enable Shift+Ctrl+R (just in case this ever exists)
.OnKey "+^r"
End With
End Sub

One of my printer toolbar icons has a print macro in the personal.xls called
"PrintCurrentPage" assigned to it, the other 2 are icons chosen from the
commands box when customizing the toolbar.

Thank you! :oD


Hopefully this makes sense:

I have a Worksheet that contains several macro's that allow the user to
insert rows, move groups of rows up/down etc.

There is only one area of the worksheet that users need to update (say
that is between rows 6 - 30). To avoid users accidently deleting a row
or inserting a row in the areas outside of rows 6 - 30, all the other
cells are locked and the worksheet is protected.

In order to run the macros, password protect is turned off in the vb
code and turned on again at the end of the sub. To ensure that the
insert row etc. is not being performed outside of the set area, the code

If Selection.Locked = True Then

Error Message


Continue with Code

This seems to work fine. A problem occurs however when the user pastes
text from Word or an email into the spreadsheet (within Rows 6-30) as
the locked cell check box becomes ticked and this stops the macros from
working correctly.

I found that if they paste special and choose text, this does not
happen, the cell remains unlocked.

Any idea why the cell locks when text is pasted from another application?

Any ideas as to another way round it?

The cells that can be updated have no background colour (ie. White),
whereas cells that contain formulas etc are pale yellow. How would I
code that so it basically says:

If Selection.Background Colour <> White Then

Error Message


Continue with Code

My ramble has now ended.



This may be a long shot but...I have a couple spreadsheet macros and thanks to the help of the fine folks on this forum they all work fine in Excel 2007 and 2010. My user received a MAC for christmas with Office for MAC 2011. None of my scripts work on his MAC. I did some research and VBA is supposed to be supported in that version.

So for starters, does anyone know some code I can add to the front end of the scripts that will determine whether it is running on a MAC or a PC so I can differentiate different segments of code for each? I would like it to work on both.

Secondly I suspect that at least two of my scripts do not work because of something to do with file/folder structure differences. Here is one of those scripts:

Sub ImportINV()
' ImportINV Macro
' Imports inventory from last month's file
  Dim sPath As String
  Dim fName As String
  Dim s As String
  Dim Wk1 As String
  Dim Wk2 As String
  Wk1 = ActiveWorkbook.Name
  s = CurDir
  sPath = ThisWorkbook.Path
  ChDir sPath
  MsgBox ("Please select the spreadsheet from the previous period that you want to import the data from.")
  fName = Application.GetOpenFilename( _
   Filefilter:="XLSM Files (*.XLSM),*.XLSM")
  ChDrive s
  ChDir s
  If LCase(fName) = "false" Then Exit Sub
    Workbooks.Open Filename:=fName
    Wk2 = ActiveWorkbook.Name
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    ActiveWorkbook.Close SaveChanges:=False
End Sub
So all this does is allow you to select another spreadsheet and it copies some of the data from that spreadsheet to the current one.

Here is an additional problem for me, currently I have no access to a MAC with Office for MAC 2011 to use for testing to figure out how to fix this. Which also unfortunately means I do not know off hand where it is failing. I am working on getting something I can use for testing and development on this but since I am not really willing to put out money to buy my own copies of OS X and Office for MAC 2011 to resolve this issue that may not happen.

If by some miracle someone who does have said operating system and Office version looks at this and knows what's wrong, I would surely appreciate a version of this code that runs on MAC. I believe I could then modify at least one of the other scripts and get it working as well.

All help or suggestions appreciated.

Hello everyone. First post, and verrrry new to VBA/macros, so please be gentle. I've been searching the forums and can't seem to find the answer I'm looking for. What I have found isn't working when I try to add it.

I appropriated a bit of code which provides an input box for the date and saves the file as that was kindly supplied to another user by Leith Ross in this thread: I made some tiny changes -- text in the input box, file path, that kind of thing) and ended up with:

Sub Button26_Click()
  Dim InputDate As Variant
  Dim FileType As String
  Dim myFileName As String
  Dim myFolder As String
    FileType = ".xls"
    myFileName = "Service Desk Report: "
    myFolder = "J:Library StatsService desk"
      InputDate = InputBox("Please enter date of Report mm/dd/yy")
      If InputDate = "" Then Exit Sub
      If Not IsDate(InputDate) Then
         MsgBox "The date you entered is not valid"
         GoTo EnterDate
      End If
      InputDate = " " & Replace(InputDate, "/", "-")
      ThisWorkbook.SaveAs myFolder & "" & myFileName & InputDate & FileType
End Sub
This works perfectly as it is. What I would like to do, though, is send the file to a folder corresponding to the current month. Searching, I saw mkDir as an option, but I don't want new folders each time I save (all May worksheets should go in one May folder, &c) and I didn't know how to incorporate mkDir anyway, so I thought pre-creating the folders and adding a series of If statements would work. I wound up with something like this:

Sub Button26_Click()
  Dim InputDate As Variant
  Dim FileType As String
  Dim myFileName As String
  Dim myFolder As String
  If Worksheets("input sheet").Range("a71").value = 5 then
    FileType = ".xls"
    myFileName = "Service Desk Report: "
    myFolder = "J:Library StatsService deskMay"
   If Worksheets("input sheet").Range("A71").Value = 6 then
          FileType = ".xls"
    myFileName = "Service Desk Report: "
    myFolder = "J:Library StatsService deskJune"
        End If
        End if
And so forth, with a statement for each month.

When I run this, I get the input box, and it lets me enter the date, but then I get Error 1004, saying it can't find path C:105264 (the number is made up; i reverted to the IF-less code) and to make sure it exists or isn't being used by another program. When I click debug, the line highlighted is:
I don't see why adding the IFs should make a difference there, or what the problem is.  Obviously there's something I'm not
getting.  If anyone could point me in the right direction, I'd be endlessly grateful.


OK - I know, I know - I should be using a UserForm - and I do - but
sometimes, just sometimes, there are instances where I still find using
Dialogsheets best. I like the fact that they retain the data typed into
them, and that all data controls are effectively like 'cells' in a

Anyway, I have a dialogsheet which contains a 'date find' function.
To do this, on part of the dialogsheet I created a 7x5 grid of
TEXTboxes (not EDITboxes or labels) - the top row of which is populated
with Mon, Tue, Wed etc. and the other rows with the numbers 1-28,29,30
or 31 (depending on how many days there are in the month). The month
and year is specified and edited by means of a dropdown (for the month)
and an editbox/spinner (for the year), which causes the 'calender' grid
to re-populate and re-draw.

Each date box would have an OnAction macro attached to it, which would
use the value in Application.Caller to determine which 'date' had been
clicked, and this value would be passed on to a search routine.

When I first wrote this routine in Excel 97, it seemed to work nearly
all the time - however I found that on occassions that these text boxes
would refuse to 'get focus' - it was normally only after selecting
another month and these boxes were getting re-drawn.

On successive versions, I have found that even on first call, these
boxes refuse to get focus, although in certain situations (and I can't
work out a pattern to how to get this) - the boxes then accept focus
again. At all times, it is possible to 'tab on to' the text boxes from
other controls - the focus marquee appears and pressing space will
select the text box and run the OnAction event routine.

I can't seem to find a way to get the thing working consistently
anymore - is there something simple that can correct this behaviour? I
know it's an obsolete way of doing it, but it used to work - so it must
be an internal bug to the dialogsheet component in later versions of
Excel (maybe not making the transition from the .DrawingObjects.
collection to the .AutoShapes. / .Shapes. collections).

BTW The reason I used text boxes rather than buttons was so that on
activation the colours of each box could be changed - the way it works
is that the date first clicked is set as the 'start date' and the next
date clicked (if a later date) is set as the 'end date'. The range
selected then highlights by changing the colour of the text boxes to
indicate the date range selected.



Hi guys, I have built a basic macro to go into to an open workbook and rename all the tables, copy and paste a couple of rows ( to duplicate the row and rename it to another table name, same info twice just different need and title for the same info ). It also adds to columns of the word U.S.A and basically it just sorts this data out to be prepaired for upload into a listing database. The problem is that I need this code to go thru a folder and run this macro on all of the files in the folder. I have tried a million ways from different forums and chats around the web, but cannot get this macro to do this one thing, lol. So I have been stuck manually opening excel files, clicking the hotkeys to launch the macro then manully saveing the file and closing it. The macro below actually does save the file in the place I need it to as the type I need it to, I just cannot get it to go thru a folder full of files. Can anyone please help me to understand how to make this macro go full auto on a folder? Thanks in advance. Also, here is the basic macro that I have, It does everything I need it to except the ability to be launched and let go thru the files in a folder, lol. Here goes .

Sub kozler()
' kozler Macro
' kozler file organizer and fields changer.
' Keyboard Shortcut: Ctrl+b
    ActiveCell.FormulaR1C1 = "Title"
    ActiveCell.FormulaR1C1 = "Address Line 1"
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Title"
    ActiveCell.FormulaR1C1 = "Short Description"
    ActiveCell.FormulaR1C1 = "Description"
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Location Level 3"
    ActiveCell.FormulaR1C1 = "Location Level 2"
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "Location Level 1"
    ActiveCell.FormulaR1C1 = "U.S.A."
    Selection.AutoFill Destination:=Range("G2:G10001"), Type:=xlFillDefault
    ActiveWindow.SmallScroll Down:=-132
    ActiveWindow.ScrollRow = 9843
    ActiveWindow.ScrollRow = 9824
    ActiveWindow.ScrollRow = 9748
    ActiveWindow.ScrollRow = 9596
    ActiveWindow.ScrollRow = 9426
    ActiveWindow.ScrollRow = 9161
    ActiveWindow.ScrollRow = 8802
    ActiveWindow.ScrollRow = 8423
    ActiveWindow.ScrollRow = 8045
    ActiveWindow.ScrollRow = 7647
    ActiveWindow.ScrollRow = 7212
    ActiveWindow.ScrollRow = 6795
    ActiveWindow.ScrollRow = 6360
    ActiveWindow.ScrollRow = 5887
    ActiveWindow.ScrollRow = 5414
    ActiveWindow.ScrollRow = 4884
    ActiveWindow.ScrollRow = 4354
    ActiveWindow.ScrollRow = 3805
    ActiveWindow.ScrollRow = 3218
    ActiveWindow.ScrollRow = 2764
    ActiveWindow.ScrollRow = 2424
    ActiveWindow.ScrollRow = 2102
    ActiveWindow.ScrollRow = 1799
    ActiveWindow.ScrollRow = 1534
    ActiveWindow.ScrollRow = 1345
    ActiveWindow.ScrollRow = 1212
    ActiveWindow.ScrollRow = 1080
    ActiveWindow.ScrollRow = 966
    ActiveWindow.ScrollRow = 872
    ActiveWindow.ScrollRow = 815
    ActiveWindow.ScrollRow = 739
    ActiveWindow.ScrollRow = 682
    ActiveWindow.ScrollRow = 626
    ActiveWindow.ScrollRow = 569
    ActiveWindow.ScrollRow = 493
    ActiveWindow.ScrollRow = 436
    ActiveWindow.ScrollRow = 417
    ActiveWindow.ScrollRow = 398
    ActiveWindow.ScrollRow = 380
    ActiveWindow.ScrollRow = 361
    ActiveWindow.ScrollRow = 342
    ActiveWindow.ScrollRow = 304
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 115
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 1
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Location Level 1"
    ActiveCell.FormulaR1C1 = "Location Text 3"
    ActiveCell.FormulaR1C1 = "Location Text 2"
    ActiveCell.FormulaR1C1 = "Location Text 1"
    ActiveCell.FormulaR1C1 = "Zip code"
    ActiveCell.FormulaR1C1 = "Zip Code"
    ActiveCell.FormulaR1C1 = "County"
    ActiveCell.FormulaR1C1 = "Website"
    ActiveCell.FormulaR1C1 = "Phone"
    ActiveCell.FormulaR1C1 = "Fax"
    ActiveCell.FormulaR1C1 = "Contact Name"
    ActiveCell.FormulaR1C1 = "Role"
    ActiveCell.FormulaR1C1 = "Gender"
    ActiveCell.FormulaR1C1 = "Employee Count"
    ActiveCell.FormulaR1C1 = "Yearly Sales"
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveCell.FormulaR1C1 = "Category Level 1"
    ActiveCell.FormulaR1C1 = "Category Level 2"
    ActiveCell.FormulaR1C1 = "Category Level 3"
    ActiveCell.FormulaR1C1 = "Sic Code"
    Selection.Insert Shift:=xlToRight
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ChDir "C:UsersGorilla PCDesktopny pieces"
    ActiveWorkbook.SaveAs Filename:= _
        "C:UsersGorilla PCDesktopny pieces" & ActiveWorkbook.Name, FileFormat:=xlCSV, _
End Sub


I posted this on another forum but couldn't get response and now it is buried under all other topics so I wanted to try here.

Here is the link for the other post.

I am a frequent reader of this forum and learned quite a lot from you guys so I hope you can help me in my current situation as well because I haven't found the answer anywhere.

I have a big file that I have been working with for a while. Many formulas, links between tabs and so on. Not many macro's. But the ones I have are for hiding and unhiding some of the columns. Until some recent modifications everything was working fine. But recently (and I don't know what might have caused it) the macro's start acting up. For example:
Sub Viewadj()
If Columns("AH:AM").Hidden = True Then
Columns("AH:AM").Hidden = False - stops after completing this step
Columns("AH:AM").Hidden = True - or this step
End If
End Sub
If I run this macro, as simple as it looks, once there is some action done on the sheet (the two lines in bold) the macro stops without any message. Simply is done. Similar thing happens here:
Sub ExportN()
Select Case MsgBox("...", vbYesNo, "...Results")
Case vbYes
j = 1
Range(Cells(36, 143), Cells(162, 143)).ClearContents - exits
j = 36
For i = 36 To 162
As soon as the macro clears the contents of those cells it stops without a message.

The interesting thing - if I run these same codes in other worksheets within the same file, it works fine.

All my worksheet calls are blank so nothing there. But something clearly happens when I make a change to the sheet and can't figure out why and how to stop it.

My Event handlers are blank.

Thank you in advance!


Thank you all for helping as I am a new Macro user!!!

I am building a comand button that does the following:

1. Validates that data has been entered into a text box
2. After validating, the user clicks on the command box that takes them to another worksheet
3. Hides rows in that worksheet.

Current the command button has the following code:

Public Sub ClientWorksheet()
Application.ScreenUpdating = False
    Selection.EntireRow.Hidden = True
    Selection.EntireRow.Hidden = True
End Sub

Private Sub Go_Client_Click()
 Application.ScreenUpdating = False
If ValidateData = True Then
    End If
End Sub
The code is validating correctly, but when it goes to hide rows, it gives me a Run Error 1004 - Select Method of Range Class Error.

Does anyone know how I can fix this?

I have a problem with programming Excel solver using vba. I have the
following micro in a workbook named "Book2.xls":

Sub Macro1()
' Macro1 Macro
' Macro recorded 11/8/2005'
SolverOk SetCell:="$H$10", MaxMinVal:=2, ValueOf:="0", ByChange:="$G$10"
SolverAdd CellRef:="$G$10", Relation:=3, FormulaText:="$G$11"
SolverOk SetCell:="$H$10", MaxMinVal:=2, ValueOf:="0", ByChange:="$G$10"
SolverSolve userfinish:=True
End Sub

Macro1 runs fine if I open "Book2.xls" manually and either run it through
"ToolsMacroMacros...Macro1 -> Run " or run it in the VB editor.

I then created another workbook named "Test.xls" with the following code.

Private Sub Workbook_Open()
Workbooks.Open Filename:="C:testBook2.xls"
End Sub

With this code, I can open "Test.xls" manually, which then automatically
opens "Book2.xls". But when I try to run Macro2 inside "Book2.xls" (either
through "ToolsMacroMacros...Macro1 -> Run " or in the VB editor), the
Solver gives me an error "Solver: An unexpected internal error occurred, or
available memory was exhausted".

Does someone have any idea of how to fix this problem?

My original intent was to launch an Excel Workbook from within Microsoft
Access and automatically run the Excel solver with the data exported from
Access (since Access does not have the solver feature). I was given the same
error by the solver when I tried to do so.

I wrote a simple macro (code listed below) to individually copy a subset of
sheets from a workbook to create new workbooks. The orginal workbook has 13
worksheets and the user wants to copy out 8 worksheets to separate workbooks
to distribute to others.

I test it on my machine running Excel 2003 SP1 and everything works fine. I
test it on another machine running Excel 2000 SP3 and everything works fine.
My wife tests it at work running Excel 2000 and everything works fine. A
colleague runs it on his machine and everything works fine. My client,
running Excel 2002 SP3, runs the macro and it fails. She can't even manually
copy a sheet in the workbook I've sent her! She works in any other workbook
and she can manually copy a sheet.

The VBA command I use to copy a worksheet to a new workbook, the line on
which the macro fails, is simply "Sheets(shtName).Copy". The error is
"Path/File access error (Error 75)."

So, I'm thinking there's some issue between versions. I save my workbook in
Excel 2003 using "Microsft Excel 97- Excel 2003 & 5.0/95 Workbook" file type.
Open it on the machine running 2000-SP3 and do a Save As using "Microsoft
Excel Workbook" thinking that it'll now be saved as a 2000 workbook. Send it
to the client and IT WORKS! ... But Wait ... there are changes ...

I make the changes, go through the same silly Save As process listed above,
send it back to the client and ... IT DOESN'T WORK!! What's with that? This
is just plain rude!

If you've read this far, I Thank You for sticking with me. If you have any
suggestions as to what the issue may be or how I might solve the problem I
would be sincerely grateful!

Thanks in advance for any help that may be provided!

Private Sub CreateWorkbook(shtName As String, flName As String, asAtDt As

Application.StatusBar = "Creating the " + flName + " workbook"
'the following formatting replaces existing formulae with values so that
'user is not continually prompted to update the formulae when they open
'the workbook
ActiveSheet.Range("H1").Value = "'" + asAtDt
Cells.Find(What:="Plus Already Approved This Fiscal Year",
LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False).Cells(1, 2).Activate
Selection.PasteSpecial Paste:=xlPasteValues
Cells.Find(What:="Target For This Fiscal Year (High End of Range)",
LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False).Cells(1, 2).Activate
Selection.PasteSpecial Paste:=xlPasteValues

With ActiveWorkbook
.KeepChangeHistory = True
.SaveAs Filename:=flName, AccessMode:=xlShared
End With

End Sub


I have searched this forum (and others) for the solution to this problem but without success. I hope somebody can help.

I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

    If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then
        Select Case Target
            Case "0.5", "1", "U"
                icolor = 38
            Case "C", "M", "P"
                icolor = 40
            Case "A", "S", "D"
                icolor = 36
            Case "L", "UP", "C/E"
                icolor = 35
            Case Else
        End Select
        Target.Interior.ColorIndex = icolor
    End If

End Sub
I am hopeful somebody can help me modify this VBA to achieve the following:

* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).

* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.

* When the cell contains no value, the borders should return to how they were previously.

Many thanks in advance for any advice or solutions.

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