Macro Addin That Inserts / Runs And Removes Module In Active Workbook

Mar 24, 2013

I have a macro that copies data from several workbooks saved in a particular folder to a master workbook.

The problem is that this macro only works when it is included as a module in the master workbook. Unfortunately, I cannot ask the users of this macro to insert a module, copy the code and run it each time they want to consolidate the master workbook. Therefore, I wanted to use this code by including it in a Add-In that I already prepared. The problem is that this consolidation macro does not run when it is in an add-in. Create a code that I can include in a macro add in that would insert a module in the master file, include the code that I already have, run it and then delete the module? The add-in would be password protected.

View 7 Replies


Function Runs Within Workbook But Not As AddIn

Feb 6, 2014

When running the following code for NSLookup in a module within the current workbook, it runs fine but when I put it in a XLAM file and try and use it as an addin I only get #NAME? as a result. I believe that that addin is installed properly because I have a second module with a sub routine in it (GetIPStatus) that is working, and when entering the command =NSLookup(A2) in a cell it shows up properly like it's an available function, but it does not seem to execute. Do I have to do anything special for a function to run as an addin?

View 4 Replies View Related

Removes All Code From The Active Workbook..

May 19, 2009

I have this script below that removes all code from the active workbook. The reason I am removing all the code is because I am running code from my personal library and the code changes the worksheet I am working with.So while stepping through I noticed it moves over to the sheet change sub routine from the active workbook that I am working on.

So I tried to remove all the code from the Active workbook but after I remove all the code it returns a expected end of sub error, where the old worksheet change sub was? but no code is in the entire workbook? So I tried to enter something in the blank sheet mod and it returns a (Cant Edit Module) error.

View 2 Replies View Related

Addin Workbook Remains Active

Feb 15, 2007

I have created a workbook as an addin. After the addin has been installed I can't make the original workbook active again either through VBA or the Window -> Select Workbook Name function. The code is below.

Private Sub Workbook_AddinInstall()
Dim Wbook As String
Wbook = ActiveWorkbook.Name
Call AddToolbars
End Sub

Sub AddToolbars()
Dim customBar As CommandBar
Dim myControl As CommandBarButton
Set customBar = CommandBars.Add("Concur Reports")
Set myControl = customBar.Controls _
With myControl
.FaceId = 2
.OnAction = "Main"
End With
customBar.Visible = True
End Sub

View 4 Replies View Related

Jumps To UDFs In Addin Module

Jan 20, 2013

When I Step through (Using the f8 Key) the below code -- Comments Explain my problem/Question


Sub SetUpTable()
Application.Calculation = xlCalculationManual ' Without entering this line the Macro in the next line or two), jumps to and begins running a UDF in a VBE ADDIN
'Module marked as Volitile
For TheYear = 1 To 5
Cells(1, TheYear + 1).Value = 1990 + TheYear


View 9 Replies View Related

Macro To Save Shared Addin To Personal Addin Folder

Dec 30, 2008

I have an addin in a shared folder which was created by someone else. (it is a user form). I need to create a macro which will go to its existing folder and then save it in the users personal addin folder also picking up their user id. There will be about 100 users so easier this way than going round doing it for them.

So I suppose the query is three fold.

1, pick up the addin and save it in a personal addin folder.
2, Find the username as part of the path file. For instance my personal would be:

"C:Documents and SettingsO033116Application DataMicrosoftAddIns"

The "O033116" is where I will need a search completed to obtain the user id.

The addin is called ServiceCredt.xla

3, Activate the installed addin.

The reason I want it this way is so the user is unable to access any of the code and we are in control.

View 8 Replies View Related

Module Runs On One Computer But Not On Another

Aug 12, 2009

I wrote some code that runs with no problem on my computer as well as another coworkers computer, but will not work on a 2nd coworker's computer. The "subscript out of range" error shows up at the point when the EMInput worksheet in the EMInput workbook is copied to the new workbook called "Out of Plane Section Cuts" (SEE BELOW). Is there an option in EXCEL or somewhere that could affect this. I don't see why you would get a subscript out of range error since the EMInput workbook is open....

View 9 Replies View Related

Open Workbook Macro Only Runs Once..

Jun 30, 2009

i have a button on my workbook1 that opens another workbook (lets call it workbook2) and automatically runs a macro on opening it. Once opened i have a button on workbook2 to close itself. I then go back to see workbook1. This all works but then when i click on the button to open the workbook2 again it opens but the macro does not run.

View 4 Replies View Related

Macro Runs On Wrong Workbook On Close

Jul 29, 2009

I have a macro on a particular workbook that runs when the workbook closes.

This runs fine when I close the workbook itself. If I happen to have other workbooks open and then I close EXCEL rather than an individual workbook, my macro that runs on close always runs when it is not the active workbook.

To put it another way, say that my 'run on close' macro is workbook 1. I also have workbooks 2 and 3 open as well. If workbook 3 is the active workbook and I choose to close Excel (and thus close all three workbooks), my 'run on close' macro runs straight away and, worse still, will run it on workbook 3 (my active workbook).

How can I stop the macro running until it is the active workbook in the closing chain, so that it only runs on the correct workbook? (workbook 1 in my example)

View 9 Replies View Related

Code To UNPROTECT Then PROTECT Workbook When Macro Runs

Jul 24, 2014

Okay, I have this code and it works very well - but it only works if my WORKBOOK is unprotected. I know how to Unprotect then Protect an ActiveSheet, but I can't figure out how to Unprotect then Protect my workbook when the macro runs..

Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets

[Code] ......

View 9 Replies View Related

Inserts New Row And Also Inserts New Sheet With The Rows Name That Was Inputted

Jan 8, 2007

(1) My main tab I have a macro button that inserts a new row and also inserts a new sheet with the rows name that was inputted. But on the main tab in cell A1 that is named, I can not get it to hyperlink to the new tab.

(2) When the button is hit to insert a new row and sheet I copy over previous tabs information and then I want to change two fields A1 and B1, A1 works fine but my B1 I can not get the formula to work correctly. My cell in B1 I need it to say 'DESCRIPTION: ' and then take the value of main tab 'Test Case Summaries' cell D?, it would be the new line and copy it here. See below... rname is the new tab name and lrow is the new row that was added when the macro button is hit.

t = Sheets.Count
Sheets(t).Copy after:=Sheets(t)
ActiveSheet.Name = rname

Sheets(t + 1).Range("a1") = ("Test Nbr " & rname)

Sheets(t + 1).Range("b1").Formula = "DESCRIPTION: " & "!$D$" & lrow

View 9 Replies View Related

Excel 2003 :: Ctrl-e Runs Wrong Same Name Macro In Different Workbook

Jun 17, 2012

I have a group of Excel (2003) template files, each with a different name. When these files are opened they ask for certain information and save themselves as a unique name. Within these files are VBA routines and functions and, because these files basically do the same things, they have the same name. These routines exist in their own unique modules each with a different name. Unfortunately, in these files there are differences in the number of columns of information these routines act on so once they have been saved I need them to only act on their own columns only.

My problem is, when I have more than 1 of these files open in the same instance of Excel, I try to run a file using a control letter combination Ctrl-e and it sometimes runs the version of the macro in the wrong file. This leads to all kinds of strange thing happening. What I can do to force the correct macro to work in all instances?

View 2 Replies View Related

Macro Removes Formula

Nov 21, 2008

Someone kindly wrote this macro for me a good while ago. It has served me well to date

In essence, this macro looks at column A, and at every instance of a value it either creates a new sheet within the workbook, names it, the copies the entire row OR if there is a sheet already just copies the entire row.

View 6 Replies View Related

Disabling Windows (Copy Addin To Addin Folder) Dialog

May 23, 2012

I have an Excel Addin that I deploy as a read-only addin on a server share. I instruct my co-workers to add the Add-In as they would any other Add-In, but I tell the to answer NO to the Windows question "Copy 'AddinName' to the Addins folder for John Doe?".

That way, everyone will be working with the server version of the addin, and any time I want to roll out an updated version I just place a new addin file on the server. The next time the users restart Excel, the new version is loaded.

Except users never follow directions and some of them click the default 'Yes' when asked and get a local copy of the addin and never receive updated versions of the addin.

How do I disable the "Copy Addin" question completely? I don't want to depend on the user. I know this is possible because I have this 3rd party addin that we use that is installed from c:Program Files and it just stays there and you're never asked to copy the addin when you install it in Excel.

View 9 Replies View Related

Macro That Removes The Color Of A Cell

Jul 30, 2008

Im having huge trubles with the interior color for the cells.
I have a macro that removes the color of a cell ("No fill").
For that purpose, I use the line:

View 8 Replies View Related

Macro That Removes Numbers Leaving Text

Dec 28, 2005

Macro that will strip out all the numbers in 1 particular cell column and and leave the text.

View 9 Replies View Related

Keeping Track Of Which Macro Is In Which Module Since You Can't Rename The Module?

Mar 27, 2009

After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?

View 2 Replies View Related

Copy A Workbook Functions From Addin

Mar 29, 2009

I have a addin and I ahve some code wriiten in the Workbook_BeforeClose event. On click some "Copy Sheets" menu option in my addin it creates a new workbook and copies some sheets from the addin to the new workbook.

But I want somehow have the code written in Workbook_BeforeClose event of my addin to be copied too...Since I am coping only the sheets , workbook events are not copied.

View 2 Replies View Related

Macro Will Not Look At Active Workbook

Apr 21, 2009

I have 2 workbooks open in the same excel session. each with its own unique name. The macro looks for a specific name and then copies data from this workbook to the 2nd workbook. For some reason it no longer like the name of the workbook #1 which has not change (as far as I can tell) When I run the macro i get the run time error '9' subscript out of range, when i click on the debug this is what is highlighted and underlined.

View 5 Replies View Related

Set Macro For All Worksheets (active Workbook)

Nov 14, 2006

Sub display_all_false()
With Application
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.CommandBars("Control Toolbox").Visible = False
.CommandBars("Drawing").Visible = False
End With
End Sub

But, when I run the code, seems there're run-time errors detected at these codes:
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False

1. Is there any way to correct these codes?
2. Does the bolded codes applied to ActiveWindow (Worksheet) only (eg. With ActiveWindow ...)? Can I change it to ActiveWorkbook (perhaps)?
3. Can the command menus (File Edit View Insert Format ...) be hidden also?
4. Does looping method for each worksheet

View 9 Replies View Related

Macro: Create Directory Based On Active Workbook And Save To It

Sep 8, 2006

Read “My Documents” Path And Use Result



Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003

1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.

2. Per customer request, VBA application needs to save extracted files for future use.

3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.

4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:

Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................

View 5 Replies View Related

How To Create Addin And Feed Macro

Dec 29, 2012

I want the following macro to be used as an excel addin, how to create addin and feed this macro on that particular addin so that its available each time I open a new workbook.

The macro is : [Code] ...........

View 2 Replies View Related

VBA Runs If Workbook Is Open Or Closed?

Jan 28, 2014

The code below doesn't run if the workbook that is saves to is all ready open. is there a way for the macro to run if the workbook is closed or open?

[Code] ......

As with bigger files i will open the folder before i run the macro.

View 6 Replies View Related

Closing Workbook Runs BeforeClose VBA Twice

Sep 11, 2009

In the various codes I am using AutoOpen, AutoClose, Workbook_BeforeClose and Workbook_BeforeSave Subs. BeforeClose and BeforeSave are in '_ThisWorkbook'; AutoOpen and AutoClose are in a Module. I also use Workbook_Open, Workbook_Activate and Workbook_Deactivate, also in _ThisWorkbook. I put message boxes thru out code to be sure code was running right. The Workbook_BeforeClose is running twice under certain condition (when a cell in active workbook sheet is blank ("")). I do not understand why it is running twice.

For condition where there is data in cell M10, this creates a whole other issue ( multiple running of mutliple various Subs) which I believe I should deal with in different thread/question.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "Test-Before close"
If Range("M10") <> "" Then
Dim PanelName As String
PanelName = Range("M10").Text

View 9 Replies View Related

Saving A Workbook Via The This Workbook Module

Mar 20, 2007

I will shortly be running a project over a twenty four hour period, where a group of people will be inputting data into a workbook (one worksheet per hour), and what I would like to know is if it is possible, to input some coding into a thisWorkbook module to action a save on the workbook every time an entry is placed in column A (of each worksheet) which is divisable by 10 (a10, a20, a30 etc).

View 2 Replies View Related

Copy Module To Another Workbook

Jul 14, 2006

Is there anyway to copy a module from one workbook to another using a macro?

View 6 Replies View Related

Macro With Reference To Active Chart On Active Sheet

May 14, 2014

I currently have the following macro running to set a chart's data values:

Sub C3Quarter12013()
' C3Quarter32013 Macro
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Values = _

[Code] ......

When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.

I've tried changing the sheet name to but that doesn't seem to work.

View 4 Replies View Related

Creating A Macro, That Creates A New Button, Which Itself Runs A Macro..

Dec 15, 2009

I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.

I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)

View 3 Replies View Related

Building Macro That Runs Another Macro Based On Certain Columns

Jun 6, 2012

Essentially, I am working with a database for a school and up until this point, each row matches up to a student with a bunch of information on them. One of these pieces of info is their parent's names. This is a much simplified look at things, but the screenshot below is a basic version of what everything looks like:

Now, the school wants me to split up the parents names from the student's and place them in their own row. I have recorded a macro to do this where you make the active cell the "full name" column for the row you want to expand, and then run the macro. Afterwards, up to two more rows are added, with the parent's names and some data copied. Below is a screenshot of what that looks like: (yellow is the new data generated, and blue is the active cell I marked to run the macro properly)

The issue is that there are thousands of these that need to be done, and I figured there is a way to do this with a macro. The issue is that not every row has parents names to expand out.

In layman's terms, I need a macro that will look at the two columns titled "Father's Name" and "Mother's Name" on each row of the spreadsheet and then, if there is any data in both or either of them, it should set the active cell to the "Full Name" column for that row then run the macro accordingly. After that it should repeat this all the way down, skipping any where neither of the columns have text in them

In the actual spreadsheet, the columns that need to be checked are AW and AX, and the column that needs to be set to the active cell before running the macro is B.

View 5 Replies View Related

Macro That Takes Workbooks Or Files And Runs Them Through Another Macro

Apr 25, 2007

i need to make a macro that takes workbooks or files and
runs them through another macro. i already have the 2nd macro
done and it is working perfectly i just need to know how to make the one
that finds the other files and runs them all through the macro i already made. My boss said that he will have about 150-200 files to run through this macro.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved