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.
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?
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.
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 Workbooks(Wbook).Activate End Sub
Sub AddToolbars() Dim customBar As CommandBar Dim myControl As CommandBarButton Set customBar = CommandBars.Add("Concur Reports") Set myControl = customBar.Controls _ .Add(Type:=msoControlButton) With myControl .FaceId = 2 .OnAction = "Main" End With customBar.Visible = True End Sub
When I Step through (Using the f8 Key) the below code -- Comments Explain my problem/Question
Sub SetUpTable() Worksheets("Sheet1").Activate 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
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.
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....
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.
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)
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..
Sub BLM_RENAME_SHEET() Dim WS As Worksheet Application.ScreenUpdating = False For Each WS In ActiveWindow.SelectedSheets
(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
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?
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.
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.
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?
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.
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.
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
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:............................
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)
Worksheets("PanelSched").Activate MsgBox "Test-Before close" If Range("M10") <> "" Then Dim PanelName As String PanelName = Range("M10").Text
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).
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 ActiveSheet.name but that doesn't seem to work.
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?)
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.
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.